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();
    }
}

Answer 2

I wrote a simple function to read SQL from a file and execute it one statement at a time.

/**
* @brief executeQueriesFromFile Read each line from a .sql QFile
* (assumed to not have been opened before this function), and when ; is reached, execute
* the SQL gathered until then on the query object. Then do this until a COMMIT SQL
* statement is found. In other words, this function assumes each file is a single
* SQL transaction, ending with a COMMIT line.
*/

void executeQueriesFromFile(QFile *file, QSqlQuery *query)
{
    while (!file->atEnd()){
        QByteArray readLine="";
        QString cleanedLine;
        QString line="";
        bool finished=false;
        while(!finished){
            readLine = file->readLine();
            cleanedLine=readLine.trimmed();
            // remove comments at end of line
            QStringList strings=cleanedLine.split("--");
            cleanedLine=strings.at(0);

            // remove lines with only comment, and DROP lines
            if(!cleanedLine.startsWith("--")
                    && !cleanedLine.startsWith("DROP")
                    && !cleanedLine.isEmpty()){
                line+=cleanedLine;
            }
            if(cleanedLine.endsWith(";")){
                break;
            }
            if(cleanedLine.startsWith("COMMIT")){
                finished=true;
            }
        }

        if(!line.isEmpty()){
            query->exec(line);
        }
        if(!query->isActive()){
            qDebug() << QSqlDatabase::drivers();
            qDebug() <<  query->lastError();
            qDebug() << "test executed query:"<< query->executedQuery();
            qDebug() << "test last query:"<< query->lastQuery();
        }
    }
}

• http://www.fluxitek.fi/2013/10/reading-sql-text-file-sqlite-database-qt/
• https://gist.github.com/savolai/6852986