I know exactly the same question was asked, but:
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?
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))"