Re: in(...) clause and PreparedStatement - Mailing list pgsql-jdbc

Thanks to all for the instant and detailed replies.

I have to say I posted simplified example of the query I need to run, in
reality I don't know how many items will be in the IN clause. I have to
do 3 queries (update, then insert ... select from this table and delete
) on particular table given the set of keys which I retrieve in the
other query, which it complex so I can't dublicate it 3 times - so I
can't just write

SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where
...).

Currently I retrieve keys at first and concatenate them into string like
1,2,3,... , then I issue my queries like

st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ;

and so on. It is working as for now, but is really ugly, so I desided to
improve things and was just wandering if there a way to use variable
number of parameters.

Considering the thing you guys wrote I'm propably will rewrite my code
to use fixed number of parameters - it wouldn' t be too hard since I'm
processing it in chunks anyway. It will be only in the next version of
my app though - don't want to fix the thing which are working :).

Thanks again, Sergey.

Csaba Nagy пишет:

>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
>>
>>
>
>
>