JDBC incorrectly retrieves date

[Kenevel]

Hi everyone,

Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling

resultSet.getDa te(paramIndex)

and it's giving me a date that's way off.

The reason that I find this bizarre is that I can see the correct date
value being transferred over the wire (localhost) using the Wireshark
packet-sniffer; also because it parses timestamp values correctly from
the same statement.

This is exposed in a unit-test which is run with a classpath that
includes the following files:

DB2_HOME/java/db2jcc.jar
DB2_HOME/java/db2jcc_licence_ cu.jar

The result-set is returned as a cursor from a stored procedure which
reads from a temporary table. The value is read from a column in that
table declared as type DATE.

I'm sure I'm missing something pretty obvious so I thought I'd ask
first before getting to involved in rooting out the problem!

Cheers

Michael

[Kenevel]

Hi,

I thought I'd post an update to this as I've fixed the problem. It
seems that it was to do with the CLI CFG settings you can retrieve by
keying in

db2 get cli cfg

The setting in question is "DateTimeString Format". I set this to ISO
value in the [common] section following the instructions here: http://
publib.boulder. ibm.com/infocenter/db2luw/v9/index.jsp?topic =/
com.ibm.db2.udb .admin.doc/doc/r0002071.htm

A value wasn't previously set. I had previously set the
DB2_SQLROUTINE_ PREPOPTS to "DATETIME ISO".

One db2stop and db2start later and I was away.

Perhaps if someone has some more information about what is going on
under the covers that'd be great.

Cheers

Michael

On 15 Feb, 21:24, "Kenevel" <michael.guy... @gmail.comwrote :

Hi everyone,

Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling

resultSet.getDa te(paramIndex)

and it's giving me a date that's way off.

The reason that I find this bizarre is that I can see the correct date
value being transferred over the wire (localhost) using the Wireshark
packet-sniffer; also because it parses timestamp values correctly from
the same statement.

This is exposed in a unit-test which is run with a classpath that
includes the following files:

DB2_HOME/java/db2jcc.jar
DB2_HOME/java/db2jcc_licence_ cu.jar

The result-set is returned as a cursor from a stored procedure which
reads from a temporary table. The value is read from a column in that
table declared as type DATE.

I'm sure I'm missing something pretty obvious so I thought I'd ask
first before getting to involved in rooting out the problem!

Cheers

Michael

[Knut Stolze]

Kenevel wrote:

Hi everyone,

Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling

resultSet.getDa te(paramIndex)

and it's giving me a date that's way off.

The reason that I find this bizarre is that I can see the correct date
value being transferred over the wire (localhost) using the Wireshark
packet-sniffer; also because it parses timestamp values correctly from
the same statement.

This is exposed in a unit-test which is run with a classpath that
includes the following files:

DB2_HOME/java/db2jcc.jar
DB2_HOME/java/db2jcc_licence_ cu.jar

The result-set is returned as a cursor from a stored procedure which
reads from a temporary table. The value is read from a column in that
table declared as type DATE.

I'm sure I'm missing something pretty obvious so I thought I'd ask
first before getting to involved in rooting out the problem!

It would be helpful if you provide some sample output of the date in DB2 and
what your query actually returned.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Kenevel]

On 16 Feb, 07:56, Knut Stolze <[e-mail]. comwrote:

>
It would be helpful if you provide some sample output of the date in DB2 and
what your query actually returned.

Hi Knut,

Thanks for taking the time to have a look at this. The date was being
returned in US format, so MM/DD/YYYY and I guess the JDBC driver was
expecting it in a different format. Hence the ISO date 1973-03-29
(expressed in yyyy-MM-dd) was being transferred as 29/03/1973 and
being converted into a Java date with the value 1975-05-03 - I guess
26-or-so months ahead of the actual date. This mis-conversion can be
demonstrated with the following code:

import java.text.Simpl eDateFormat;

public class DateTest {

public static void main(String[] args) throws Exception {
SimpleDateForma t format = new SimpleDateForma t("yyyy-MM-dd");
System.out.prin tln(format.pars e("1973-29-03"));
}
}

I guess this doesn't actually answer your request for more
information, Knut, but I believe it clears up the problem. Setting the
DateTimeStringF ormat to ISO on my linux box made the difference and it
now works. Interestingly I had assumed that DB2 would transfer date
values as a millisecond value from 1970, but I guess this isn't
flexible enough for all possible dates.

Cheers

Michael

[Knut Stolze]

Kenevel wrote:

On 16 Feb, 07:56, Knut Stolze <[e-mail]. comwrote:

>>
It would be helpful if you provide some sample output of the date in DB2
and what your query actually returned.

Thanks for taking the time to have a look at this. The date was being
returned in US format, so MM/DD/YYYY and I guess the JDBC driver was
expecting it in a different format. Hence the ISO date 1973-03-29
(expressed in yyyy-MM-dd) was being transferred as 29/03/1973 and
being converted into a Java date with the value 1975-05-03 - I guess
26-or-so months ahead of the actual date. This mis-conversion can be
demonstrated with the following code:

import java.text.Simpl eDateFormat;

public class DateTest {

public static void main(String[] args) throws Exception {
SimpleDateForma t format = new SimpleDateForma t("yyyy-MM-dd");
System.out.prin tln(format.pars e("1973-29-03"));
}
}

Makes sense to me, although I would have expected an exception being thrown.
After all, your date does not match with the indicated format.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Kenevel]

On 16 Feb, 15:51, Knut Stolze <[e-mail]. comwrote:

Makes sense to me, although I would have expected an exception being thrown.
After all, your date does not match with the indicated format.

True, although the JDBC driver may have been expecting DD/MM/YYYY
instead of the provided MM/DD/YYYY.

[Knut Stolze]

Kenevel wrote:

On 16 Feb, 15:51, Knut Stolze <[e-mail]. comwrote:

>Makes sense to me, although I would have expected an exception being
thrown. After all, your date does not match with the indicated format.

True, although the JDBC driver may have been expecting DD/MM/YYYY
instead of the provided MM/DD/YYYY.

I don't see what the JDBC driver has to do with that. Your sample code
shows an independent little Java program.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Kenevel]

On 16 Feb, 16:56, Knut Stolze <[e-mail]. comwrote:

Kenevel wrote:

True, although the JDBC driver may have been expecting DD/MM/YYYY
instead of the provided MM/DD/YYYY.

I don't see what the JDBC driver has to do with that. Your sample code
shows an independent little Java program.

DB2 transmits its date values as string values over the wire, at least
that was what showed up when using Wireshark to sniff the local
connection. Unless the DB2 JDBC driver implements its deserialisation
code in a native binary, it will use a similar method to the one
outlined in the sample Java app - hence no exception and also an
explanation for why the dates were off.

[Knut Stolze]

Kenevel wrote:

On 16 Feb, 16:56, Knut Stolze <[e-mail]. comwrote:

>Kenevel wrote:

True, although the JDBC driver may have been expecting DD/MM/YYYY
instead of the provided MM/DD/YYYY.

I don't see what the JDBC driver has to do with that. Your sample code
shows an independent little Java program.

DB2 transmits its date values as string values over the wire, at least
that was what showed up when using Wireshark to sniff the local
connection.

Yes, that's right.

Unless the DB2 JDBC driver implements its deserialisation
code in a native binary, it will use a similar method to the one
outlined in the sample Java app - hence no exception and also an
explanation for why the dates were off.

Then the client would have to know how the date string was encoded at the
server. And because many people still insist on not using a sane
formatting for dates, i.e. the ISO format, there is often not a unique way
to get the right results.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[weirdwoolly]

On Feb 15, 11:15 pm, "Kenevel" <michael.guy... @gmail.comwrote :

Hi,

I thought I'd post an update to this as I've fixed the problem. It
seems that it was to do with the CLI CFG settings you can retrieve by
keying in

db2 get cli cfg

The setting in question is "DateTimeString Format". I set this to ISO
value in the [common] section following the instructions here: http://
publib.boulder. ibm.com/infocenter/db2luw/v9/index.jsp?topic =/
com.ibm.db2.udb .admin.doc/doc/r0002071.htm

A value wasn't previously set. I had previously set the
DB2_SQLROUTINE_ PREPOPTS to "DATETIME ISO".

One db2stop and db2start later and I was away.

Perhaps if someone has some more information about what is going on
under the covers that'd be great.

Cheers

Michael

On 15 Feb, 21:24, "Kenevel" <michael.guy... @gmail.comwrote :

Hi everyone,

Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling

resultSet.getDa te(paramIndex)

and it's giving me a date that's way off.

The reason that I find this bizarre is that I can see the correct date
value being transferred over the wire (localhost) using the Wireshark
packet-sniffer; also because it parses timestamp values correctly from
the same statement.

This is exposed in a unit-test which is run with a classpath that
includes the following files:

DB2_HOME/java/db2jcc.jar
DB2_HOME/java/db2jcc_licence_ cu.jar

The result-set is returned as a cursor from a stored procedure which
reads from a temporary table. The value is read from a column in that
table declared as type DATE.

I'm sure I'm missing something pretty obvious so I thought I'd ask
first before getting to involved in rooting out the problem!

Cheers

Michael- Hide quoted text -

- Show quoted text -

We had a similar issue with dates in java when we migrated from v7 to
v8 at the end of 2005 and raised PMR 34350 with IBM for a resolution.

We demonstrated the problem through returning CURRENT DATE from a
stored procedure both as a result set value and an out parameter.

The out parameter worked fine, however the result set behaved in the
same way as yours i.e. 2005-09-21 became 2006-09-09

We resolved the issues ourselves by making various code changes but
also binding the stored procedures with DATETIME JIS (we found ISO to
be less compatible than JIS for our application)

The PMR was closed with no changes to be made to the v8 documentation
to reflect our issue but an offer was made to update the v9
documentation and a permanent restriction was put in place for v8
although I am not sure what that is to be honest.

See http://www-1.ibm.com/support/docview...=utf-8&lang=en
for some of the details and if you have access to PMR's it is 34350.

Regards,
Paul.