Using the JDBC 8i, 9iR1, Oracle's DATE datatype is mapped to the "java.sql.Timestamp" class. However, the new "ojdbc14.jar" driver maps DATE to "java.sql.Date", and "java.sql.Date" only holds a date (without a time), whereas "java.sql.Timestamp" holds both a date and a time.




          Subject: JDBC 920x Date/TimeStamp mapping
          Type: BULLETIN
          Status: UNDER_EDIT
          Content Type: TEXT/PLAIN
          Creation Date: 29-JUL-2003
          Last Revision Date: 04-OCT-2004


          PURPOSE
          -------
             Clarify the use of oracle.jdbc.V8Compatible property flag
           
          SCOPE & APPLICATION
          -------------------
          JDBC 920x Date/TimeStamp mapping is different from JDBC 8i, 9iR1.
          <TITLE FOR MAIN ARTICLE TEXT>
          -----------------------------
          Summary of  features afftected by oracle.jdbc.V8Compatible.
           
          As of 9.2.0.1.0 Oracle realigned its DATE type with the java.sql.Types.DATE type.
          Prior to this
          java.sql.DATE and  java.sql.TIMESTAMP were mapped to java.sql.Types.TIMESTAMP.
           
          This mapping change applies to JDBC default mapping (i.e when getObject() is
          used for Date column.
           
          example:
          select sysdate from dual;
          ...
          while (rset.next ())  {
          System.out.println("getObject for sysdate  : " +
          rset.getObject(1).getClass().getName());
          System.out.println("getDate for sysdate :" +
          rset.getDate(1).getClass().getName());
          System.out.println("getTimetamp for sysdate :" +
          rset.getTimestamp(1).getClass().getName());
          }
           
          Prior to 9201, this will return
          getObject for sysdate  : java.sql.Timestamp      <<<<
          getDate for sysdate :java.sql.Date
          getTimetamp for sysdate :java.sql.Timestamp
           
          As of 9201 onward the following will be returned
           
          getObject for sysdate  : java.sql.Date        <<<<<
          getDate for sysdate :java.sql.Date            >> no change
          getTimetamp for sysdate :java.sql.Timestamp   >> no change
           
           
           
          Note: java.sql.Date has no time portion whereas java.sql.Timestamp does.
           
           
          With this change in Datatype mapping, some application will fail and/or generate
          incorrect results when JDBC driver is upgraded from 8i/ 9iR1 to 920x JBDC driver.
          To maintain compatibility and keep applications working after upgrade, a compatibility flag was
          Provided.  Developers now have some options:
           
          1>
          Use oracle.jdbc.V8Compatible flag.
           
          JDBC Driver does not detect database version by default.
          To change the compatibility flag for handling TIMESTAMP datatypes,
          connection property 'oracle.jdbc.V8Compatible' can be set to
          'true' and the driver behaves as it behaved in 8i, 901x, 9200
          (with respect to TIMESTAMPs).
          By default the flag is set to 'false'. In OracleConnection constructor
          the driver obtains the server version and set the compatibility flag
          Appropriately.
           
          java.util.Properties prop = new java.util.Properties ();
          prop.put ("oracle.jdbc.V8Compatible", "true");
          prop.put ("user", "scott");
          prop.put ("password", "tiger");
          String url ="jdbc:oracle:thin:@host:port:sid";
          Connection conn = DriverManager.getConnection (url,prop);
           
           
           
          With JDBC 10.1.0.x, in instead of the connection property, the following system
          property can be useed
          java -Doracle.jdbc.V8Compatible=true .....
           
           
           
          Note: This flag is a client only flag that governs the Timestamp and Date mapping.
          It does not affect any Database feature.
           
           
           
          2> use set/getDate and set/getTimestamp   when dealing with Date and TimeStamp column datatype accordingly.
          9i server  supports both Date and Timestamp column types
           
          DATE is mapped to  java.sql.Date and TIMESTAMP is mapped to java.sql.Timestamp
           
          I> using setTimestamp
           
          PreparedStatement pstmt = conn.prepareStatement(
          "SELECT count(*) from  tstable where tscol between ? and ?");
          // tscol of type Timetamp (or it can be Date)
           
          String s = new String("2003-01-14 10:00:00.000000000");
          Timestamp ts1 = Timestamp.valueOf(s);
          pstmt.setTimestamp(1, ts1); // Timestamp
           
          String s2 = new String("2003-01-16 10:00:00.000000000");
          Timestamp ts2 = Timestamp.valueOf(s2);
          pstmt.setTimestamp(2, ts2); // Timestamp
          ...
           
           
          II>using setDate
           
          PreparedStatement pstmt = conn.prepareStatement(
          "SELECT count(*) from  tstable where datecol between ? and ?");
          // datecole of type Date
           
          /*
          pstmt.setDate(1,new java.sql.Date(System.currentTimeMillis()));
          pstmt.setDate(2,new java.sql.Date(System.currentTimeMillis()));
          */
           
          SimpleDateFormat start_dt_in1 = new SimpleDateFormat("2002-09-18 00:00:00");
          SimpleDateFormat start_dt_in2 = new SimpleDateFormat("2003-09-18 00:00:00");
          pstmt.setDate(1,start_dt_in1);
          pstmt.setDate(2,start_dt_in2);
           
           
           
          Summary of  features afftected by oracle.jdbc.V8Compatible.
           
          Is backward compatible (with oracle.jdbc.V8Compatible)?
          
           
           
          * Examples:
          ..
          The following will fail   when using JDBC 9iR1, 9iR2 connecting 817 server since the
          817 did not support Timestamp
           
           
          Connection conn = DriverManager.getConnection(url, "scott",  "tiger");
          // Prepare a statement to cleanup the emp table
          Statement  stmt = conn.createStatement();
          try {
          stmt.execute("delete from EMP where EMPNO = 1");
          } catch (SQLException e) {
          }
          try {
          stmt.execute("INSERT INTO EMP (EMPNO, ENAME, HIREDATE) VALUES (1, 
          'ALI', {ts '2003-04-14 14:19:24.94'})");
          } catch (SQLException e) {
          e.printStackTrace();
          }
           
          Error : Exception in thread "main" java.sql.SQLException: ORA-00904: invalid column name
           
          Solution you need
          1> fix for Bug 2640192 (included in 9204)
          2> oracle.jdbc.V8Compatible", "true"
           
           
           
          In earlier versions of JDBC drivers  SQL FUNCTION "TS" was mapped to "to_date" .   So, the query
           
          select {ts '2002-10-18 18:02:00'} from dual;
          was translated by JDBC to,
          select TO_DATE ('2002-10-18 18:02:00',  'YYYY-MM-DD HH24:MI:SS') from dual;
           
           
          With 9i Timestamp is supported in the database and also by 9203 JDBC Drivers.
          So the query
           
          select {ts '2002-10-18 18:02:00'} from dual;
           
          is now translated  by JDBC to
           
          select TO_TIMESTAMP('2002-10-18 18:02:00', 'YYYY-MM-DD HH24:MI:.SS.FF') from dual;
           
           
          Known issues:  There is some performances issue when set/getTimestamp
          Bug 3037615
          Bug 2770935
          These bugs are very likely duplicate.
           
           
          The following code will no longer work with 9203+ unless V8 flag is set to true
           
          Timestamp start_dt_in = Timestamp.valueOf("2002-09-18 00:00:00");
          Timestamp now_period_start_dt ;
          PreparedStatement stmt = null;
          ResultSet rs = null;
           
          System.out.println("start_dt_in="+  start_dt_in );
           
          try {
          stmt = conn.prepareStatement( "SELECT TRUNC(?) FROM DUAL" );
          stmt.setTimestamp( 1, start_dt_in );
          rs = (OracleResultSet) stmt.executeQuery();
          if ( rs.next() ) {
          now_period_start_dt = rs.getTimestamp( 1 );
          System.out.println("Curr Period Start="+  now_period_start_dt );
          }
           
           
          will generate
          Exception in thread "main" java.sql.SQLException:
          ORA-932: inconsistent datatypes
           
           
          Reason : trunc ( )  supports Date columns and does not support  Timestamp  (this is an RDBMS issue).
          So, you need to set the V8 flag to true
           
          Another bug that changed the Date/Timetamp mapping is  2428427 to comly with
          J2EE 1.3 CTS.  This was fixed in 9014 and it specific to classesdmx*.zip/jar
          (the *dms* jar filed mainly used by iAS/OC4J).  These *dms* jar files, by the
          default value for oracle.jdbc.J2EE13Compliant  is true.  in classes111.zip
          classes12.jar and ojdbc14.jar/zip the default is false.
           
          One can toggel this flag  true/false by
           
          java -Doracle.jdbc.J2EE13Compliant=true|false
           
           
          example of of sample runs:
           
          query used :"select sysdate from dual"
          classes12dms.jar used.
           
           
          Driver Version      Object Type
          ==============      ===========
          9.0.1.3.0         java.sql.Timestamp >> fix for 2428427 NOT included
          9.0.1.4.0         java.sql.Date   >> fix for 2428427 INCLUDED
          9.0.1.5.0         java.sql.Date   >> fix for 2428427 INCLUDE
           
          In JDBC 9014+ ,to keep older (9013) behavior  simply run the application with
           
           
          $java -Doracle.jdbc.J2EE13Compliant=false .....
           
           
          However please note that J2EE 1.3 CTS require that Date to mapped to
          java.sql.Date.
          

          只有注冊用戶登錄后才能發表評論。


          網站導航:
           
          主站蜘蛛池模板: 含山县| 屏南县| 庆安县| 朝阳区| 治多县| 会理县| 延寿县| 宜良县| 新巴尔虎左旗| 扬州市| 年辖:市辖区| 利辛县| 长子县| 大荔县| 青铜峡市| 巴林右旗| 宁陕县| 贵定县| 常德市| 北流市| 岑溪市| 龙游县| 巴林左旗| 金塔县| 芒康县| 会同县| 芜湖市| 阳曲县| 桃园县| 洱源县| 延寿县| 天全县| 元氏县| 合水县| 平湖市| 凌云县| 泾源县| 达拉特旗| 本溪市| 阳谷县| 安龙县|