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