11

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.

Wouter
  • 241
  • 2
  • 8
  • 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. – Axel Amthor Aug 22 '14 at 15:33
  • 1
    Possibly more useful to refer to the [docs for the constructor](http://docs.oracle.com/javase/6/docs/api/java/sql/Date.html#Date(long)), 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? – Alex Poole Aug 22 '14 at 15:34
  • 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.) – Wouter Aug 22 '14 at 15:40
  • 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? – Alex Poole Aug 22 '14 at 16:33
  • 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. – Wouter Aug 27 '14 at 12:55
  • 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? – Wouter Aug 27 '14 at 12:57
  • 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." – Wouter Aug 27 '14 at 13:04
  • @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. – Mark Rotteveel Aug 27 '14 at 15:33
  • 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: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01 –  Aug 27 '14 at 15:34
  • @MarkRotteveel: that's **not** up to the driver. Check the Javadocs for `java.sql.Date`. [They clearly state](http://docs.oracle.com/javase/7/docs/api/java/sql/Date.html): "*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.*" –  Aug 27 '14 at 15:36
  • @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](http://docs.oracle.com/javase/8/docs/api/java/sql/Date.html#Date-long-) – Mark Rotteveel Aug 27 '14 at 15:37

1 Answers1

10

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.

Wouter
  • 241
  • 2
  • 8