Year 2000 problem with Oracle and Java ?

From: <thrav_at_tdk.dk>
Date: Mon, 08 Mar 1999 14:25:25 GMT
Message-ID: <7c0mkc$2jm$1@nnrp1.dejanews.com>

Hello

I have run into some strange problems with Oracle, Java and Oracle's JDBC Thin driver for Oracle 7. It seems as the driver does not handle dates after year 2000 correct. There is no problems in Oracle's Thin JDBC Driver for Oracle 8.

Can anyone confirm this ?

I have made a small test program i Java to illustrate the problem:

---

/*

   This small piece of code illustrates a year 2000 problem in    Oracle JDBC Thin Driver version 1.0 for Oracle 7.    Tested with client : JDK 1.1.6 and JDK 1.2 on Windows NT

               database: Oracle 7.3.2.3.0 on HP Unix B.10.20 U 9000/879

  The program inserts two equal dates into a table. The first date is inserted using the setDate function on a prepared statement, and the second date is inserted using Oracle's to_date function.

  The to inserted dates is then compared, and it turns out that if two equal dates below year 2000 is inserted, Oracle figures out that the dates are the same, but if equal dates after year 2000 is inserted, Oracle thinks they are different.

   (The Oracle JDBC Thin Driver version 1.0 for Oracle 8 works fine)

   To run the program you must replace the strings DATABASE_URL, DATABASE_USERNAME
   and DATABASE_PASSWORD with your own database and username/password.

   (The program uses some deprecated date methods, but it is just a test program)

   Thomas Ravnholt, Tele Danmark EDB
   thrav_at_tdk.dk
*/

import java.sql.*;

class Y2Koracle
{

  final static String TEMP_TABLE       ="y2ktest";
  final static String TEMP_COLUMN1     ="y2kdate1";
  final static String TEMP_COLUMN2     ="y2kdate2";
  final static String DATABASE_URL     ="jdbc:oracle:thin:@host:port:sid";

  final static String DATABASE_USERNAME="username";   final static String DATABASE_PASSWORD="password";

  public static void main (String args [])

       throws SQLException, ClassNotFoundException   {
    Class.forName ("oracle.jdbc.driver.OracleDriver");     Connection conn =DriverManager.getConnection
(DATABASE_URL,DATABASE_USERNAME, DATABASE_PASSWORD);

    System.out.println("Testing JDBC driver "+DriverManager.getDriver

(DATABASE_URL).getMajorVersion()+"."+DriverManager.getDriver
(DATABASE_URL).getMinorVersion()+"\n");

    //try year=1990 month=12 date=30
    createTempTable(conn);
    insertDates(conn, new Date(90, 11, 30));     searchRowsWithEqualDates(conn);
    dropTempTable(conn);

    //try year=2000 month=12 date=30
    createTempTable(conn);
    insertDates(conn, new Date(100, 11, 30));     searchRowsWithEqualDates(conn);
    dropTempTable(conn);
  }

  static void createTempTable(Connection conn)

       throws SQLException, ClassNotFoundException {     Statement createTableStmt = conn.createStatement ();     createTableStmt.executeQuery ("create table "+TEMP_TABLE+" ("+TEMP_COLUMN1+" DATE,"+TEMP_COLUMN2+" DATE)");   }

  static void dropTempTable(Connection conn)

       throws SQLException, ClassNotFoundException {     Statement dropTableStmt = conn.createStatement ();     dropTableStmt.executeQuery ("drop table "+TEMP_TABLE);   }

  static void searchRowsWithEqualDates(Connection conn)

       throws SQLException, ClassNotFoundException {     System.out.println("Searching for rows where first date is equal to second date");

    Statement searchStmt = conn.createStatement ();     ResultSet rset = searchStmt.executeQuery ("select * from "+TEMP_TABLE+" where "+TEMP_COLUMN1+"="+TEMP_COLUMN2);

    if( rset.next()==false ) {
      System.out.println("\tNo rows found!");     }
    else

      do {
        System.out.println("\tFound row :"+ rset.getString (1)+",
"+rset.getString (2)+"\n");
      }  while (rset.next ());

  }

  static void insertDates(Connection conn, Date date)

       throws SQLException, ClassNotFoundException {

    String sql ="insert into "+TEMP_TABLE+" values( ?, to_date('"+
(1900+date.getYear())+"-"+(date.getMonth()+1)+"-"+date.getDate()+"','YYYY-MM-
DD') )";

    PreparedStatement preparedStmt = conn.prepareStatement(sql);

    System.out.println("Inserting this date "+date.toString()+" twice");     preparedStmt.setDate(1, date);
    preparedStmt.executeUpdate();
  }
}

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Mar 08 1999 - 08:25:25 CST