0

I wrote a query of the form:

select .... where x.y in (?) union select .... where p.y in (?) and a.b not in (?)

The question marks indicate places where I put multiple values at run time (dynamically putting values in the IN clause) using the preparedStatement.setString() method.

The resultset, on executing this query seems to ignore the query after the union clause. I get no exception anywhere.

I post this question, just to know if anyone else has faced such a problem, like this link suggests UNION of multiple tables and preparedstatement not working The database is Oracle 10g, in case that makes a difference.

Abhijeet Kashnia
  • 12,290
  • 8
  • 38
  • 50
  • i would be suspicious that the 'many values' string is being interpreted as a single string - and therefore not matching what you expect. it feels like you want to generate the full sql string, then issue it as an execute imediate type operation. – Randy Apr 08 '11 at 13:32
  • I take this query template. Then depending on the number of incoming parameters, replace one ? with n question marks for each in clause, and then call setString the correct number of times. I don't understand what you imply by "many values string interpreted as single string". Could you please elaborate. – Abhijeet Kashnia Apr 08 '11 at 13:37
  • In that case, can you show the actual query you are executing against the database? – Dave Costa Apr 08 '11 at 13:52
  • Apologies, the query failed because the second IN clause, after the UNION, was incorrectly populated. Thank you all for your help. I really should be more careful. :( – Abhijeet Kashnia Apr 08 '11 at 16:22
  • Related: http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause – BalusC Apr 08 '11 at 20:12

2 Answers2

1

As far as I remember - you cant use a ? for the in operator - but union is supported fine.

Have you tried replacing the in with =? Or removing the union and checking if it solves the problem?

RonK
  • 9,472
  • 8
  • 51
  • 87
  • I disagree. This link seems to indicate we can use ? for IN operator. Take a look at the query in option 2. http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 – Abhijeet Kashnia Apr 08 '11 at 13:42
  • 1
    Abhijeet, look more carefully at option 2 in that article. It uses a single bind variable for each value in the list: `id IN (?, ?, ?)`. It appears that you are trying to use a single bind variable for the whole list: `x.y in (?)` and you say you "put multiple values" at runtime. This will not work; the bind variable represents a single value, not a list of values. Probably you are effectively binding a string like `'1,2,3'`. – Dave Costa Apr 08 '11 at 13:50
  • Just read your comment above. My original interpretation may not be correct -- but you should post the actual query being executed, if the string that you posted is being manipulated in Java before being submitted to the database. – Dave Costa Apr 08 '11 at 13:55
1

You can only use the '?' operator for separate values. Using a String to set the IN value you will get...

SELECT * FROM TABLE WHERE ID IN (?)

... will be considered ...

SELECT * FROM TABLE WHERE ID IN ("1,2,3,4")

... in your case.

If you use the "Option 2" from the JavaRanch link, it will be like..

SELECT * FROM TABLE WHERE ID IN (1, 2, 3, 4)

... which I believe is what you want, BUT you will need to always supply exactly 4 values. If you have fewer you canof course use one of them again with no ill effect, but if you have more of them you are out of luck.

What I would recommend you to do, is to construct the PreparedStatement dynamically, with as many '?' as you have in-parameters and then loop through and set them. That way you combine that you need a dynamic query with cleaning the input, avoiding any SQL injection attack.

JenEriC
  • 758
  • 7
  • 10
  • Thanks JenEriC, but I think I replaced IN(?) with IN(?,?,?) at runtime before executing. I'll double check my code, and try a sample spike solution on simple data just to be sure. – Abhijeet Kashnia Apr 08 '11 at 14:03