PC Repaire Windows Error

Problems with JDBC 11g Driver Timestamp and Date

by | Jan 29, 2021

Problems with JDBC 11g Driver Timestamp and Date

The JDBC 11g driver and the JDBC 10g driver differ in handling timestamp variables.

Create a ts timestamp variable in Java

java.sql.Timestamp ts = new java.sql.Timestamp(date.getTime());

If you compare this variable with the Date field in the database, for example

CREATE TABLE TEST_DATE (DATE_FIELD Date)

SELECT COUNT(1) FROM TEST_DATE WHERE DATE_FIELD >= ?

Ts in java declared at the beginning of binding are binding variables

Driven by 10g JDBC, Oracle oracle will truncate the millisecond part of the java timestamp variable and compare it, that is to say

2010-09-15 16:00:00 (date column) >= 2010-09-15 16:00:00.00100 (timestamp value)

But under the JDBC 11g drive, Oracle will convert the Date column to timestamp and add the millisecond part to 0, which is

2010-09-15 16:00:00 (date column) will be converted to 2010-09-15 16:00:00.00000 (timestamp value)

In this case

2010-09-15 16:00:00.00000 (converted value) >= 2010-09-15 16:00:00.00100 (timestamp value) does not hold

When inserting the value, if the Timestamp java variable is used, the processing of 10g and 11g is to cut off the millisecond part.

The first problem is that after upgrading the 10g JDBC driver to 11g, the data results will be different. E.g:

insert into TEST_DATE (DATE_FIELD) values (?); — bind variable timestamp value ts
select count(*)from TEST_DATE where DATE_FIELD=?; — bind variable timestamp value ts

It will always return 1 under 10g JDBC driver, and it is likely to return 0 under 11g JDBC driver.

Another problem is that this conversion will cause the index on the DATE column to be unavailable, similar to the conversion between Number and Varchar2. You can see the INTERNAL_FUNCTION function in the results of the explain plan.

I sent a tar to Oracle. Oracle support does not think this is a bug but thinks it is a correction to the previous processing method and does not provide backward-compatible parameters (such as the previous V8Compatible).

There is currently no other way except to modify the program code. But this is a nightmare for a lot of code search and modification.

Related Articles

How to Unbrick Your A95X MAX TV Box

How to Unbrick Your A95X MAX TV Box

The Story : After Installing a SuperSu in my A95X MAX Tv Box, the box didn't want to reboot, then i did manage to flash a custom rom using the SD card method, but then i didn't know that this will break my Box, doing so my box was dead no boot logo no usb connection...

Thou shalt always(ish) use UTF-8 in PHP

Thou shalt always(ish) use UTF-8 in PHP

Character sets are a mystery to many native-English coders, and if you're not aware of them then you'll occasionally find that things break, or go weird. Unicode was designed to replace all existing character sets with a single universal one. This article explains how...

Using prepared statements in PHP

Using prepared statements in PHP

Whether you're reading from, or writing to a database, using prepared statements are easy, convenient, and secure. So what are they? About these examples The examples here are all for PHP's built-in database layer, PDO, but many other database layers also support...