SQLite exec() limitation



  • 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.


  • Qt Champions 2017

    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;
    }
    


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


  • Qt Champions 2017

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



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


  • Qt Champions 2017

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



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


  • Qt Champions 2017

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



  • @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....


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.