Kava Pava Gava Tava Nava Zava Java

everything about Java
随笔 - 15, 文章 - 0, 评论 - 1, 引用 - 0
数据加载中……

Set oracle.jdbc.V8Compatible=true when using higher version JDBC driver to access 8i databases, or strange things will happen

Sometimes days work - encountering the problem, confusing, testing, replacing this and that, getting source code, tracing into source codes, searching on Internet, and finally you get an answer which can be phased in just one sentence. This one is the case. The answer I finally get is:

You shall set oracle.jdbc.V8Compatible=true when using higher version JDBC driver to access 8i databases, or strange things will happen.

Here's my story...

Strange things happened when working with hibernate + oracle database. At first I thought it was hibernate that massed things up and for days I was tracing between lines of hibernate source codes and observing what was hibernate doing. At last I thought that this is a JDBC driver bug. So, I wrote a test without hibernate but doing what hibernate had done to reproduce the bug. Here it is:

(1) The schema (generated by hbm2ddl):


    
drop table BizCase cascade constraints;

    
drop table TimeSheetItem cascade constraints;

    
drop sequence hibernate_sequence;

    
create table BizCase (
        id 
number(10,0not null,
        created date,
        description 
varchar2(500),
        status 
varchar2(2),
        title 
varchar2(200not null,
        typeid 
varchar2(2),
        
primary key (id)
    );

    
create table TimeSheetItem (
        id 
number(10,0not null,
        description 
varchar2(255not null,
        hours 
float not null,
        occurDate date 
not null,
        status 
varchar2(2),
        bizCase_id 
number(10,0not null,
        
primary key (id)
    );

    
alter table TimeSheetItem 
        
add constraint FK_TIMESHEET_BIZCASE 
        
foreign key (bizCase_id) 
        
references BizCase;

    
create sequence hibernate_sequence;

Note: if the id columns are defined as "id number not null", i.e. without precision and scale, the bug won't show up.

(2) The test code, simple and straight-forward:

import java.sql.*;

public class JDBCTest {

  
public static void main (String args []) throws SQLException
  {

    
// open connection
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn 
=
      DriverManager.getConnection (
"jdbc:oracle:thin:@your.oracleserver.com:1521:yourdbname""username""password");

    
// get id from sequence, as hibernate did
    Statement stmt = conn.createStatement ();
    ResultSet rset 
= stmt.executeQuery ("select hibernate_sequence.nextval from dual");
    rset.next();
    Integer seq 
= rset.getInt(1);
    System.out.println(
"Sequence is "+String.valueOf(seq));

    
// insert bizcase
    PreparedStatement insert = conn.prepareStatement("insert into BizCase (created, description, status, title, typeid, id) values (?, ?, ?, ?, ?, ?)");

    
// the following lines do what exactly hibernate did
    Timestamp ts = new Timestamp( ( (java.util.Date) (new java.util.Date()) ).getTime() );
    insert.setTimestamp(
1, ts);
    insert.setString(
2"test case");
    insert.setString(
3"OP");
    insert.setString(
4"test case title");
    insert.setString(
5"TP");
    insert.setInt(
6, ( (Integer) seq ).intValue());

    insert.addBatch();
    
int[] ret = insert.executeBatch();
    System.out.print(
"Insert is successful and returned {");
    
for (int i : ret) 
        System.out.print(String.valueOf(i)
+" ");
    System.out.println(
"}");
    conn.commit();
    insert.close();
    
    System.out.println(
"Commit successful. ");
    conn.close();
  }
}

(3) Output from running the above:

D:\>java -cp C:\Oracle\Ora81\jdbc\lib\classes12.zip;. JDBCTest
Sequence is 1
Insert is successful and returned {-2 }
Commit successful.

D:\>java -cp C:\Oracle\Ora81\jdbc\lib\classes12.zip;. JDBCTest
Sequence is 2
Insert is successful and returned {-2 }
Commit successful.

D:\>java -cp C:\Oracle\Ora81\jdbc\lib\classes12.zip;. JDBCTest
Sequence is 3
Insert is successful and returned {-2 }
Commit successful.

......


(4) The bug, from sqlplus, see what's inserted into the database:
SQL> select id, title from bizcase;

        ID TITLE
---------- ----------------------------------------
-(.000E+46 test case title
8.1000E+49 test case title
4.5000E+25 test case title


Apparently, the id column is corrupted, even beyond what is defined in schema, and this cause Sqlplus crash from time to time at client side.

(5) Test matrix - which driver has this bug?

Driver Result
with classes12.zip at C:\Oracle\Ora81\jdbc\lib, Oracle JDBC Drivers release 8.1.6 OK, no problem found
with "oracle 8.1.7 driver\classes12.zip", oracle 8.1.7 driver OK, no problem found
with "oracle 10i\classes12.jar", oracle 10i driver ID corrputed. Bug
with "oracle9i driver\classes12.jar", oracle 9i driver ID corrputed. Bug
with "oracle 10i\ojdbc14.jar", oracle 10i driver ID corrputed. Bug
with "oracle9i driver\ojdbc14.jar", oracle 9i driver ID corrputed. Bug

(6) Solution:

Searching with google, some other guys seem to have noticed the same or a related issue. See (http://forums.oracle.com/forums/thread.jspa?messageID=1659839). It suggests "" props.put("oracle.jdbc.V8Compatible", "true"); "".

In searching, crossed this guy's blog. Finally, someone encuntered the same problem with me. (http://sorphi.javaeye.com/blog/290045) If I had seen this one earlier, it would have saved me a lot of efforts.

Change the codes of the test application like this, and test again with those drivers, the problem disappeared.

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

java.util.Properties prop 
= new java.util.Properties();
prop.put(
"oracle.jdbc.V8Compatible""true"); 
prop.put(
"user""user_name");
prop.put(
"password""your_password");

Connection conn 
= DriverManager.getConnection(
                
"jdbc:oracle:thin:@your.server.com:1521:dbname", prop);

To set the property in hibernate, add this line into hibernate.cfg.xml:

<property name="hibernate.connection.oracle.jdbc.V8Compatible">true</property>

After solving the problem, I can find articles suggesting "oracle.jdbc.V8Compatible" everywhere. However, before I knew this, I need to trace from line to line in the source codes. The lesson is, to the library writters:

PLEASE:
 
Do more check, find problem earlier, and throw an exception with clear message indicating what went wrong and how to fix.
 
THANK YOU

- FROM DISTRESSED DEVELOPER USING YOUR LIB.

posted on 2009-12-09 15:04 bing 阅读(4008) 评论(0)  编辑  收藏 所属分类: Hibernate


只有注册用户登录后才能发表评论。


网站导航: