Skip site navigation (1) Skip section navigation (2)

Re: in(...) clause and PreparedStatement

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Sergey Pariev <egy(at)tnet(dot)dp(dot)ua>
Cc: Postgres JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: in(...) clause and PreparedStatement
Date: 2005-06-13 13:30:12
Message-ID: 1118669412.2720.11.camel@coppola.muc.ecircle.de (view raw or flat)
Thread:
Lists: pgsql-jdbc
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(at)postgresql(dot)org)
> >
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org


In response to

Responses

pgsql-jdbc by date

Next:From: Tom LaneDate: 2005-06-13 14:34:43
Subject: Re: timestamp & graph time scale
Previous:From: Andrés LunaDate: 2005-06-13 13:13:52
Subject: Re: Getting Reference cursors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group