New bug in the spring jdbc

[henry lu]

String in_clause = "(120,'JOHN'),(121,'SMITH')";

String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
+ "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
+ "DEPTNO " + "FROM EMP "
+ "WHERE (empno,ename) not in (?) "
+ "ORDER BY 2";

errors:

08:08:26,233 WARN - [support.SQLErrorCodeSQLExceptionTranslator.transla te] Unab
le to translate SQLException with errorCode '920', will now try the fallback tra
nslator
08:08:26,233 INFO - [support.SQLStateSQLExceptionTranslator.translate] Translat
ing SQLException with SQLState '42000' and errorCode '920' and message [ORA-0092
0: invalid relational operator
]; SQL was [SELECT EMPNO,ENAME,JOB,MGR,to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE,
SAL,COMM,DEPTNO FROM EMP WHERE (empno,ename) not in (?) ORDER BY 2] for task [exe
cuting PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$Sim
plePreparedStatementCreator@25b72a]]
08:08:26,233 INFO - [interceptor.TransactionInterceptor.onThrowable] Invoking r
ollback for transaction on method 'dispEmp' in class [edu.umich.umms.springemp.s
ervice.EmpService] due to throwable [org.springframework.jdbc.BadSqlGrammarExcep
tion: Bad SQL grammar [SELECT EMPNO,ENAME,JOB,MGR,to_char(HIREDATE, 'MM/DD/YYYY'
) HIREDATE,SAL,COMM,DEPTNO FROM EMP WHERE (empno,ename) not in (?) ORDER BY 2] in
task 'executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTe
mplate$SimplePreparedStatementCreator@25b72a]'; nested exception is java.sql.SQL
Exception: ORA-00920: invalid relational operator
]
08:08:26,233 INFO - [datasource.DataSourceTransactionManager.rollback] Initiati
ng transaction rollback
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [SELECT EMPNO,E
NAME,JOB,MGR,to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE,SAL,COMM,DEPTNO FROM EMP W
HERE (empno,ename) not in (?) ORDER BY 2] in task 'executing PreparedStatementCal
lback [org.springframework.jdbc.core.JdbcTemplate$SimpleP reparedStatementCreator
@25b72a]'; nested exception is java.sql.SQLException: ORA-00920: invalid relatio
nal operator

----------------------------------------------------------------------------

But, If i change the sql to the following, it works.

String in_clause = "(120,'JOHN'),(121,'SMITH')";

String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
+ "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
+ "DEPTNO " + "FROM EMP "
+ "WHERE (empno,ename) not in ("+in_clause+") "
+ "ORDER BY 2";

Is this a bug in the spring jdbc?

-Henry

[trisberg]

No, it is not a bug in either Spring or JDBC. This is a limitation of JDBC. You can't pass in a variable list of values using a placeholder. It is just not supported.

You will have to either add it to the sql string, like you did, or use a stored procedure where you can pass in an array.

[henry lu]

But, this one works:

String in_clause = "'120','121'";

String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
+ "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
+ "DEPTNO " + "FROM EMP "
+ "WHERE ENAME not in (?) "
+ "ORDER BY 2";

Henry

----------------------------------------------------------------------------------

No, it is not a bug in either Spring or JDBC. This is a limitation of JDBC. You can't pass in a variable list of values using a placeholder. It is just not supported.

You will have to either add it to the sql string, like you did, or use a stored procedure where you can pa?ss in an array.

[henry lu]

But, this one works:

String in_clause = "'120','121'";

String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
+ "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
+ "DEPTNO " + "FROM EMP "
+ "WHERE ENAME not in (?) "
+ "ORDER BY 2";

and this one doesn't:

String in_clause = "120,121";

String sqlString = "SELECT " + "EMPNO," + "ENAME," + "JOB," + "MGR,"
+ "to_char(HIREDATE, 'MM/DD/YYYY') HIREDATE," + "SAL," + "COMM,"
+ "DEPTNO " + "FROM EMP "
+ "WHERE ENAME not in (?) "
+ "ORDER BY 2";

Henry

[trisberg]

Henry,

You might find certain combinations that provide a workaround with a specific jdbc driver. However, it is still not the right way to do it

[Tom Turelinckx]

Henry,

I've moved this topic to the Data Access forum, here.

Best regards,
Tom.