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.

Mamba

Just some basic ideas: we fiddle around with dates as well but instantiate a Gregorian calender first, setting all time fragments to 0 and initializing the prepared statements from this object with setTimestamp(...). Works perfect, but the DB isn't ORA but MySql. – Pereyra

Possibly more useful to refer to the docs for the constructor, which in a roundabout way says the time is stripped. Have you verified what actual time the parameter has on the Oracle side? Is it the planDate time (if it has one), or might this be a time zone issue? – Intrust

In the database, only a date is stored, no time. I guess it might be defaulted to 00:00:00 then. (If I check the field information with Toad, 'null time' is flagged.) – Mamba

A date always has a time, even if it midnight. I'm asking what the database is seeing as the bind value. You could query select to_char(?, 'YYYY-MM-DD HH24:MI:SS') from dual and bind the same planDate value, and see what is returned, and how that compares to what you expect to be in planDate? – Intrust

After defining te following variables: java.util.Date utilDate = new Date();, java.sql.Date sqlDate1 = new java.sql.Date(utilDate.getTime()); and java.sql.Date sqlDate2 = java.sql.Date.valueOf("2014-08-27"); and binding them to the statement you proposed (SELECT to_char(?, 'YYYY-MM-DD HH24:MI:SS') testDate FROM dual), I think I got some more insight in the problem. – Mamba

Using the 11.2.0 client, the result is '2014-08-27 00:00:00' twice. Using the 12.1.0 client, the result is '2014-08-27 00:00:00' for sqlDate2, and '2014-08-27 14:47:29' for sqlDate1. So the new client takes the time into account - this is unexpected behaviour to me, what about you? – Mamba

I cannot match this behaviour to what is stated in the API of java.sql.Date (constructor): "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." – Mamba

@AlexPoole Time is not stripped in java.sql.Date, that is up to the driver; if the Oracle driver isn't doing that, it is a bug in the driver. – Pharr

There was a change in the JDBC driver where the driver started to return values from DATE columns as java.sql.Timestamp because otherwise the time part would have been lost (java.sql.Date removes the time). But that change happened somewhere in the 10.x drivers not from 11.x to 12.x You can tell the driver to treate a DATE as a "real" date by supplying a connection parameter. See the JDBC FAQ: oracle.com/technetwork/database/enterprise-edition/… – Jari

@MarkRotteveel: that's not up to the driver. Check the Javadocs for java.sql.Date. They clearly state: "To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated." – Jari

@a_horse_with_no_name But a driver still needs to set time to 00:00 in the local timezone when it receives a java.sql.Date instead of a java.sql.Timestamp. Note that JDBC api documentation is sometimes confusing because it is intended both for driver developers and JDBC users. The 'zero-ing' behavior is also specified in the java.sql.Date constructor – Pharr

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.

Mamba

You might also want to consider filing a bug report with Oracle. – Pharr

Here is a relevant threda on Oracle forums : community.oracle.com/thread/3826309?start=0&tstart=0 Apparently it is intentional and not considered a bug... – Quinta