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: 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: Imitating Mail Merge on server
    ... given moment we know which data (in this case, customers) is being worked ... and concantenate each query together. ... loop the array, plug in the current recordset value. ... We have close to fifty tokens that we use in the application correspondence, ...
    (microsoft.public.inetserver.asp.general)
  • Re: Direct3D ExecutionEngineException
    ... I managed to get the VertexBuffer and IndexBuffer built without ... shutting down the app by reducing the index buffer array to the exact size ... app quits. ...
    (microsoft.public.win32.programmer.directx.managed)