Cayenne
  1. Cayenne
  2. CAY-1323

oracle.sql.TIMESTAMP in Result of query

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.5, 3.0 beta 1
    • Fix Version/s: 3.1M1
    • Component/s: Core Library
    • Labels:
      None

      Description

      Result of query from column of timestamp type was mapped to oracle.sql.TIMESTAMP.
      I think it should be mapped to standard JDBS TIMESTAMP
      I am using latest official Oracle JDBC driver.

      1. OracleTimestampTestMap.map.xml
        1 kB
        Evgeny Ryabitskiy
      2. cayenne.xml
        0.4 kB
        Evgeny Ryabitskiy
      3. OracleTimestampTest.java
        1 kB
        Evgeny Ryabitskiy
      4. CAY-1323_3.1.patch
        10 kB
        Evgeny Ryabitskiy
      5. CAY-1323_3.1_OracleTest.patch
        7 kB
        Evgeny Ryabitskiy
      6. CAY-1323_3.1.patch
        14 kB
        Evgeny Ryabitskiy
      7. CAY-1323_3.1.patch
        14 kB
        Evgeny Ryabitskiy

        Activity

        Hide
        Andrus Adamchik added a comment -

        Could you provide more infomation. Cayenne Mapping can only contain JDBC types , so where was it mapped as oracle.sql.TIMESTAMP?

        Show
        Andrus Adamchik added a comment - Could you provide more infomation. Cayenne Mapping can only contain JDBC types , so where was it mapped as oracle.sql.TIMESTAMP?
        Hide
        Evgeny Ryabitskiy added a comment - - edited

        on 2.0.5 branch:
        Small test that I ran on Oracle.
        Result is:

        class java.sql.Timestamp
        2009-12-02 14:39:59.0
        class oracle.sql.TIMESTAMP
        2009-12-02 00:00:00.0
        class java.sql.Timestamp
        2009-12-02 00:00:00.0

        So... result of this query:

        select to_timestamp(sysdate) as TTT from dual

        is mapped to class oracle.sql.TIMESTAMP that is not a JDBC driver

        Show
        Evgeny Ryabitskiy added a comment - - edited on 2.0.5 branch: Small test that I ran on Oracle. Result is: class java.sql.Timestamp 2009-12-02 14:39:59.0 class oracle.sql.TIMESTAMP 2009-12-02 00:00:00.0 class java.sql.Timestamp 2009-12-02 00:00:00.0 So... result of this query: select to_timestamp(sysdate) as TTT from dual is mapped to class oracle.sql.TIMESTAMP that is not a JDBC driver
        Hide
        Evgeny Ryabitskiy added a comment -

        Driver metadata

        <groupId>com.oracle.ojdbc5</groupId>
        <artifactId>ojdbc5_g</artifactId>
        <version>11.2.0.1.0</version>

        Show
        Evgeny Ryabitskiy added a comment - Driver metadata <groupId>com.oracle.ojdbc5</groupId> <artifactId>ojdbc5_g</artifactId> <version>11.2.0.1.0</version>
        Hide
        Andrus Adamchik added a comment -

        Ok, so that's for SQLTemplate result unmapped to objects... Here the type that we use is what the driver thinks it is (as provided via ResultSetMetadata). #result() directive is the way to override it, but I don't think Cayenne should guess here.

        Show
        Andrus Adamchik added a comment - Ok, so that's for SQLTemplate result unmapped to objects... Here the type that we use is what the driver thinks it is (as provided via ResultSetMetadata). #result() directive is the way to override it, but I don't think Cayenne should guess here.
        Hide
        Evgeny Ryabitskiy added a comment -

        Sory previous was for 2.0.5 Cayenne Lib

        For 3.1-SNAPSHOT (current from trunk) I got Exception:

        17:08:51,337 INFO main QueryLogger:logQueryStart:473 - — will run 1 query.
        17:08:51,337 INFO main QueryLogger:logBeginTransaction:427 - — transaction started.
        17:08:51,337 INFO main QueryLogger:logQuery:357 - select to_timestamp(sysdate) as TTT from dual
        17:08:51,352 INFO main QueryLogger:logQueryError:453 - *** error.
        java.io.StreamCorruptedException: invalid stream header
        at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:764)
        at java.io.ObjectInputStream.<init>(ObjectInputStream.java:277)
        at org.apache.cayenne.access.types.SerializableTypeFactory$SerializableType.toJavaObject(SerializableTypeFactory.java:112)
        at org.apache.cayenne.access.types.ExtendedTypeDecorator.materializeObject(ExtendedTypeDecorator.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31)
        at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235)
        at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123)
        at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
        at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274)
        at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418)
        at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65)
        at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391)
        at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847)
        at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388)
        at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117)
        at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
        at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335)
        at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
        at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049)
        at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038)
        at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:27)
        Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Query exception.
        at org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:545)
        at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:281)
        at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418)
        at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65)
        at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391)
        at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847)
        at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388)
        at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117)
        at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
        at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335)
        at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
        at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049)
        at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038)
        at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:27)
        Caused by: java.io.StreamCorruptedException: invalid stream header
        at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:764)
        at java.io.ObjectInputStream.<init>(ObjectInputStream.java:277)
        at org.apache.cayenne.access.types.SerializableTypeFactory$SerializableType.toJavaObject(SerializableTypeFactory.java:112)
        at org.apache.cayenne.access.types.ExtendedTypeDecorator.materializeObject(ExtendedTypeDecorator.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31)
        at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235)
        at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123)
        at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
        at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274)
        ... 12 more

        Show
        Evgeny Ryabitskiy added a comment - Sory previous was for 2.0.5 Cayenne Lib For 3.1-SNAPSHOT (current from trunk) I got Exception: 17:08:51,337 INFO main QueryLogger:logQueryStart:473 - — will run 1 query. 17:08:51,337 INFO main QueryLogger:logBeginTransaction:427 - — transaction started. 17:08:51,337 INFO main QueryLogger:logQuery:357 - select to_timestamp(sysdate) as TTT from dual 17:08:51,352 INFO main QueryLogger:logQueryError:453 - *** error. java.io.StreamCorruptedException: invalid stream header at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:764) at java.io.ObjectInputStream.<init>(ObjectInputStream.java:277) at org.apache.cayenne.access.types.SerializableTypeFactory$SerializableType.toJavaObject(SerializableTypeFactory.java:112) at org.apache.cayenne.access.types.ExtendedTypeDecorator.materializeObject(ExtendedTypeDecorator.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31) at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197) at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104) at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85) at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235) at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83) at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167) at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123) at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274) at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418) at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65) at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391) at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847) at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388) at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117) at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740) at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335) at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96) at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049) at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038) at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:27) Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Query exception. at org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:545) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:281) at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418) at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65) at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391) at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847) at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388) at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117) at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740) at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335) at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96) at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049) at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038) at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:27) Caused by: java.io.StreamCorruptedException: invalid stream header at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:764) at java.io.ObjectInputStream.<init>(ObjectInputStream.java:277) at org.apache.cayenne.access.types.SerializableTypeFactory$SerializableType.toJavaObject(SerializableTypeFactory.java:112) at org.apache.cayenne.access.types.ExtendedTypeDecorator.materializeObject(ExtendedTypeDecorator.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31) at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197) at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104) at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85) at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235) at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83) at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167) at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123) at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274) ... 12 more
        Hide
        Evgeny Ryabitskiy added a comment -

        Update version affect: for both 2.0 and 3.0
        Sure it can be simulated on 1.2

        Show
        Evgeny Ryabitskiy added a comment - Update version affect: for both 2.0 and 3.0 Sure it can be simulated on 1.2
        Hide
        Evgeny Ryabitskiy added a comment -

        About #result directive.. so I add it:

        <query factory="org.apache.cayenne.map.SQLTemplateBuilder"
        name="select4"
        root="data-map" root-name="OracleTimestampTestMap">
        <property name="cayenne.GenericSelectQuery.fetchingDataRows" value="true"/>
        <sql><![CDATAselect #result('to_timestamp(sysdate)' 'java.util.Date' 'TTT') from dual]></sql>
        </query>

        And got Exception:

        17:20:27,451 INFO main QueryLogger:log:171 - Detected and installed adapter: org.apache.cayenne.dba.oracle.OracleAdapter
        17:20:27,560 INFO main QueryLogger:logQuery:357 - select to_timestamp(sysdate) AS TTT from dual
        17:20:27,779 INFO main QueryLogger:logQueryError:453 - *** error.
        org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Expected an instance of java.util.Date, instead got oracle.sql.TIMESTAMP, column index: 1
        at org.apache.cayenne.access.types.UtilDateType.materializeObject(UtilDateType.java:78)
        at org.apache.cayenne.dba.oracle.OracleUtilDateType.materializeObject(OracleUtilDateType.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31)
        at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235)
        at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123)
        at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
        at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274)
        at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418)
        at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65)
        at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391)
        at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847)
        at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388)
        at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117)
        at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
        at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335)
        at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
        at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049)
        at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038)
        at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:39)
        Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Query exception.
        at org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:545)
        at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:281)
        at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418)
        at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65)
        at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391)
        at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847)
        at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388)
        at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117)
        at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
        at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335)
        at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
        at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049)
        at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038)
        at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:39)
        Caused by: org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Expected an instance of java.util.Date, instead got oracle.sql.TIMESTAMP, column index: 1
        at org.apache.cayenne.access.types.UtilDateType.materializeObject(UtilDateType.java:78)
        at org.apache.cayenne.dba.oracle.OracleUtilDateType.materializeObject(OracleUtilDateType.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50)
        at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31)
        at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104)
        at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235)
        at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167)
        at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123)
        at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
        at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274)
        ... 12 more

        Show
        Evgeny Ryabitskiy added a comment - About #result directive.. so I add it: <query factory="org.apache.cayenne.map.SQLTemplateBuilder" name="select4" root="data-map" root-name="OracleTimestampTestMap"> <property name="cayenne.GenericSelectQuery.fetchingDataRows" value="true"/> <sql><![CDATA select #result('to_timestamp(sysdate)' 'java.util.Date' 'TTT') from dual ]></sql> </query> And got Exception: 17:20:27,451 INFO main QueryLogger:log:171 - Detected and installed adapter: org.apache.cayenne.dba.oracle.OracleAdapter 17:20:27,560 INFO main QueryLogger:logQuery:357 - select to_timestamp(sysdate) AS TTT from dual 17:20:27,779 INFO main QueryLogger:logQueryError:453 - *** error. org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Expected an instance of java.util.Date, instead got oracle.sql.TIMESTAMP, column index: 1 at org.apache.cayenne.access.types.UtilDateType.materializeObject(UtilDateType.java:78) at org.apache.cayenne.dba.oracle.OracleUtilDateType.materializeObject(OracleUtilDateType.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31) at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197) at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104) at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85) at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235) at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83) at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167) at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123) at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274) at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418) at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65) at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391) at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847) at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388) at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117) at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740) at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335) at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96) at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049) at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038) at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:39) Exception in thread "main" org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Query exception. at org.apache.cayenne.access.DataDomainQueryAction.nextQueryException(DataDomainQueryAction.java:545) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:281) at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:418) at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:65) at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:391) at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:847) at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:388) at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:117) at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740) at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:335) at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96) at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1049) at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1038) at ru.apache.cayenne.testoracle.OracleTimestampTest.main(OracleTimestampTest.java:39) Caused by: org.apache.cayenne.CayenneRuntimeException: [v.3.1-SNAPSHOT äåê 02 2009 12:21:12] Expected an instance of java.util.Date, instead got oracle.sql.TIMESTAMP, column index: 1 at org.apache.cayenne.access.types.UtilDateType.materializeObject(UtilDateType.java:78) at org.apache.cayenne.dba.oracle.OracleUtilDateType.materializeObject(OracleUtilDateType.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:50) at org.apache.cayenne.access.jdbc.FullRowReader.readRow(FullRowReader.java:31) at org.apache.cayenne.access.jdbc.JDBCResultIterator.nextRow(JDBCResultIterator.java:197) at org.apache.cayenne.access.jdbc.LimitResultIterator.nextRow(LimitResultIterator.java:104) at org.apache.cayenne.access.jdbc.LimitResultIterator.allRows(LimitResultIterator.java:85) at org.apache.cayenne.access.jdbc.SQLTemplateAction.processSelectResult(SQLTemplateAction.java:235) at org.apache.cayenne.dba.oracle.OracleSQLTemplateAction.processSelectResult(OracleSQLTemplateAction.java:83) at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:167) at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:123) at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87) at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:274) ... 12 more
        Hide
        Evgeny Ryabitskiy added a comment -

        And last one test:

        <query factory="org.apache.cayenne.map.SQLTemplateBuilder"
        name="select4"
        root="data-map" root-name="OracleTimestampTestMap">
        <property name="cayenne.GenericSelectQuery.fetchingDataRows" value="true"/>
        <sql><![CDATAselect #result('to_timestamp(sysdate)' 'java.sql.Timestamp ' 'TTT') from dual]></sql>
        </query>

        result:

        17:33:18,502 INFO main QueryLogger:log:171 - Detected and installed adapter: org.apache.cayenne.dba.oracle.OracleAdapter
        17:33:18,612 INFO main QueryLogger:logQuery:357 - select to_timestamp(sysdate) AS TTT from dual
        17:33:18,831 INFO main QueryLogger:logSelectCount:401 - === returned 1 row. - took 219 ms.
        17:33:18,831 INFO main QueryLogger:logCommitTransaction:434 - +++ transaction committed.
        class oracle.sql.TIMESTAMP
        2009-12-02 00:00:00.0

        So still oracle.sql.TIMESTAMP. Even I asked for 'java.sql.Timestamp'

        Show
        Evgeny Ryabitskiy added a comment - And last one test: <query factory="org.apache.cayenne.map.SQLTemplateBuilder" name="select4" root="data-map" root-name="OracleTimestampTestMap"> <property name="cayenne.GenericSelectQuery.fetchingDataRows" value="true"/> <sql><![CDATA select #result('to_timestamp(sysdate)' 'java.sql.Timestamp ' 'TTT') from dual ]></sql> </query> result: 17:33:18,502 INFO main QueryLogger:log:171 - Detected and installed adapter: org.apache.cayenne.dba.oracle.OracleAdapter 17:33:18,612 INFO main QueryLogger:logQuery:357 - select to_timestamp(sysdate) AS TTT from dual 17:33:18,831 INFO main QueryLogger:logSelectCount:401 - === returned 1 row. - took 219 ms. 17:33:18,831 INFO main QueryLogger:logCommitTransaction:434 - +++ transaction committed. class oracle.sql.TIMESTAMP 2009-12-02 00:00:00.0 So still oracle.sql.TIMESTAMP. Even I asked for 'java.sql.Timestamp'
        Hide
        Andrus Adamchik added a comment -

        Evgeny, while you are investigating this issue, I am going to unset the "Fix Version" for this jira. This is something different from "Affected Version" and it is not clear how and when this needs to be fixed.

        Show
        Andrus Adamchik added a comment - Evgeny, while you are investigating this issue, I am going to unset the "Fix Version" for this jira. This is something different from "Affected Version" and it is not clear how and when this needs to be fixed.
        Hide
        Evgeny Ryabitskiy added a comment -

        I think I finished my Investigation. If you wish I can add some JUnit for this UC.

        You can add "Fix Version" as you wish. But I think it should be fixed in all branches (1.0, 2.0, 3.0).
        It is no expectable behavior.... As you wrote: "Cayenne Mapping can only contain JDBC types"

        How to fix... mm have thoughts that OracleAdapter can help us... need some time to look there inside

        Show
        Evgeny Ryabitskiy added a comment - I think I finished my Investigation. If you wish I can add some JUnit for this UC. You can add "Fix Version" as you wish. But I think it should be fixed in all branches (1.0, 2.0, 3.0). It is no expectable behavior.... As you wrote: "Cayenne Mapping can only contain JDBC types" How to fix... mm have thoughts that OracleAdapter can help us... need some time to look there inside
        Hide
        Evgeny Ryabitskiy added a comment - - edited

        Fixed!
        Improved Timestamp/Date mapping for most of cases

        Patch is only for 3.1 trunk.

        Still need to think what to do with other branches

        Show
        Evgeny Ryabitskiy added a comment - - edited Fixed! Improved Timestamp/Date mapping for most of cases Patch is only for 3.1 trunk. Still need to think what to do with other branches
        Hide
        Evgeny Ryabitskiy added a comment -

        JUnits to check Oracle Date/Timestamp Mapping

        Show
        Evgeny Ryabitskiy added a comment - JUnits to check Oracle Date/Timestamp Mapping
        Hide
        Evgeny Ryabitskiy added a comment -

        Maybe assign this issue to me.. cause I am resolving it?

        Show
        Evgeny Ryabitskiy added a comment - Maybe assign this issue to me.. cause I am resolving it?
        Hide
        Andrus Adamchik added a comment -

        Cool. Assigning this to Evgeny.

        Good stuff. See my comments inline below...

        Index: src/main/java/org/apache/cayenne/access/types/UtilDateType.java
        ===================================================================

        • public Object materializeObject(ResultSet rs, int index, int type) throws Exception {
          + public Date materializeObject(ResultSet rs, int index, int type) throws Exception {
          Date val = null;

        switch (type) {
        @@ -70,28 +69,18 @@
        val = rs.getTime(index);
        break;
        default:

        • // here the driver can "surprise" us
        • // check the type of returned value...
        • Object object = rs.getObject(index);
          -
        • if (object != null && !(object instanceof Date)) { - throw new CayenneRuntimeException( - "Expected an instance of java.util.Date, instead got " - + object.getClass().getName() - + ", column index: " - + index); - }

          -

        • val = (Date) object;
          + val = rs.getDate(index);
          break;
          }

        [Andrus] Not a bad idea, however 'getDate' would lose all time data, so what if that weird object returned by the driver is a custom timestamp... Should we call 'getTimestamp()' instead? Something to figure out.

        Index: src/main/java/org/apache/cayenne/dba/oracle/OracleTimestampType.java
        ===================================================================
        +
        +/**
        + * This is handler for Oracle specific type "oracle.sql.TIMESTAMP"
        + * Oracle official JDBC Driver is mapping SQL TIMESTAMP to this type
        + * Created to solve CAY-1323.
        + */
        +public class OracleTimestampType extends TimestampType {
        +
        + @Override
        + public String getClassName()

        { + return "oracle.sql.TIMESTAMP"; + }

        +}

        [Andrus] This is a new use pattern of ExtendedTypes (till now 'materializeObject' would always return the same class or a subclass of 'getClassName'... I guess it's fine to trick Oracle. Also Oracle has a few more odd types:

        http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/package-summary.html

        that we may want to handle in a similar fashion (at least the date/time ones)

        Show
        Andrus Adamchik added a comment - Cool. Assigning this to Evgeny. Good stuff. See my comments inline below... Index: src/main/java/org/apache/cayenne/access/types/UtilDateType.java =================================================================== public Object materializeObject(ResultSet rs, int index, int type) throws Exception { + public Date materializeObject(ResultSet rs, int index, int type) throws Exception { Date val = null; switch (type) { @@ -70,28 +69,18 @@ val = rs.getTime(index); break; default: // here the driver can "surprise" us // check the type of returned value... Object object = rs.getObject(index); - if (object != null && !(object instanceof Date)) { - throw new CayenneRuntimeException( - "Expected an instance of java.util.Date, instead got " - + object.getClass().getName() - + ", column index: " - + index); - } - val = (Date) object; + val = rs.getDate(index); break; } [Andrus] Not a bad idea, however 'getDate' would lose all time data, so what if that weird object returned by the driver is a custom timestamp... Should we call 'getTimestamp()' instead? Something to figure out. Index: src/main/java/org/apache/cayenne/dba/oracle/OracleTimestampType.java =================================================================== + +/** + * This is handler for Oracle specific type "oracle.sql.TIMESTAMP" + * Oracle official JDBC Driver is mapping SQL TIMESTAMP to this type + * Created to solve CAY-1323 . + */ +public class OracleTimestampType extends TimestampType { + + @Override + public String getClassName() { + return "oracle.sql.TIMESTAMP"; + } +} [Andrus] This is a new use pattern of ExtendedTypes (till now 'materializeObject' would always return the same class or a subclass of 'getClassName'... I guess it's fine to trick Oracle. Also Oracle has a few more odd types: http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/package-summary.html that we may want to handle in a similar fashion (at least the date/time ones)
        Hide
        Evgeny Ryabitskiy added a comment -

        [Andrus] Not a bad idea, however 'getDate' would lose all time data, so what if that weird object returned by the driver is a custom timestamp... Should we call 'getTimestamp()' instead? Something to figure out.

        Re;
        It's UtilDateType.java class and it's expecting to return java.util.Date.

        'getDate' returns 'java.sql.Date'
        There are no deferents between 'java.sql.Date' and ' java.util.Date'

        if we use 'getTimestamp()' we got 'java.sql.Timestamp'
        Only deferents between Timestamp and Date is nanoseconds.
        We don't need nanoseconds, because we converting to ' java.util.Date' and nanoseconds will be dropped.

        So I think it is right method in right place. No lose of time data.

        Show
        Evgeny Ryabitskiy added a comment - [Andrus] Not a bad idea, however 'getDate' would lose all time data, so what if that weird object returned by the driver is a custom timestamp... Should we call 'getTimestamp()' instead? Something to figure out. Re; It's UtilDateType.java class and it's expecting to return java.util.Date. 'getDate' returns 'java.sql.Date' There are no deferents between 'java.sql.Date' and ' java.util.Date' if we use 'getTimestamp()' we got 'java.sql.Timestamp' Only deferents between Timestamp and Date is nanoseconds. We don't need nanoseconds, because we converting to ' java.util.Date' and nanoseconds will be dropped. So I think it is right method in right place. No lose of time data.
        Hide
        Evgeny Ryabitskiy added a comment -

        [Andrus] This is a new use pattern of ExtendedTypes (till now 'materializeObject' would always return the same class or a subclass of 'getClassName'... I guess it's fine to trick Oracle. Also Oracle has a few more odd types:

        http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/package-summary.html

        that we may want to handle in a similar fashion (at least the date/time ones)

        Re:
        Love to do something new
        Yeah! It's a large field of Oracle tunning.
        Next step I am going to work with Oracle CLOBs. (Some fellas from my work have troubles with it). I have some logs.... but not even looked inside... yet..

        Show
        Evgeny Ryabitskiy added a comment - [Andrus] This is a new use pattern of ExtendedTypes (till now 'materializeObject' would always return the same class or a subclass of 'getClassName'... I guess it's fine to trick Oracle. Also Oracle has a few more odd types: http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/package-summary.html that we may want to handle in a similar fashion (at least the date/time ones) Re: Love to do something new Yeah! It's a large field of Oracle tunning. Next step I am going to work with Oracle CLOBs. (Some fellas from my work have troubles with it). I have some logs.... but not even looked inside... yet..
        Hide
        Evgeny Ryabitskiy added a comment -

        Updated. + Some test coverage.
        I think it's ready to commit

        Show
        Evgeny Ryabitskiy added a comment - Updated. + Some test coverage. I think it's ready to commit
        Hide
        Evgeny Ryabitskiy added a comment -

        After some thoughts I decide to use 'getTimeStamp()' (as Andrus suggest).
        I think there is nothing else to do in this issue. I will create another issuer for handling others Oracle types.
        Going to commit.... may I ?
        or I should wait till someone from PMC admit it?

        Show
        Evgeny Ryabitskiy added a comment - After some thoughts I decide to use 'getTimeStamp()' (as Andrus suggest). I think there is nothing else to do in this issue. I will create another issuer for handling others Oracle types. Going to commit.... may I ? or I should wait till someone from PMC admit it?
        Hide
        Evgeny Ryabitskiy added a comment -

        Issue committed to trunk (3.1)
        Add fix versions to 3.1.
        Not closing issue because we should decide if we are going to fix other branches

        Show
        Evgeny Ryabitskiy added a comment - Issue committed to trunk (3.1) Add fix versions to 3.1. Not closing issue because we should decide if we are going to fix other branches
        Hide
        Evgeny Ryabitskiy added a comment -

        Committed only to 3.1 trunk.

        Show
        Evgeny Ryabitskiy added a comment - Committed only to 3.1 trunk.

          People

          • Assignee:
            Evgeny Ryabitskiy
            Reporter:
            Evgeny Ryabitskiy
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development