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.
-
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; }
-
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? :)
-
@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? :)
@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. :)
-
@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. :)
@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-scriptso 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
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-scriptso 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!
-
@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!
@Tewha
Does SQLite allow "stored procedures"? Assuming it does, are they allowed to holdCREATE 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....