Changed behaviour of java.sql.Date after OJBC client upgrade

Question

After an upgrade of the OJDBC client from version 11.2.0 to 12.1.0, I experience a different behavior in binding a java.sql.Date object to a PreparedStatement.

In the prepared statement, a host variable "f.plan_date = ?" should be binded with the value of a java.util.Date object, being an input obtained elsewhere in the code. The column data type in the Oracle table is "DATE" and only the date part should be taken into account - time is irrelevant.

I translated the java.util.Date object in a java.sql.Date object in the following way: statementRegisterJobs.setDate(3, new java.sql.Date(planDate.getTime()));

This worked fine with the 11.2.0 client. However, things tend to go wrong after the upgrade towards 12.1.0. No records are retrieved anymore. After hours of debugging, I found out that the issue was related to the date variable. The following way of working gives me my records back: statementRegisterJobs.setDate(3, java.sql.Date.valueOf("2014-08-21"));

Could someone clarify this behaviour? The java.util.Date object can eventually have a time component, and I have the undefined feeling that this could be related to the problem somehow. On the other hand, the following items should argue that a time component is neglected in a java.sql.Date, no matter how the object was constructed...

• In the Java 6 API for java.sql.Date, I found the following statement: "This method is deprecated and should not be used because SQL Date values do not have a time component." (method 'getHours()'). So this would mean that the time aspect is neglected when converting the java.util.Date into a java.sql.Date.
• This is confirmed by the information in the constructor documentation: "Constructs a Date object using the given milliseconds time value. If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT."
• Moreover, I'm not able to get a possible time aspect out of the java.sql.Date object: toString() gives me only the date, getHours() throws an exception.
• And how can this be related to an update in a JDBC client?

Any thoughts are appreciated :) Thank you very much in advance.

Answer 1

Opposed to what the Java API states, when creating a java.sql.Date object by passing a milliseconds time value the time aspect seems to be stored in the object and is not defaulted to zero when using the 12.1.0 OJDBC driver.

This is the test I set up:

java.util.Date utilDate = new Date();
java.sql.Date sqlDate1 = new java.sql.Date(utilDate.getTime());
java.sql.Date sqlDate2 = java.sql.Date.valueOf("2014-08-27");

I prepared the following statement (SELECT to_char(?, 'YYYY-MM-DD HH24:MI:SS') testDate FROM dual), binded both sqlDate1 and sqlDate2 and got the following results.

1. With driver version 11.2.0

   • sqlDate1: 2014-08-27 00:00:00
   • sqlDate2: 2014-08-27 00:00:00

2. With driver version 12.1.0

   • sqlDate1: 2014-08-27 14:47:29
   • sqlDate2: 2014-08-27 00:00:00

This is not in line with the documentation in the API:

If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

However, knowing this I can fix the issue by forcing the time information of the sql date object to be midnight.