Re: Can I use an array with an IN clause?

flarosa <frank@xxxxxxxxxxxxxxx> writes:

Hi,

I want to execute a prepared statement of the form:

SELECT * FROM MyTable WHERE ID IN (1,2,3)

Instead of putting in the literal values (1,2,3) I want to substitute
an array at runtime.

I tried doing this:

SELECT * FROM MyTable WHERE ID IN ?

I tried passing an array of integers as the parameter, but it didn't
work.

I can solve the problem by generating a query of the form "WHERE ID IN
(?,?,?,?)", but I have to create a new query from scratch each time
because the number of integers varies.

Is there some general way to do this?

Don't think so. Someone, please correct me if I'm wrong.

But even with your current approach you may run into problems since
the number of elements in an IN-list often is limited. The limit varies
between databases.

Without knowing more about your app it is a bit hard to say what you
could do instead. I'm guessing that the records you want from MyTable
are somehow related, but your app is hiding this fact from the dbms by
just asking for a seemingly random collection of IDs.

--
dt

.

Relevant Pages

  • Re: Can I use an array with an IN clause?
    ... I tried passing an array of integers as the parameter, ... I can solve the problem by generating a query of the form "WHERE ID IN ... because the number of integers varies. ... FirstSQL/J Object/Relational DBMS ...
    (comp.lang.java.databases)
  • Can I use an array with an IN clause?
    ... I want to execute a prepared statement of the form: ... I tried passing an array of integers as the parameter, ... I can solve the problem by generating a query of the form "WHERE ID IN ... because the number of integers varies. ...
    (comp.lang.java.databases)
  • Re: Copying a table to an array
    ... You probably don't need a query, ... Dim rstRSet As Recordset ... table to enable data from the latter to be read into an array with GetRows. ... exercise has therefore to be repeated prior to each interrogation. ...
    (microsoft.public.access.formscoding)
  • Re: Mix/Max/Avg Help based on dynamic ranges
    ... Did you enter Biff's formula as an array? ... The query I gave you is dependent on your breaking out the ranges as Biff ... Where Nums are the values to avg based on the head count range. ... deemed optimal for building device profiles based on locn size and device ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Copying a table to an array
    ... "Peter Hibbs" wrote: ... You probably don't need a query, ... Dim rstRSet As Recordset ... table to enable data from the latter to be read into an array with GetRows. ...
    (microsoft.public.access.formscoding)