Prepared Statements and IN Expression

Question

I have a database where users can search for entries containing one or more list items. I use IN to search, but I cannot get IN to work with prepared statements. This is what I tried:

SELECT * FROM tbl1 WHERE col IN (?)

But the prepared statement considers the list of elements that I pass to it as a separate element. How can I do this job?

I use sqlite if that matters.

Answer 1

You cannot do this because you cannot bind to an array.

You must do this in two steps:

• Create SQL with one '?' per value in an array or list.
• .

, .

, sub-SELECT , , , , .

Answer 2

:

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 ;