c++qtbatch-processingqlistqtsql

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


I know exactly the same question was asked, but:

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?


Solution

  • 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))"