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

    Data Truncation error in timesten

    658831
    658831 Sep 10, 2008 10:30 AM
      Hi,

      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?
      • 1290 Views
      • Tags:
        • 1. Re: Data Truncation error in timesten
          ChrisJenkins-Oracle
          ChrisJenkins-Oracle Sep 10, 2008 3:07 PM (in response to 658831)
          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()?

          Chris
          • 2. Re: Data Truncation error in timesten
            658831
            658831 Sep 11, 2008 6:32 AM (in response to ChrisJenkins-Oracle)
            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
              ChrisJenkins-Oracle
              ChrisJenkins-Oracle Sep 11, 2008 7:41 AM (in response to 658831)
              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');

              and

              INSERT INTO Z VALUES(TT_SYSDATE);

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

              Chris