1

Consider an SQL statement like 

Select * from items where id in (123,456,789)

Can I use a prepared statement like

Select * from items where id in ?

and then supply the parameter as a set or list? Maybe I'd need parentheses around the "?".

I'm planning to use this in R, but I guess it's a general query for JDBC.

John
  • 6,701
  • 3
  • 34
  • 56
  • 1
    [See here](https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives). You may certainly create a prepared statement with a `WHERE IN` clause. – Tim Biegeleisen Feb 01 '18 at 05:55

1 Answers1

1

Two partial work-arounds:

  1. Create a function that changes in (?) to in (?,?,?), depending on the length of the supplied list of values, then break that array into individual values for binding.

    • Pros: one query; binding is straight-forward
    • Cons: not feasible with large lists; have to wrap your queries in query-manglers, not fool-proof

  2. Upload values to a temp table and change your query to

    select * from items where id in (select val from temptable)
    • Pros: deal with arbitrary number of values; no need to trick SQL; binding is just as one would do for a multi-row insert
    • Cons: multiple calls; requires temp table and clean-up; might be problematic integrating with more complex queries (??)
r2evans
  • 141,215
  • 6
  • 77
  • 149