SQLite exec() limitation

[Tewha]

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.

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

[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]

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

[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]

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

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

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

[JonB]

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