Problem with IN clause

[sreedhar Lackka]

Hi,

Thanks in advance.
I would like to pass a comma separated string to my stored procedure. When I send a single value it is working correctly however If I pass more than one it is not returning any parameter in JDBC.
Example:
I have a select query which uses in like ‘select name from where roll_no in (‘10a’,’10e’,’11j’);
I have java variable to hold this comma separated value.

Please guide me.

Thanks,
Sree

[Mike Zal]

Can you post the code you are using so far?

[sreedhar Lackka]

Hi,
Thanks for your reply.

The high level code looks like below,

CallableStatement cs = connection.prepareCall("call test_proc(?,?)");
StringBuffer s=new StringBuffer();
s.append("11B").append("10A");
cs.setString(1, s.toString());
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.execute();
String param2 = cs.getString(2);
System.out.println(param2);

The proc will accept this in parameter, and perform select with in caluse

select name from xsdr where num in (varName)

here varName is the value sent from Java

Thanks
Sree

[Mike Zal]

After your call on line 3, the StringBuffer contains "11B10A". If you intended to have them comma separated you need to append that as well

s.append("11B").append(",").append("10A"); 

I'm not sure how stored procedure will react to the comma separated list; you may want to try testing it from the database command line. You might want to consider overloading the test_proc procedure to take an Array of values instead of trying to stuff multiple values into one varchar parameter.