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
  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 | 3 years ago | pbharrin
    org.apache.uima.analysis_engine.AnalysisEngineProcessException
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Spring JdbcTemplate throwing 17004 Invalid column type whtn I insert

    Stack Overflow | 1 month ago | Jim Archer
    org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO decisions_cache_log ( server_ip, server_hostname, cache_type, load_time) VALUES (?, ?, ?, ?)]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
  6. 0

    Spring-batch, ItemReadListener [SQLException: Invalid state, the Connection object is closed]

    Stack Overflow | 1 year ago | Angel
    org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select count(*) from USER_SERVICE where user_serv_oidval = ?]; SQL state [HY010]; error code [0]; Invalid state, the Connection object is closed.; nested exception is java.sql.SQLException: Invalid state, the Connection object is closed.

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    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