Parameterized sql in clause with variable number of arguments in DB2

Question

I have a sql as below in my java program:

String sql = "Select * from mySchema.myTable where product in (?) and myDate = ?";

I have my query params as:

Object[] params = {"\'abc\',\'pqr\',\'lmn\'",'2013-07-18'};

And I am trying to execute as:

List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, params);

where jdbcTemplate is a org.springframework.jdbc.core.JdbcTemplate object.

However, I am getting error as:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [Select * from mySchema.myTable where product in (?) and myDate = ?]; DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.59.81; nested exception is com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.59.81
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:796)

Further down the stack trace:

Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=3.59.81
at com.ibm.db2.jcc.am.dd.a(dd.java:668)
at com.ibm.db2.jcc.am.dd.a(dd.java:60)
at com.ibm.db2.jcc.am.dd.a(dd.java:127)
at com.ibm.db2.jcc.am.bn.c(bn.java:2546)
at com.ibm.db2.jcc.am.bn.a(bn.java:2053)
at com.ibm.db2.jcc.t4.cb.n(cb.java:802)
at com.ibm.db2.jcc.t4.cb.i(cb.java:259)
at com.ibm.db2.jcc.t4.cb.c(cb.java:54)
at com.ibm.db2.jcc.t4.q.c(q.java:44)
at com.ibm.db2.jcc.t4.rb.j(rb.java:147)
at com.ibm.db2.jcc.am.bn.ib(bn.java:2048)
at com.ibm.db2.jcc.am.cn.b(cn.java:3845)
at com.ibm.db2.jcc.am.cn.b(cn.java:3975)
at com.ibm.db2.jcc.am.cn.bc(cn.java:678)
at com.ibm.db2.jcc.am.cn.executeQuery(cn.java:652)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)

How can I pass a string as a parameter to my sql where my string is of type 'abc','pqr','xyz'

Thanks for reading!

@mustaccio: Yes.. and String "'abc','pqr','xyz'" will be one value.. isn't that correct ? — Vicky, Oct 21 '13 at 11:35
Have a look at http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause — Peter Schuetze, Oct 21 '13 at 14:09
What happens if you allow the array to be an array? `Object[] params = {new String[] { "abc", "pqr", "lmn" }, "2013-07-18 }` — Ian McLaird, Oct 21 '13 at 15:16
See also this answer: http://stackoverflow.com/questions/4504592/how-to-use-select-in-clause-in-jdbctemplates — Ian McLaird, Oct 21 '13 at 20:14

RokL · Accepted Answer · 2013-10-21T13:13:49.640

0

I suggest you use a better ORM like MyBatis.

Also it looks like you're passing a string parameter as a date. Your solution to pass a list of strings to IN statement won't work either, your "'abc','cde'" will be treated as a single string in the IN statement since all SQL characters like , are ignored in parameters of parametrized statements (it's the feature that prevents SQL injection).

edited Oct 21 '13 at 13:13

answered Oct 21 '13 at 13:07

RokL

2,663
3
22
26

Parameterized sql in clause with variable number of arguments in DB2

1 Answers1