3 Replies Latest reply on Sep 11, 2008 7:41 AM by ChrisJenkins-Oracle

    Data Truncation error in timesten


      I am getting data truncation error in TimesTen Driver. Following is the stack trace for the same.

      java.sql.DataTruncation: Data truncation
      at com.timesten.jdbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:319)
      at com.timesten.jdbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:243)
      at TimesTenTest3.<init>(TimesTenTest3.java:274)
      at TimesTenTest3.main(TimesTenTest3.java:317)

      If the date field is set using setDate of prepared statement error is not coming but setTimestamp throws the above mentioned error. If the same application is run on Oracle it works fine but the problem does occur with timesten.

      Is anyone aware of such issue?
        • 1. Re: Data Truncation error in timesten
          What TypeMode is in use for the datastore? How is the column defined in the TT database? What is the SQL statement being executed? What value is being passed to setDate()/setTimestamp()?

          • 2. Re: Data Truncation error in timesten
            I am firing simple insert statement.

            Column type is Date

            The following statement gives data truncation error

            pstmtCDRData.setTimestamp(3, new Timestamp(new Date().getTime()));

            while following does not

            pstmtCDRData.setDate(3,new java.sql.Date(new Date().getTime()));
            • 3. Re: Data Truncation error in timesten
              This is because an Oracle Date (and a TimesTen DATE in Oracle TypeMode) includes a time valuebut the precision of the time value is only down to seconds. A TIMESTAMP has a precison down to microseconds. When one tries to insert such a value into a DATE column then the value has to be truncated (loss of the fractional seconds part). The issue here appears to be an API (JDBC or ODBC) issue since performing such an INSERT in SQL succeeds with no error:

              CREATE TABLE Z(D1 DATE);

              INSERT INTO Z VALUES (DATE '2008-01-01');

              succeeeds as does

              INSERT INTO Z VALUES(TIMESTAMP '2008-01-01 12:00:00.123456');



              Since TimesTen's objective is to be as compatible as possible with Oracle I would consider this a bug. I will raise a bug report for this. In the meantime the only workaround is to use setDate which should work correctly with both Oracle and TimesTen (and is really what you should be using anyway since it is a DATE column).