Re: in(...) clause and PreparedStatement

Sergey,

Additionally to what Dave wrote you: if you want to use the prepared
statement with variable number of parameters, you can sometimes use a
query with a fixed number of parameters, and if you have more
parameters, execute it chunk-wise, if you have less parameters then set
the additional ones to null. This variant complicates your code
considerably though, but the query is prepared and reusable. I would
think it only matters if you reuse the prepared statement for a large
number of executions.

This only works if you don't have to have all the parameters processed
in one statement, i.e. you can chunk your query and the final cumulated
results will be the same.

In any other case you will not be able to use prepared statements, i.e.
you'll need to build your query each time. It is still advisable to do
it via JDBC prepared statements, and not build it directly by hand,
because that will take care for all the escaping necessary for your
parameter values. This means you should build a query with as many ?
signs as many parameters you have, and then set them in a loop or so.

HTH,
Csaba.


On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
> Yes, because it thinks "1,2,3" is a string
>
> you would have to do
>
> IN(?,?,?)
>
> then
>
> setObject(1, 1);
> setObject(2, 2);
> setObject(3, 3);
>
> Dave
>
> On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
>
> > Hi all.
> >    It may be stupid question, but is there way to use
> > PreparedStatement with queries like
> > SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
> >    I've googled but haven't found nothing explicitly said on this
> > topic.
> >    I've tried the following
> >    PreparedStatement st = conn.prepareStatement("SELECT * FROM
> > mytable WHERE t_id IN ( ? )");
> >    st.setObject(1,"1,2,3");
> >
> >    and get error complaining on type mismatch.
> >
> >    Thanks in advance, Sergey.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org