ResultSet # getDate () semantics

Question

We moved from ojdbc6-11.2.0.3.0 to ojdbc7-12.1.0.1 and noticed a change in the semantics of ResultSet#getDate() . Previously, the returned java.sql.Date would be "normalized" by setting the hours, minutes, seconds and milliseconds to zero according to the contract specified in java.sql.Date . With ojdbc7 this is no longer the case, and java.sql.Date has hours, minutes, seconds and milliseconds set according to the value in the database.

I looked at the Javadoc ResultSet#getDate() and it does not explicitly indicate which of the behaviors is correct. I would suggest that the previous behavior was what was intended for specification. I'm right? Have we encountered a driver error?

Answer 1

This is clearly a mistake, since the documentation for the java.sql.Date class contains

To meet the SQL DATE definition, the millisecond values ​​wrapped by the java.sql.Date instance must be "normalized" by setting the hours, minutes, seconds, and milliseconds to zero in the specific time zone with which the instance is associated.

Answer 2

Oracle does not change the JDBC specification. We updated the Oracle Database JDBC driver documents. If there is something wrong or wrong in the documents, we will fix it. However, no change to the JDBC specification.

Previous driver versions are not compatible. In some places they are zero seconds. In other places, they did not. In 12.1, we made the drivers consistent. The question was what the correct behavior was. In any case, some customers will see changes in behavior.

After a long and energetic debate, we decided that the best thing for Oracle Database clients was not zero seconds. Let me explain.

• The ANSI SQL DATE type does not hold seconds. The JDBC specification mainly assumes ANSI SQL.
• The DATA type of an Oracle database stores seconds. I was sure that the ANSI SQL committee members were fully consistent with ANSI SQL.
• The purpose of JDBC is to open a database. The JDBC specification does not define some abstract database with the intention that drivers implement this abstract database. JDBC drivers should expose database details, not hide them. JDBC defines tools for abstracting database data, but the programmer has the choice to use or not use these tools.
• java.sql.Date does not enforce zero second behavior, although this is easy. The program provides for the forced or non-violent implementation of this behavior.

So, Oracle DATE has seconds. The Oracle JDBC driver provides the Oracle Database. If getDate zero seconds, this will result in data loss. For some users this would not matter, but for others it would be. Because Oracle DATE stores seconds, many Oracle databases store time with second precision in DATE columns. A zero second in these cases would lose information.

If the program sends a date with nonzero seconds before setDate , the program created an inappropriate date. If the driver is zero seconds, the driver discarded information that the provided program and that the database can store. Again, the driver lost the information.

It's easy enough to write SQL or Java to zero seconds for both get and set. It is more difficult to work with information loss, although it is certainly possible.

So, we decided to force drivers to hold seconds for java.sql.Date s sequentially. ResultSet.getDate can build java.sql.Date with non-zero seconds, however this accurately reflects what is in the database. As previously noted, the implementation of the Date could enforce this, but this did not happen. One way to look at this is that the program created the date that it stored it in the database, so this is the responsibility of the program. The driver simply uses the data provided by the program.

I'm sorry that drivers were inconsistent before. We constantly strive to eliminate inconsistencies and strange corner cases. But we have a huge installed base. Every time we change something, even obvious bug fixes, some clients are affected somewhere. Therefore, we are trying to strike a balance between improving drivers and maintaining backward compatibility. 12c is a major release. We took this opportunity to make some more noticeable changes. We regret this, but we believe that this is right for customers in general.

Answer 3

I analyzed this problem in 11g database and I can confirm the behavior of PreparedStatement.setDate(index, java.sql.Date) mentioned by shonky linux user (I tested ojdbc6-11.2.0.4-g vs. ojdbc7-12.1.0.2 ).

While the new behavior probably does not explicitly violate the JDBC specification, it certainly does not fit the concepts of java.sql.Date , java.sql.Time and java.sql.Timestamp and the corresponding data types Types.DATE , Types.TIME and Types.TIMESTAMP .

Thus, although the solution explained by Douglas Suber may make sense from the point of view of “less pain for existing Oracle customers,” it does not seem to be correct from the point of view of JDBC (independent of the DBMS).