JDBC incorrectly retrieves date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kenevel - 15 Feb 2007 21:24 GMT

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.getDate(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

Reply to this Message

Kenevel - 15 Feb 2007 23:15 GMT

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 "DateTimeStringFormat". 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

> Hi everyone,
>
[quoted text clipped - 27 lines]
>
> Michael

Reply to this Message

weirdwoolly@googlemail.com - 28 Feb 2007 13:31 GMT

> Hi,
>
[quoted text clipped - 54 lines]
>
> - 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.wss?rs=71&context=SSEPGG&q1=IY79762&uid=swg
1IY79762&loc=en_US&cs=utf-8&lang=en
for some of the details and if you have access to PMR's it is 34350.

Regards,
Paul.

Reply to this Message

Knut Stolze - 16 Feb 2007 07:56 GMT

> Hi everyone,
>
[quoted text clipped - 23 lines]
> 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Kenevel - 16 Feb 2007 11:04 GMT

> 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.SimpleDateFormat;

public class DateTest {

    public static void main(String[] args) throws Exception {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        System.out.println(format.parse("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
DateTimeStringFormat 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

Reply to this Message

Knut Stolze - 16 Feb 2007 15:51 GMT

>> It would be helpful if you provide some sample output of the date in DB2
>> and what your query actually returned.
[quoted text clipped - 16 lines]
> }
> }

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

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Kenevel - 16 Feb 2007 16:28 GMT

> 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.

Reply to this Message

Knut Stolze - 16 Feb 2007 16:56 GMT

>> 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Kenevel - 20 Feb 2007 18:18 GMT

> > 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.

Reply to this Message

Knut Stolze - 20 Feb 2007 20:09 GMT

>> > True, although the JDBC driver may have been expecting DD/MM/YYYY
>> > instead of the provided MM/DD/YYYY.
[quoted text clipped - 5 lines]
> 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message