Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Unsolved SQLite exec() limitation

    General and Desktop
    3
    9
    1360
    Loading More Posts
    • 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.
    • Tewha
      Tewha last edited by

      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 Reply Quote 1
      • mrjj
        mrjj Lifetime Qt Champion last edited by

        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;
        }
        
        Tewha 1 Reply Last reply Reply Quote 0
        • Tewha
          Tewha @mrjj last edited by 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? :)

          mrjj 1 Reply Last reply Reply Quote 1
          • mrjj
            mrjj Lifetime Qt Champion @Tewha last edited by

            @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 Reply Quote 0
            • Tewha
              Tewha last edited by 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. :)

              mrjj 1 Reply Last reply Reply Quote 0
              • mrjj
                mrjj Lifetime Qt Champion @Tewha last edited by

                @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 :)

                Tewha 1 Reply Last reply Reply Quote 0
                • Tewha
                  Tewha @mrjj last edited by

                  @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!

                  mrjj JonB 2 Replies Last reply Reply Quote 1
                  • mrjj
                    mrjj Lifetime Qt Champion @Tewha last edited by

                    @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 Reply Quote 0
                    • JonB
                      JonB @Tewha last edited by

                      @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 Reply Quote 0
                      • First post
                        Last post