Bind collection as the right-hand-side of a "where col in ?" clause

Question

In Oracle OCI, I can prepare a statement like:

select * from t where pk in :1

and bind a VArray collection for the :1 placeholder.

I don't see any way to do the equivalent in SQLite, unless I use one of the following works arounds:

• prepare

  select * from t where pk=:1

  instead and execute this N times with all the pks in my collection, and manually do a "union" of the rows from the N queries

• put my collection of pk in a temporary table and do a join with t on it.

• textually replace :1 with the collection values, negating the benefits of prepared statements.

Am I missing something? And what would be the recommended way to emulate OCI's collection binding? Thanks, --DD

Answer 1

Alas, sqlite only supports binding to scalars, so you're right that you can't bind a parameter to a collection. If the number of items in the collector is bounded, you could prepare a statement with 'IN (?, ?, ?, ?)' [[assuming a max of four items]] then bind the actual scalars in the array (if less than 4, bind the last one repeatedly, or bind a value that you know is "impossible" as a placeholder); otherwise, the approaches you sketch are indeed more or less all that comes to mind.