7 Replies Latest reply on Nov 14, 2003 9:27 PM by Elena Neroslavskaya

    Oracle Date and JDBC Timestamp

    tpearsall Newbie

      I've been search through the forums and found similar posts, but not the answer so hear goes.

      Using Jboss 3.2.2 and Oracle 8.1.6 or 8.1.7 when storing a date/time to the database with the newer Oracle JDBC drivers I can only save the date or time portion, not both. In the table I have column of type DATE. I've tried different version of the JDBC drivers from Oracle with varying degrees of success in CMPs (and previously with direct JDBC calls, but I'm not doing it that way anymore).

      Using classes12.jar (1081kb not sure where I got this one) Thin:
      - In jbosscmp-jdbc.xml setting jdbc-type to TIMESTAMP and sql-type=DATE
      - Works as expected is just out of date and has other issues (namely CLOBs)

      Using classes12.jar (1081kb not sure where I got this one) OCI:
      - In jbosscmp-jdbc.xml setting jdbc-type to TIMESTAMP and sql-type=DATE
      - Throws a link error when starting Jboss

      Using classes12.jar (1169kb from Oracle) Thin:
      - In jbosscmp-jdbc.xml setting jdbc-type to TIMESTAMP and sql-type=DATE
      - When creating the CMP the following error is thrown:java.sql.SQLException: ORA-03115: unsupported network datatype or representation

      Using classes12.jar (1169kb from Oracle) Thin:
      - In jbosscmp-jdbc.xml setting jdbc-type to DATE and sql-type=DATE
      - Only date portion is stored since java.sql.Date "normalizes" the time to 12:00:00.0

      Using ojdbc14.jar (1169kb from Oracle) Thin:
      - In jbosscmp-jdbc.xml setting jdbc-type to TIMESTAMP and sql-type=DATE
      - When creating the CMP the following error is thrown:java.sql.SQLException: ORA-03115: unsupported network datatype or representation

      Using ojdbc14.jar (1169kb from Oracle) OCI8:
      - In jbosscmp-jdbc.xml setting jdbc-type to TIMESTAMP and sql-type=DATE
      - When creating the CMP the following error is thrown:java.sql.SQLException: ORA-03115: unsupported network datatype or representation


      It seems like another case of Oracle making an "odd" interpretion the JDBC spec. Am I missing something or is there a better solution that stays non-database specific?

      Thanks,
      Todd