Qt - How to bind a QList to a QSqlQuery with a "WHERE ... IN" clause?

user_342647 0
Viewed 9096

Note: this is with SQLite, although I expect the problem is on the Qt side.

First, I set up a database table from the SQLite command line tool:

sqlite> create table testtable ( id INTEGER PRIMARY KEY NOT NULL, state INTEGER );
sqlite> insert into testtable (state) values (0);
sqlite> insert into testtable (state) values (1);
sqlite> insert into testtable (state) values (9);
sqlite> insert into testtable (state) values (20);

Then I test my query:

sqlite> SELECT id,state FROM testtable WHERE state IN (0,1,2);
1|0
3|1

(Those are expected results.)

Then I run this C++ code:

void runQuery() {
        QSqlQuery qq;
        qq.prepare( "SELECT id,state FROM testtable WHERE state IN (:states)");
        QList<QVariant> statesList = QList<QVariant>();
        statesList.append(0);
        statesList.append(1);
        statesList.append(2);
        qq.bindValue(":states", statesList);
        qq.exec();
        qDebug() << "before";
        while( qq.next() ) {
            qDebug() << qq.value(0).toInt() << qq.value(1).toInt();
        }
        qDebug() << "after";
}

which prints this:

before
after

No rows were printed. I assume this is because I can't bind a list directly to a placeholder in an "in" clause. But is there a way to do it? I haven't been able to find anything about this.

qsqldatabase qt sqlite
3 Answers

You have to use QSqlQuery::execBatch

QSqlQuery q;
q.prepare("insert into myTable values (?, ?)");

QVariantList ints;
ints << 1 << 2 << 3 << 4;
q.addBindValue(ints);

QVariantList names;
names << "Harald" << "Boris" << "Trond" << QVariant(QVariant::String);
q.addBindValue(names);

if (!q.execBatch())
    qDebug() << q.lastError();
user_101099980
Related