PDA

View Full Version : need urgent help with PreparedStatement

msggmx
01-13-03, 09:30
i use db2 as database, jdbc 2.0 driver and jsp.
i have a prepared statement which looks like this :

query = "select id, name from t_department where id in (?) order by name";

i have a StringBuffer (selectedIDs) which consists of comma sepperated integers like :
216, 220, 230, 323, 432

it is not a string so the statement below gives an error :

pst.setString(1, selectedIDs.toString());

using a Statement instead would solve the problem :
query = "select id, name from t_department where id in ("+selectedIDs.toString()+") order by name";

but wouldn't that compile a new statement every time, because the id-list (the comma sepperated values) changes with every request.

how can i solve this ?
any help will be appreciated

Mehmet Gunacti
mserkan@gmx.net
sangeethk
01-19-04, 03:27
Originally posted by msggmx
i use db2 as database, jdbc 2.0 driver and jsp.
i have a prepared statement which looks like this :

query = "select id, name from t_department where id in (?) order by name";

i have a StringBuffer (selectedIDs) which consists of comma sepperated integers like :
216, 220, 230, 323, 432

it is not a string so the statement below gives an error :

pst.setString(1, selectedIDs.toString());

using a Statement instead would solve the problem :
query = "select id, name from t_department where id in ("+selectedIDs.toString()+") order by name";

but wouldn't that compile a new statement every time, because the id-list (the comma sepperated values) changes with every request.

how can i solve this ?
any help will be appreciated

Mehmet Gunacti
mserkan@gmx.net



Hi,

Can you post clearly, what sort of error you are getting.
And the portion of the code where you are getting error.
I can't asure you that i can solve your's.But I can try,bcoz i have worked with the similar kind of problem long back.

With regards,
sangeethk
n_i
01-19-04, 18:23
Originally posted by msggmx

how can i solve this ?


You don't necessarily need to solve it. The SQL compilation overhead is not that significant in many cases. However, if you insist on using PreparedStatement you could do this:

If you know (or can limit) how many values can go into your IN () clause you could use as many parameter markers as you need (replacing some markers with the same values if there are less values than the maximum).

Say, you expect the maximum of 5 ID values to be provided. You the would create a statement like this:

SELECT ... WHERE ID IN (?, ?, ?, ?, ?) ...

If all 5 values are provided for a certain invokation of the query you would call setInt() for each of them.

If only 1 value is provided you still call setInt 5 times but use the same value every time.
karansplash
01-21-04, 06:00
If we dont know the maximum number of ids thts is the maximum limit ........then wat can be the solution .....
yogeshk
01-21-04, 10:23
what n__i suggested (using multiple ?) is the only way out of it.
i agree that using Statement will not cause too much of an overhead.
orababa@kshema
01-25-04, 05:20
Dont do anything. Your code is fine... You can't have control over your parameters.
Cheers
Keep things simple as the world is already complex
msegmx
01-27-04, 03:23
i had sent this posting one year ago, and can't remember how i solved it, possibly using Statement.
thanks to all of you.
Mehmet