Prepared statements and the IN expression

Question

I have a database where users can search for records that have one or more of a list of items. I'm using IN to do the search, but I can't get IN to work with prepared statements. This is what I've tried:

SELECT * FROM tbl1 WHERE col IN (?)

But the prepared statement treats the list of items I pass it as a single item. How can I make this work?

I'm using sqlite, if it makes any difference.

Answer 1

You can't do IN this way, because you can't bind to an array.

You have to do it in two steps:

1. Create the SQL with one '?' per value in the array or list.
2. Loop over the array or list and bind each value.

This is true regardless of database.

You don't say whether a sub-SELECT could be a better solution, but perhaps it could be made to work if the values in question were available in another table.

Answer 2

You can use a temp table and subquery:

CREATE TEMP TABLE cols (col integer primary key);
INSERT INTO cols VALUES (23);
INSERT INTO cols VALUES (25);
INSERT INTO cols VALUES (28);

SELECT * FROM tbl1 WHERE col IN (select col from cols);

DROP TABLE cols ;