How to get last prepared and batch executed query using QSqlQuery?

Question

I know exactly the same question was asked, but:

It was 11 years ago
Solution from this answer does not handles execBatch() method with addBindValue(QVariantList).

Using QVariantList as a parameter is helpful for me, because in my SQL queries I don't create a string like this:

(?, ?, ?), ... , (?, ?, ?)

but instead, I can use only one (?, ?, ?) and add many items by:

query.prepare("insert into a (b, c) values (?, ?)");
query.addBindValue(qvariantlist1);
query.addBindValue(qvariantlist2);
query.execBatch();

Code from 11 yo post only substitutes each ? with only the first element of QVariantList.

How do I get it to replace each ? by all the values of the QVariantList bound to it?

score 0 · Accepted Answer · answered Apr 15 '23 at 22:26

If I understood you correctly, you just need to modify the solution from the question you linked a little bit, add another dimension to it.

#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QSqlDatabase db;
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.open();
    QSqlQuery query(db);
    QVariantList qvariantlist1(3),qvariantlist2(3),qvariantlist3(3);

    qvariantlist1[0]="11";
    qvariantlist1[1]="12";
    qvariantlist1[2]="13";

    qvariantlist2[0]="21";
    qvariantlist2[1]="22";
    qvariantlist2[2]="23";

    qvariantlist3[0]="31";
    qvariantlist3[1]="32";
    qvariantlist3[2]="33";

    query.prepare("insert into a (b, c) values (?, ?, ?)");
    query.addBindValue(qvariantlist1);
    query.addBindValue(qvariantlist2);
    query.addBindValue(qvariantlist3);

    query.execBatch();

    QString s = query.lastQuery();
    qDebug()<<s;
    QVariantList list = query.boundValues();

    for (int i = 0; i < list.size(); i++)
    {
        //get a QVariantList each loop, each one is bound to a `?`
        QVariantList l = list.at(i).toList();
        //this is the opening parenthesis of the values of each QVariantList
        QString extract="(";

        //make a substring of the the form: (var1,var2,var3,...varn)
        for(int j = 0; j < l.size()-1 ; j++)
        {
            extract.append(l[j].toString()+",");
        }
        //an if statement to avoid adding a comma to the last parentheses of the last QVariantList 
        if(i<list.size()-1)
            extract.append(l[l.size()-1].toString()+"),");
        else
            extract.append(l[l.size()-1].toString()+")");

        //get the index of each '?'
        int index = s.indexOf(QLatin1Char('?'), i);
        //then remove it
        s.replace(index,1,"");
        //then insert your substring, a QVariantList each loop inside parentheses 
        s.insert(index,extract);
    }
    qDebug()<<s;

    return a.exec();
}

Last query before:

"insert into a (b, c) values (?, ?, ?)"

Last query after (output of the last qDebug()):

"insert into a (b, c) values ((11,12,13), (21,22,23), (31,32,33))"

How to get last prepared and batch executed query using QSqlQuery?

1 Answers1