JDBC and WHERE IN clauses

[Tony Seebregts <to...@cibecs.com>]

Hi,

Does anybody know of a way to use a PreparedStatement with a WHERE IN 
(?) clause

e.g. SELECT * FROM Names WHERE ID IN (?)

where the parameter is replaced by an array of e.g. int's ?

regards

Tony Seebregts

Re: JDBC and WHERE IN clauses

[Rick Hillegas <Ri...@Sun.COM>]

Hi Tony,

You could try using a temporary table to hold your parameter values. By 
changing the contents of the temporary table you might achieve what you 
want. Something like the following:

declare global temporary table session.ztemp
( param int )
not logged;


select *
from names
where id in ( select param from session.ztemp );

Cheers,
-Rick

Tony Seebregts wrote:

> Hi,
>
> Does anybody know of a way to use a PreparedStatement with a WHERE IN 
> (?) clause
>
> e.g. SELECT * FROM Names WHERE ID IN (?)
>
> where the parameter is replaced by an array of e.g. int's ?
>
> regards
>
> Tony Seebregts
>
>
>

Re: JDBC and WHERE IN clauses

[Michael Vinca <mi...@vinca.org>]

Well, when you build the prepared statement, you could just loop and 
add "?, " to the string for the length of the array. Of course, at this 
point, you could just do a normal statement. But if you are 
programmatically filling in the prepared statement anyway, this could 
be a solution. Depends on what you are trying to do.

~Mike

On Sep 23, 2005, at 1:19 PM, Satheesh Bandaram wrote:

> Don't think you can do this in Derby. However, you can do:
>
> SELECT * FROM Names WHERE ID IN (?, ?, ?)
>
> and bind individual elements of the array to each of the parameters.
> This, of course, forces you to know the number of elements of the 
> array..
>
> Satheesh
>

Re: JDBC and WHERE IN clauses

[Satheesh Bandaram <sa...@Sourcery.Org>]

Don't think you can do this in Derby. However, you can do:

SELECT * FROM Names WHERE ID IN (?, ?, ?)

and bind individual elements of the array to each of the parameters.
This, of course, forces you to know the number of elements of the array..

Satheesh

Tony Seebregts wrote:

> Hi,
>
> Does anybody know of a way to use a PreparedStatement with a WHERE IN
> (?) clause
>
> e.g. SELECT * FROM Names WHERE ID IN (?)
>
> where the parameter is replaced by an array of e.g. int's ?
>
> regards
>
> Tony Seebregts
>
>
>
>