Uploaded image for project: 'Spring Framework'
  1. Spring Framework
  2. SPR-9150

simpleJdbcInsert cannot insert Timestamp field on Oracle

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Works as Designed
    • Affects Version/s: 3.0.5
    • Fix Version/s: None
    • Component/s: Core
    • Labels:
      None
    • Last commented by a User:
      true

      Description

      simpleJdbcInsert cannot insert a Timestamp field. Spring throws exception:
      org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
      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.update(JdbcTemplate.java:842)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyHolderInternal(AbstractJdbcInsert.java:445)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyInternal(AbstractJdbcInsert.java:426)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecuteAndReturnKey(AbstractJdbcInsert.java:393)
      at org.springframework.jdbc.core.simple.SimpleJdbcInsert.executeAndReturnKey(SimpleJdbcInsert.java:126)
      at com.amfam.rrs.common.persistence.IntTestCommonDAO.testRetrieveErrorLog_PrimaryKey(IntTestCommonDAO.java:407)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
      at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
      at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:240)
      at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
      at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:180)
      at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
      at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
      at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
      Caused by: java.sql.SQLException: Invalid column type
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8516)
      at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034)
      at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8575)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:238)
      at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:163)
      at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:356)
      at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
      at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.setParameterValues(AbstractJdbcInsert.java:621)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.access$1(AbstractJdbcInsert.java:612)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert$1.createPreparedStatement(AbstractJdbcInsert.java:449)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:580)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:842)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyHolderInternal(AbstractJdbcInsert.java:445)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertAndReturnKeyInternal(AbstractJdbcInsert.java:426)
      at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecuteAndReturnKey(AbstractJdbcInsert.java:393)
      at org.springframework.jdbc.core.simple.SimpleJdbcInsert.executeAndReturnKey(SimpleJdbcInsert.java:126)

      Code:
      MapSqlParameterSource namedParameters = new MapSqlParameterSource();
      namedParameters.addValue("ORD_TS", new Timestamp(1000));
      long id = simpleJdbcInsert.executeAndReturnKey(namedParameters).longValue();

        Activity

        Hide
        licao Li Cao added a comment -

        The following seems working but defeats the purpose of simpleJbdcInsert as the SQL type needs to be supplied. It also prevents usage of java.util.Map.

        namedParameters.addValue("ORD_TS", new Timestamp(1000), Types.TIMESTAMP);

        Show
        licao Li Cao added a comment - The following seems working but defeats the purpose of simpleJbdcInsert as the SQL type needs to be supplied. It also prevents usage of java.util.Map. namedParameters.addValue("ORD_TS", new Timestamp(1000), Types.TIMESTAMP);
        Hide
        cbeams Chris Beams added a comment -

        (added Thomas Risberg as a watcher)

        @Thomas, this is really your area of expertise. Can you take a look and provide advice if not an actual implementation of the fix?

        Show
        cbeams Chris Beams added a comment - (added Thomas Risberg as a watcher) @Thomas, this is really your area of expertise. Can you take a look and provide advice if not an actual implementation of the fix?
        Hide
        thomas.risberg Thomas Risberg added a comment -

        Do you have the DDL for the table you are using?

        -Thomas

        Show
        thomas.risberg Thomas Risberg added a comment - Do you have the DDL for the table you are using? -Thomas
        Hide
        licao Li Cao added a comment -

        Unfortunately I am not allowed to provide the full DDL (there are other VARCHAR2 columns on the table) but this is the definition for the column in question.

        ORD_TS TIMESTAMP(0) NOT NULL,

        I also tried using ResultSetMetadata and a "SELECT" statement to probe the type info of that column from the JDBC driver and the ResultSetMetadata indicates correctly it is Timestamp.

        Show
        licao Li Cao added a comment - Unfortunately I am not allowed to provide the full DDL (there are other VARCHAR2 columns on the table) but this is the definition for the column in question. ORD_TS TIMESTAMP(0) NOT NULL, I also tried using ResultSetMetadata and a "SELECT" statement to probe the type info of that column from the JDBC driver and the ResultSetMetadata indicates correctly it is Timestamp.
        Hide
        thomas.risberg Thomas Risberg added a comment - - edited

        Looks like the problem is the TIMESTAMP(0) declaration. If I use TIMESTAMP or TIMESTAMP(6) the metadata reports this column as an OracleTypes value of 93 which is TIMESTAMP. If I use TIMESTAMP(0) it is reported as a column of type 1111 which is OTHER.

        (You can turn on debug logging for org.springframework.jdbc.core.metadata.TableMetaDataProvider to see the metadata values returned. See http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/constant-values.html for the oracle.jdbc.OracleTypes values)

        Since it is reported as OTHER we simply use the setObject() method which throws this exception. If you specify the type of your parameter value as Types.TIMESTAMP then we will use the setTimestamp() method and the Oracle driver is happy.

        Not sure what we can do to improve this other than recommend using the specific type for the parameter value in this instance.

        -Thomas

        Show
        thomas.risberg Thomas Risberg added a comment - - edited Looks like the problem is the TIMESTAMP(0) declaration. If I use TIMESTAMP or TIMESTAMP(6) the metadata reports this column as an OracleTypes value of 93 which is TIMESTAMP. If I use TIMESTAMP(0) it is reported as a column of type 1111 which is OTHER. (You can turn on debug logging for org.springframework.jdbc.core.metadata.TableMetaDataProvider to see the metadata values returned. See http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/constant-values.html for the oracle.jdbc.OracleTypes values) Since it is reported as OTHER we simply use the setObject() method which throws this exception. If you specify the type of your parameter value as Types.TIMESTAMP then we will use the setTimestamp() method and the Oracle driver is happy. Not sure what we can do to improve this other than recommend using the specific type for the parameter value in this instance. -Thomas
        Hide
        cbeams Chris Beams added a comment -

        Thanks, Thomas. Looks like there isn't much we can do here. Li, reopen if you have further information, but this appears to be a limitation at the Oracle level, and hopefully Thomas' advice provides you the workaround you need.

        Thanks,

        Chris

        Show
        cbeams Chris Beams added a comment - Thanks, Thomas. Looks like there isn't much we can do here. Li, reopen if you have further information, but this appears to be a limitation at the Oracle level, and hopefully Thomas' advice provides you the workaround you need. Thanks, Chris

          People

          • Assignee:
            cbeams Chris Beams
            Reporter:
            licao Li Cao
            Last updater:
            Trevor Marshall
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:
              Days since last comment:
              6 years, 1 week, 3 days ago