Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. SQLite exec() limitation
QtWS25 Last Chance

SQLite exec() limitation

Scheduled Pinned Locked Moved Unsolved General and Desktop
9 Posts 3 Posters 2.0k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • TewhaT Offline
    TewhaT Offline
    Tewha
    wrote on last edited by
    #1

    I understand that the exec() function when using SQLite is limited to a single statement. This is an incredibly useful function for setting up a database from a schema, so I'm wondering how people typically get around this limitation.

    Scanning the SQL for ; and splitting on them would work but introduces some limitations I don't know that I want to deal with. Likewise, I'd really like to bind some values.

    I'm used to using sqlite3 directly, but the models seem worth using.

    1 Reply Last reply
    1
    • mrjjM Offline
      mrjjM Offline
      mrjj
      Lifetime Qt Champion
      wrote on last edited by
      #2

      Hi and welcome to the forums.

      What you mean by one statement ?
      Like you cannot issue multiple inserts as one string or ?

      void ProcessDB() {
        QSqlQuery query;
        bool ok = query.prepare("SELECT * from person ");
        query.exec();
        while (query.next()) {
          QString name = query.value(2).toString(); // col 1 = name
          qDebug() << ">" << name;
        }
      }
      
      
      bool createConnection() {
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(":memory:");
        if (!db.open()) {
          QMessageBox::critical(0, qApp->tr("Cannot open database"), "Click Cancel to exit.", QMessageBox::Cancel);
          return false;
        }
        QSqlQuery query;
        qDebug() << "table:" <<   query.exec("create table person (id int primary key, "
                                             "firstname varchar(20), lastname varchar(20), num int )");
        query.exec("insert into person (firstname , lastname, num) values('Dennis', 'Young','1')");
        query.exec("insert into person values(102, 'Christine', 'Holand','2')");
        query.exec("insert into person values(103, 'Lars junior', 'Gordon','4')");
        query.exec("insert into person values(104, 'Roberto', 'Robitaille','5')");
        query.exec("insert into person values(105, 'Maria', 'Papadopoulos','3')");
        return true;
      }
      
      TewhaT 1 Reply Last reply
      0
      • mrjjM mrjj

        Hi and welcome to the forums.

        What you mean by one statement ?
        Like you cannot issue multiple inserts as one string or ?

        void ProcessDB() {
          QSqlQuery query;
          bool ok = query.prepare("SELECT * from person ");
          query.exec();
          while (query.next()) {
            QString name = query.value(2).toString(); // col 1 = name
            qDebug() << ">" << name;
          }
        }
        
        
        bool createConnection() {
          QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
          db.setDatabaseName(":memory:");
          if (!db.open()) {
            QMessageBox::critical(0, qApp->tr("Cannot open database"), "Click Cancel to exit.", QMessageBox::Cancel);
            return false;
          }
          QSqlQuery query;
          qDebug() << "table:" <<   query.exec("create table person (id int primary key, "
                                               "firstname varchar(20), lastname varchar(20), num int )");
          query.exec("insert into person (firstname , lastname, num) values('Dennis', 'Young','1')");
          query.exec("insert into person values(102, 'Christine', 'Holand','2')");
          query.exec("insert into person values(103, 'Lars junior', 'Gordon','4')");
          query.exec("insert into person values(104, 'Roberto', 'Robitaille','5')");
          query.exec("insert into person values(105, 'Maria', 'Papadopoulos','3')");
          return true;
        }
        
        TewhaT Offline
        TewhaT Offline
        Tewha
        wrote on last edited by Tewha
        #3

        @mrjj Thank you! With SQLite, a typical way to set up the database (outside of Qt, I mean!) would be to create the entire schema as a single string.

        Not tested (and it's been a while since I last did this), but sort of like this:

        BEGIN;
        CREATE TABLE Person (id INTEGER PRIMARY KEY, FirstName VARCHAR COLLATE NOCASE, LastName VARCHAR COLLATE NOCASE, CompanyID INTEGER);
        CREATE TABLE Company (id INTEGER PRIMARY KEY, Name VARCHAR COLLATE NOCASE);
        CREATE TABLE PhoneNumber(id INTEGER PRIMARY KEY, PersonID INTEGER, Label VARCHAR, Value VARCHAR);
        END;
        

        I get that I can manage the transaction with Qt methods, but I'm still left splitting the string myself or trying to split it in code. Is that right or am I missing something obvious? :)

        mrjjM 1 Reply Last reply
        1
        • TewhaT Tewha

          @mrjj Thank you! With SQLite, a typical way to set up the database (outside of Qt, I mean!) would be to create the entire schema as a single string.

          Not tested (and it's been a while since I last did this), but sort of like this:

          BEGIN;
          CREATE TABLE Person (id INTEGER PRIMARY KEY, FirstName VARCHAR COLLATE NOCASE, LastName VARCHAR COLLATE NOCASE, CompanyID INTEGER);
          CREATE TABLE Company (id INTEGER PRIMARY KEY, Name VARCHAR COLLATE NOCASE);
          CREATE TABLE PhoneNumber(id INTEGER PRIMARY KEY, PersonID INTEGER, Label VARCHAR, Value VARCHAR);
          END;
          

          I get that I can manage the transaction with Qt methods, but I'm still left splitting the string myself or trying to split it in code. Is that right or am I missing something obvious? :)

          mrjjM Offline
          mrjjM Offline
          mrjj
          Lifetime Qt Champion
          wrote on last edited by
          #4

          @Tewha
          Hi
          Hmm. i think you are right.
          only likes the SQL way
          query.exec("create table person (id int primary key, firstname varchar(20), lastname varchar(20), num int )");
          but i actually never tried chaining them with ;
          But i assume you tried "create table xxx; create table yyy" and it wont accept it.

          Maybe someone knows trick. I can see the use case.

          1 Reply Last reply
          0
          • TewhaT Offline
            TewhaT Offline
            Tewha
            wrote on last edited by Tewha
            #5

            @mrjj said in SQLite exec() limitation:

            But i assume you tried "create table xxx; create table yyy" and it wont accept it.

            Yes, it fails and there's a note in the documentation that it won't work. I'm not totally sure, maybe it's a security thing?

            For example in, QSqlQuery::exec(const QString &query):

            For SQLite, the query string can contain only one statement at a time. If more than one statement is given, the function returns false.

            I'd be quite comfortable using SQLite directly if I could get to the sqlite3 handle and it was safe to manipulate things behind Qt's back.

            Thanks for the reply. :)

            mrjjM 1 Reply Last reply
            0
            • TewhaT Tewha

              @mrjj said in SQLite exec() limitation:

              But i assume you tried "create table xxx; create table yyy" and it wont accept it.

              Yes, it fails and there's a note in the documentation that it won't work. I'm not totally sure, maybe it's a security thing?

              For example in, QSqlQuery::exec(const QString &query):

              For SQLite, the query string can contain only one statement at a time. If more than one statement is given, the function returns false.

              I'd be quite comfortable using SQLite directly if I could get to the sqlite3 handle and it was safe to manipulate things behind Qt's back.

              Thanks for the reply. :)

              mrjjM Offline
              mrjjM Offline
              mrjj
              Lifetime Qt Champion
              wrote on last edited by
              #6

              @Tewha
              Hi
              with some extra checking/googling.
              It seems to map to
              sqlite3_prepare(...)
              and docs says.
              "These routines only compile the first statement in zSql".
              source:
              https://stackoverflow.com/questions/4420623/qt-sqlite-only-creates-one-table-from-script

              so it might be limitation of SQLite , at least for the interface Qt is using.

              Anyway, i also found
              Q_DECLARE_OPAQUE_POINTER(sqlite3*)
              Q_DECLARE_METATYPE(sqlite3*)
              sqlite* sqlite_handle = database.driver()->handle().value<sqlite3*>();
              which should give the native handle.
              Disclaimer: not tried :)

              TewhaT 1 Reply Last reply
              0
              • mrjjM mrjj

                @Tewha
                Hi
                with some extra checking/googling.
                It seems to map to
                sqlite3_prepare(...)
                and docs says.
                "These routines only compile the first statement in zSql".
                source:
                https://stackoverflow.com/questions/4420623/qt-sqlite-only-creates-one-table-from-script

                so it might be limitation of SQLite , at least for the interface Qt is using.

                Anyway, i also found
                Q_DECLARE_OPAQUE_POINTER(sqlite3*)
                Q_DECLARE_METATYPE(sqlite3*)
                sqlite* sqlite_handle = database.driver()->handle().value<sqlite3*>();
                which should give the native handle.
                Disclaimer: not tried :)

                TewhaT Offline
                TewhaT Offline
                Tewha
                wrote on last edited by
                #7

                @mrjj Probably. There's also sqlite3_exec, which executes multiple statements. The exec() in Qt is sort of a false friend in that sense. :)

                I'll try working with the handle directly. I imagine it's safe enough if I just use it for schema setup. Thank you!

                mrjjM JonBJ 2 Replies Last reply
                1
                • TewhaT Tewha

                  @mrjj Probably. There's also sqlite3_exec, which executes multiple statements. The exec() in Qt is sort of a false friend in that sense. :)

                  I'll try working with the handle directly. I imagine it's safe enough if I just use it for schema setup. Thank you!

                  mrjjM Offline
                  mrjjM Offline
                  mrjj
                  Lifetime Qt Champion
                  wrote on last edited by
                  #8

                  @Tewha
                  Ok. if it works please update here so others know they can be dirty if needed. ;)
                  I also assume its ok since you only constructs the table so there not be any
                  housekeeping data altered that could confused Qt.

                  1 Reply Last reply
                  0
                  • TewhaT Tewha

                    @mrjj Probably. There's also sqlite3_exec, which executes multiple statements. The exec() in Qt is sort of a false friend in that sense. :)

                    I'll try working with the handle directly. I imagine it's safe enough if I just use it for schema setup. Thank you!

                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote on last edited by
                    #9

                    @Tewha
                    Does SQLite allow "stored procedures"? Assuming it does, are they allowed to hold CREATE TABLE statements? One possibility, depending on your design, is to factor all these multi-statements into a stored procedure and just call that from your Qt code as a single statement....

                    1 Reply Last reply
                    0

                    • Login

                    • Login or register to search.
                    • First post
                      Last post
                    0
                    • Categories
                    • Recent
                    • Tags
                    • Popular
                    • Users
                    • Groups
                    • Search
                    • Get Qt Extensions
                    • Unsolved