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,0) not null,
created date,
description varchar2(500),
status varchar2(2),
title varchar2(200) not null,
typeid varchar2(2),
primary key (id)
);
create table TimeSheetItem (
id number(10,0) not null,
description varchar2(255) not null,
hours float not null,
occurDate date not null,
status varchar2(2),
bizCase_id number(10,0) not 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.