org.springframework.jdbc.UncategorizedSQLException

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • I think there are some issues with nulls when calling JdbcTemplate.update(String, Object[]). I see some work was already done between 1.0.2 and 1.1 RC2 but I am still running into problems. Specifically, I think JdbcTemplate.update(String, Object[], int[]) would probably work, but I'm working in framework code that doesn't want to have to figure out the types for itself. First, a stack trace (including Spring log messages): DEBUG [main] (JdbcTemplate.java:489) - Executing SQL update [update waiver rec set staffing_ind_cd = ?, waiver_status_nm = ?, start_dt = ?, commenter_dt = ?, staffer_dt = ?, commenter_extension_dt = ? where rec.waiver_id_cd = ?] DEBUG [main] (StatementCreatorUtils.java:78) - Setting SQL statement parameter value; columnIndex=1, parameter value='Y', valueClass=java.lang.String, sqlType=-2147483648 DEBUG [main] (StatementCreatorUtils.java:78) - Setting SQL statement parameter value; columnIndex=2, parameter value='Staffing', valueClass=java.lang.String, sqlType=-2147483648 DEBUG [main] (StatementCreatorUtils.java:78) - Setting SQL statement parameter value; columnIndex=3, parameter value='null', valueClass=null, sqlType=-2147483648 org.springframework.jdbc.UncategorizedSQLException: (executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator@4ec78d]): encountered SQLException [Invalid column type]; nested exception is java.sql.SQLException: Invalid column type at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:94) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:281) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:340) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:491) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:543) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:551) at com.bah.krm.util.JdbcTemplateSqlUtils.execute(JdbcTemplateSqlUtils.java:466) at com.bah.krm.ql.sql.impl.ExecutableOperation.execute(ExecutableOperation.java:37) at com.bah.imako.staffing.StaffingService.staff(StaffingService.java:99) at Test.main(Test.java:42) at Test$$FastClassByCGLIB$$27b8b2.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149) at org.springframework.aop.framework.Cglib2AopProxy$MethodInvocationImpl.invokeJoinpoint(Cglib2AopProxy.java:912) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:119) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:627) at Test$$EnhancerByCGLIB$$18959700.main(<generated>) at Test.main(Test.java:54) I believe the line of code to fix is org.springframework.core.StatementCreatorUtils.java:89. This is where Spring is dealing with null values when setting substitutions in a JDBC PreparedStatement. Here is the line of code with some context around it. I included what I think the line should be in a comment like this //!!! public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, Object inValue) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Setting SQL statement parameter value; columnIndex=" + paramIndex + ", parameter value='" + inValue + "', valueClass=" + (inValue != null ? inValue.getClass().getName() : "null") + ", sqlType=" + sqlType); } if (inValue == null) { if (typeName != null) { ps.setNull(paramIndex, sqlType, typeName); } else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) { //!!! ps.setNull(paramIndex, Types.VARCHAR); ps.setObject(paramIndex, inValue); } else { ps.setNull(paramIndex, sqlType); } } Now the weird thing is that I would expect this to be a cleaner fix: ps.setNull(paramIndex, Types.NULL); //NULL instead of VARCHAR However, that doesn't work for me using Oracle. I don't know if all DBs are like this, but in Oracle null is just null, it doesn't have a type. So specifying a random type, like VARCHAR is OK even though in my particular example the column I was setting to null was a Date. I guess this might be a bug in Oracle's JDBC implementation, but what are the chances they'll prepare a fix within the next few years? I'm not sure if this fix would break things on other DBs or if the change would need to be isolated for Oracle somehow...
    via by Matthew Sgarlata,
  • I think there are some issues with nulls when calling JdbcTemplate.update(String, Object[]). I see some work was already done between 1.0.2 and 1.1 RC2 but I am still running into problems. Specifically, I think JdbcTemplate.update(String, Object[], int[]) would probably work, but I'm working in framework code that doesn't want to have to figure out the types for itself. First, a stack trace (including Spring log messages): DEBUG [main] (JdbcTemplate.java:489) - Executing SQL update [update waiver rec set staffing_ind_cd = ?, waiver_status_nm = ?, start_dt = ?, commenter_dt = ?, staffer_dt = ?, commenter_extension_dt = ? where rec.waiver_id_cd = ?] DEBUG [main] (StatementCreatorUtils.java:78) - Setting SQL statement parameter value; columnIndex=1, parameter value='Y', valueClass=java.lang.String, sqlType=-2147483648 DEBUG [main] (StatementCreatorUtils.java:78) - Setting SQL statement parameter value; columnIndex=2, parameter value='Staffing', valueClass=java.lang.String, sqlType=-2147483648 DEBUG [main] (StatementCreatorUtils.java:78) - Setting SQL statement parameter value; columnIndex=3, parameter value='null', valueClass=null, sqlType=-2147483648 org.springframework.jdbc.UncategorizedSQLException: (executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator@4ec78d]): encountered SQLException [Invalid column type]; nested exception is java.sql.SQLException: Invalid column type at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:94) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:281) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:340) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:491) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:543) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:551) at com.bah.krm.util.JdbcTemplateSqlUtils.execute(JdbcTemplateSqlUtils.java:466) at com.bah.krm.ql.sql.impl.ExecutableOperation.execute(ExecutableOperation.java:37) at com.bah.imako.staffing.StaffingService.staff(StaffingService.java:99) at Test.main(Test.java:42) at Test$$FastClassByCGLIB$$27b8b2.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149) at org.springframework.aop.framework.Cglib2AopProxy$MethodInvocationImpl.invokeJoinpoint(Cglib2AopProxy.java:912) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:119) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:627) at Test$$EnhancerByCGLIB$$18959700.main(<generated>) at Test.main(Test.java:54) I believe the line of code to fix is org.springframework.core.StatementCreatorUtils.java:89. This is where Spring is dealing with null values when setting substitutions in a JDBC PreparedStatement. Here is the line of code with some context around it. I included what I think the line should be in a comment like this //!!! public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, Object inValue) throws SQLException { if (logger.isDebugEnabled()) { logger.debug("Setting SQL statement parameter value; columnIndex=" + paramIndex + ", parameter value='" + inValue + "', valueClass=" + (inValue != null ? inValue.getClass().getName() : "null") + ", sqlType=" + sqlType); } if (inValue == null) { if (typeName != null) { ps.setNull(paramIndex, sqlType, typeName); } else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) { //!!! ps.setNull(paramIndex, Types.VARCHAR); ps.setObject(paramIndex, inValue); } else { ps.setNull(paramIndex, sqlType); } } Now the weird thing is that I would expect this to be a cleaner fix: ps.setNull(paramIndex, Types.NULL); //NULL instead of VARCHAR However, that doesn't work for me using Oracle. I don't know if all DBs are like this, but in Oracle null is just null, it doesn't have a type. So specifying a random type, like VARCHAR is OK even though in my particular example the column I was setting to null was a Date. I guess this might be a bug in Oracle's JDBC implementation, but what are the chances they'll prepare a fix within the next few years? I'm not sure if this fix would break things on other DBs or if the change would need to be isolated for Oracle somehow...
    via by Matthew Sgarlata,
  • View Forum Topic
    via by Unknown author,
  • Stored Procedure
    via springframework by gana123
    ,
  • Failed to load batch 2-17
    via symmetricds by sunildims
    ,
    • org.springframework.jdbc.UncategorizedSQLException: (executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator@4ec78d]): encountered SQLException [Invalid column type]; nested exception is java.sql.SQLException: Invalid column type at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:94) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:281) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:340) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:491) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:543) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:551) at com.bah.krm.util.JdbcTemplateSqlUtils.execute(JdbcTemplateSqlUtils.java:466) at com.bah.krm.ql.sql.impl.ExecutableOperation.execute(ExecutableOperation.java:37) at com.bah.imako.staffing.StaffingService.staff(StaffingService.java:99) at Test.main(Test.java:42) at Test$$FastClassByCGLIB$$27b8b2.invoke(<generated>) at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149) at org.springframework.aop.framework.Cglib2AopProxy$MethodInvocationImpl.invokeJoinpoint(Cglib2AopProxy.java:912) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:119) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:627) at Test$$EnhancerByCGLIB$$18959700.main(<generated>) at Test.main(Test.java:54)

    Users with the same issue

    Unknown visitor1 times, last one,