SQLite: bind list of values to "WHERE col IN ( :PRM )"

Question

all I want to do is send a query like

SELECT * FROM table WHERE col IN (110, 130, 90);

So I prepared the following statement

SELECT * FROM table WHERE col IN (:LST);

Then I use

sqlite_bind_text(stmt, 1, "110, 130, 90", -1, SQLITE_STATIC);

Unfortunately this becomes

SELECT * FROM table WHERE col IN ('110, 130, 90');

and is useless (note the two additional single quotes). I already tried putting extra ' in the string but they get escaped. I didn't find an option to turn off the escaping or prevent the text from being enclosed by single quotes. The last thing I can think of is not using a prepared statement, but I'd only take it as last option. Do you have any ideas or suggestions?

Thanks

Edit:

The number of parameters is dynamic, so it might be three numbers, as in the example above, one or twelve.

Answer 1

You can dynamically build a parameterized SQL statement of the form

 SELECT * FROM TABLE WHERE col IN (?, ?, ?)

and then call sqlite_bind_int once for each "?" you added to the statement.

There is no way to directly bind a text parameter to multiple integer (or, for that matter, multiple text) parameters.

Here's pseudo code for what I have in mind:

-- Args is an array of parameter values
for i = Lo(Args) to Hi(Args)
   paramlist = paramlist + ', ?'

sql = 'SELECT * FROM TABLE WHERE col IN (' + Right(paramlist, 3)  + ')'

for i = Lo(Args) to Hi(Args)
  sql_bind_int(sql, i, Args[i]

-- execute query here.

Answer 2

I just faced this question myself, but answered it by creating a temporary table and inserting all the values into that, so that I could then do:

SELECT * FROM TABLE WHERE col IN (SELECT col FROM temporarytable);