QSQLITE: multiple instructions in SQL query

[mcosta]

Hi,

I'm porting a pure C++ application in Qt.
These application create a SQLITE database structure in one instruction

const char* sql = 
    "create table T1 (id INTEGER, name TEXT); \n"
    "create table T2 (id INTEGER, name TEXT); \n";
 
int ans = sqlite_exec (db, sql, 0, 0, 0);
if (SQLITE_OK != ans) {
...
}

const char* sql = 
    "create table T1 (id INTEGER, name TEXT); \n"
    "create table T2 (id INTEGER, name TEXT); \n";

int ans = sqlite_exec (db, sql, 0, 0, 0);
if (SQLITE_OK != ans) {
...
}

To copy to clipboard, switch view to plain text mode 

writing this code

const char* sql = 
    "create table T1 (id INTEGER, name TEXT); \n"
    "create table T2 (id INTEGER, name TEXT); \n";
 
QSqlQuery q(db)
if (!q.exec(sql)) {
...
}

const char* sql = 
    "create table T1 (id INTEGER, name TEXT); \n"
    "create table T2 (id INTEGER, name TEXT); \n";

QSqlQuery q(db)
if (!q.exec(sql)) {
...
}

To copy to clipboard, switch view to plain text mode 

QSqlQuery::exec returns true but only the first table is created.
I think this is a QSqlDriver limit.

Any suggestion?

PS. At the moment i resolved creating an array of query and executing them one a time

[schnitzel]

I'm not an expert on SQLite, but why don't you try the following:

QString sql = "create table T1 (id INTEGER, name TEXT); create table T2 (id INTEGER, name TEXT);";
QSqlQuery q(db);
if (!q.exec(sql))
...

QString sql = "create table T1 (id INTEGER, name TEXT); create table T2 (id INTEGER, name TEXT);";
QSqlQuery q(db);
if (!q.exec(sql))
...

To copy to clipboard, switch view to plain text mode 

[unit]

Code of schnitzel is not work too.

I'm looking code of sqlite plugin, but cann't find any mistake. This problem is very interesting for me, can anybody help?

[mcosta]

You code doesn't work. Only T1 are created.

[schnitzel]

sorry, didn't actually try the code. I just tried it in mysql Query Browser and it didn't work there either.
If you look at one of the sql examples:
http://doc.qt.nokia.com/latest/sql-cachedtable.html
... it appears you can only do one statement per exec() call.

However, can't you read multiple sql statements from a file using sqlite command line?

[mcosta]

At the moment this behaviour isn't a real problem, I'm only curious about it because using sqlite API is possible to do this.

[schnitzel]

I'm not sure if the sqlite API supports reading sql statements from a file, you would have to check on sqlite website.

What is wrong with using sqlite command line? You could use QProcess to do the tedious work of creating the db structure.

[ChrisW67]

The Sqlite API permits one statement at a a time through sqlite3_prepare16_v2(), which is the call that Qt uses because it permits parameter binding. The sqlite3_exec() API call, which does permit multiple statements, does not permit parameter binding AFAICT.