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

Question

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.

score 11 · Accepted Answer · edited May 08 '20 at 17:06

11

Never mind my question. I think what I am trying to do is not possible with prepared statements, regardless of the framework or RDBMS. You can do "WHERE x IN (?)", but then the '?' refers to an single value -- it cannot be a list of values; or you can do "WHERE x IN (?,?,?), and each '?' needs to be bound separately.

Example:

QString const queryText = "SELECT id, firstname FROM users WHERE id IN (%1)";

QVector<int>     const ids { /* ... */ };
QVector<QString> const placeholders(ids.size(), "?");

QSqlQuery query(db);
query.prepare(queryText.arg(QStringList::fromVector(placeholders).join(", ")));

for (auto const & i : ids)
    query.addBindValue(i);

query.exec();

edited May 08 '20 at 17:06

Chnossos

9,971
4
28
40

answered Jul 10 '10 at 18:38

Travis

2,961
4
22
29

2019: This answer is outdated, see Teemu Karimerto & Павел Андреев's answers – Rhdr Jul 03 '19 at 13:31
1

@Rhdr No it is not. Павел's answer is off-topic and Teemu's answer can lead to injections. See my edit of this answer. – Chnossos May 08 '20 at 16:54
Still only way that worked for me in sqlite. – vbargl Jan 22 '23 at 00:34

score 8 · Answer 2 · edited Feb 05 '20 at 21:11

I have been looking for a way to do this too for a while now, and Google wasn't very helpful. I started playing around with it and it turns out it is indeed possible, to a limited degree at least. It is tested only with PostgreSQL and SQLite, so I do not know about other RDBMS. My case concerns only integer keys, but should theoretically work for other types as well.
The way to do this is build an array manually and bind it to a variable. Say I want to select multiple users by their ids from a table, such as SELECT id, firstname, lastname FROM users WHERE id = ANY(:id). Here's how it can be done.

QList<int> ids; // A list of IDs to select
ids << 1 << 5 << 7;

// Create strings from list
QStringList idstrings;
foreach(int id, ids) {
    idstrings << QString::number(id);
}
QString numberlist = idstrings.join(",");

// Create, prepare and execute the query
QSqlQuery sql;
sql.prepare("SELECT id, firstname, lastname FROM users WHERE id = ANY(:id)");
sql.bindValue(":id", numberlist);
sql.exec();

// Now this is possible
while( sql.next() ) {
    qDebug() << sql.value(0).toInt() << sql.value(1).toString() << sql.value(2).toString();
}

Typed from memory, but should be fine. I know this reply is extremely late, but hopefully this post helps someone else out there. The snippet above only works with PostgreSQL. However it is be possible to adapt this to other databases as well, depending on their array support.

For SQLite replace the SQL query with:

sql.prepare("SELECT id, firstname, lastname FROM users WHERE id IN (:id)");

Any alternative for SQLite? This seems not to be working in SQLite. — Sleeba Paul, Aug 21 '19 at 11:18
@SleebaPaul You might try using `?` instead of `:id` syntax, I was using sqlite3 and using the Oracle form of parameter passing did not work for me. Not positive that's the solution, but try it first. — NuclearPeon, Feb 15 '21 at 19:25
This solution doesn't work with strings. I get `AND name = ANY('one, two')` (note the placement of the single quotes) instead of `AND name = ANY('one', 'two')`. Oh and I get a syntax error anyway, with MariaDB (mysql), so I'm not even sure `ANY` works there. I'll write a loop and make multiple queries, how suboptimal :( — David Faure, May 25 '22 at 22:51

score 1 · Answer 3 · edited Jul 03 '19 at 15:08

1

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

edited Jul 03 '19 at 15:08

Rhdr

387
4
22

answered Oct 24 '18 at 08:45

Павел Андреев

37
1

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

3 Answers3