multiple sql statements in QSqlQuery using the sqlite3 driver

Question

I have a file containing several SQL statements that I'd like to use to initialize a new sqlite3 database file. Apparently, sqlite3 only handles multiple statements in one query via the sqlite3_exec() function, and not through the prepare/step/finalize functions. That's all fine, but I'd like to use the QtSQL api rather than the c api directly. Loading in the same initializer file via QSqlQuery only executes the first statement, just like directly using the prepare/step/finalize functions from the sqlite3 api. Is there a way to get QSqlQuery to run multiple queries without having to have separate calls to query.exec() for each statement?

Answer 1

As clearly stated in Qt Documentation for QSqlQuery::prepare() and QSqlQuery::exec(),

For SQLite, the query string can contain only one statement at a time. If more than one statements are give, the function returns false.

As you have already guessed the only known workaround to this limitation is having all the sql statements separated by some string, split the statements and execute each of them in a loop.

See the following example code (which uses ";" as separator, and assumes the same character not being used inside the queries..this lacks generality, as you may have the given character in string literals in where/insert/update statements):

QSqlDatabase database;
QSqlQuery query(database);
QFile scriptFile("/path/to/your/script.sql");
if (scriptFile.open(QIODevice::ReadOnly))
{
    // The SQLite driver executes only a single (the first) query in the QSqlQuery
    //  if the script contains more queries, it needs to be splitted.
    QStringList scriptQueries = QTextStream(&scriptFile).readAll().split(';');

    foreach (QString queryTxt, scriptQueries)
    {
        if (queryTxt.trimmed().isEmpty()) {
            continue;
        }
        if (!query.exec(queryTxt))
        {
            qFatal(QString("One of the query failed to execute."
                        " Error detail: " + query.lastError().text()).toLocal8Bit());
        }
        query.finish();
    }
}