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

Spring JIRA | Matthew Sgarlata | 1 decade ago
tip
Do you know that we can give you better hits? Get more relevant results from Samebug’s stack trace search.
  1. 0

    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...

    Spring JIRA | 1 decade ago | Matthew Sgarlata
    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
  2. 0

    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...

    Spring JIRA | 1 decade ago | Matthew Sgarlata
    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
  3. 0

    SQL error or missing database (no such table: cas)

    GitHub | 4 years ago | pbharrin
    org.apache.uima.analysis_engine.AnalysisEngineProcessException
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. 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()
    2. Spring Framework
      JdbcTemplate.update
      1. org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:94)
      2. org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:281)
      3. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:340)
      4. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:491)
      5. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:543)
      6. org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:551)
      6 frames
    3. com.bah.krm
      ExecutableOperation.execute
      1. com.bah.krm.util.JdbcTemplateSqlUtils.execute(JdbcTemplateSqlUtils.java:466)
      2. com.bah.krm.ql.sql.impl.ExecutableOperation.execute(ExecutableOperation.java:37)
      2 frames
    4. com.bah.imako
      StaffingService.staff
      1. com.bah.imako.staffing.StaffingService.staff(StaffingService.java:99)
      1 frame
    5. Unknown
      Test$$FastClassByCGLIB$$27b8b2.invoke
      1. Test.main(Test.java:42)
      2. Test$$FastClassByCGLIB$$27b8b2.invoke(<generated>)
      2 frames
    6. IDEA
      MethodProxy.invoke
      1. net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
      1 frame
    7. Spring AOP
      ReflectiveMethodInvocation.proceed
      1. org.springframework.aop.framework.Cglib2AopProxy$MethodInvocationImpl.invokeJoinpoint(Cglib2AopProxy.java:912)
      2. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:119)
      2 frames
    8. Spring Tx
      TransactionInterceptor.invoke
      1. org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56)
      1 frame
    9. Spring AOP
      Cglib2AopProxy$DynamicAdvisedInterceptor.intercept
      1. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:139)
      2. org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:627)
      2 frames
    10. Unknown
      Test.main
      1. Test$$EnhancerByCGLIB$$18959700.main(<generated>)
      2. Test.main(Test.java:54)
      2 frames