How can I go about using a PreparedStatement for a SQL query that uses the Oracle IN operator. I have something like SELECT name FROM emp WHERE id IN ('A11', 'B12', 'C13') The number of values specified in the IN-list is only known at run time.
Is there any way of using a PreparedStatement other than calculate the number of values at run-time and create a String with that many '?'s, and set them individually, in which case I might as well use a Statement, since the values to be put into IN-list are retrieved directly from the database, and will be genuine String values.
since the values to be put into IN-list are retrieved directly from the database
Then you may probably be able to rewrite your query so that it doesn't need to use a variable-length "IN" list.
In any case, yours is an ultra FAQ. As far as I know, the only way is the one you have described. If you haven't already done so, I suggest going to the Ask Tom Web site and doing a search for the terms "variable in list".
Sonny, I've used a "batching" solution to use prepared statements with an in clause. I pick some predefined batch sizes (like 1, 4, 11 and 51.) I then fill up the largest batch size and submit it. Repeat for the remaining data.
The idea is that the database can truly prepare my prepared statement (since there are only four of them.)
Avi, I think I will rewrite it to use a subquery as the condition. And thanks for the link, I did not know of that site, it seems really useful.
Jeanne, How exactly do you use the 'batching' solution? If you have a PreparedStatement with say 10 parameters, and at runtime you have 14 values that you need to use, do you execute it once with the first 10 values, and on the second pass, you set the 10 parameters by repeating the 4 values that are left? or Have I got it all wrong?
author & internet detective
Sonny, Suppose your batch sizes are 1, 4 and 10. For 14, you would do one query with ten parameters. Then, you would do a query with four parameters. This would be a separate SQL string with only four parameters. You wouldn't use the full one with 10 parameters, because those would be wasted. The idea is to have a finite number of distinct sql queries in your prepared statements so they stay in the cache.