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

    Oracle Date and JDBC Timestamp

    tpearsall

      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

        • 1. Re: Oracle Date and JDBC Timestamp
          tpearsall

          One correction to the above post:

          The OCI drivers throws:
          ORA-00932: inconsistent datatypes

          during the save while the thing driver throws:
          ORA-03115: unsupported network datatype or representation

          • 2. Re: Oracle Date and JDBC Timestamp
            tpearsall

            If only I could type..

            One correction to the above post:

            The OCI drivers throws:
            ORA-00932: inconsistent datatypes

            during the save while the THIN driver throws:
            ORA-03115: unsupported network datatype or representation

            • 3. Re: Oracle Date and JDBC Timestamp
              tpearsall

              Here is a snippet from my jbosscmp-jdbc.xml that is generated by Xdoclet:


              <ejb-name>EventEntity</ejb-name>
              <table-name>EVENT</table-name>
              <cmp-field>
              <field-name>eventId</field-name>
              <column-name>EVENT_ID</column-name>
              <jdbc-type>VARCHAR</jdbc-type>
              <sql-type>VARCHAR</sql-type>
              </cmp-field>
              .......
              <cmp-field>
              <field-name>eventEffDateTime</field-name> <column-name>EVENT_EFF_DATE_TIME</column-name>
              <jdbc-type>TIMESTAMP</jdbc-type>
              <sql-type>DATE</sql-type>
              </cmp-field>
              ......

              • 4. Re: Oracle Date and JDBC Timestamp
                tpearsall

                With some help form others in different forums, apparently the classes12.zip for Oracle 8.1.7 maps the Oracle DATE time to java.sql.Timestamp, but the ojdbc14.jar and classes12.zip from 9i map Oracle Date to java.sql.Date.

                I assume the change is due to Oracle adding the TIMESTAMP data type in 9i, but I'm not sure. It have just been really nice if Oracle renamed the file instead of having at least 2 classes12.zip that have different implemenations.

                - Todd

                • 5. Re: Oracle Date and JDBC Timestamp
                  lenisha

                  Hi guys,
                  What is the solution for DATE field in Oracle
                  with OCI8 driver ?
                  I have this setting in
                  In jbosscmp-jdbc.xml setting jdbc-type to TIMESTAMP and sql-type=DATE

                  and error like that
                  The OCI drivers throws:
                  ORA-00932: inconsistent datatypes

                  Does anybody knows how to make it work ?

                  Thanks a lot

                  • 6. Re: Oracle Date and JDBC Timestamp
                    hans.noemmer

                    I had the same problem, that only the data protion was stored.

                    It worked, when I defined the abstract getter/setter with the type java.util.Date instead of java.sql.Date.

                    I am using the default datasource mapping for Oracle8 in jbosscmp-jdbc.xml

                    • 7. Re: Oracle Date and JDBC Timestamp
                      lenisha

                      Hi,
                      I have this abstract getter/setters and it does not help. What version of JBoss you are using ?
                      And what classes12.jar ?


                      Thanks alot.