473,888 Members | 1,573 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

JDBC incorrectly retrieves date

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

Feb 15 '07 #1
9 5464
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

Feb 15 '07 #2
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
Feb 16 '07 #3
On 16 Feb, 07:56, Knut Stolze <sto...@de.ibm. 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

Feb 16 '07 #4
Kenevel wrote:
On 16 Feb, 07:56, Knut Stolze <sto...@de.ibm. 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
Feb 16 '07 #5
On 16 Feb, 15:51, Knut Stolze <sto...@de.ibm. 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.

Feb 16 '07 #6
Kenevel wrote:
On 16 Feb, 15:51, Knut Stolze <sto...@de.ibm. 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
Feb 16 '07 #7
On 16 Feb, 16:56, Knut Stolze <sto...@de.ibm. 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.

Feb 20 '07 #8
Kenevel wrote:
On 16 Feb, 16:56, Knut Stolze <sto...@de.ibm. 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
Feb 20 '07 #9
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.

Feb 28 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4681
by: Andrea | last post by:
Hello, I spent now several hours searching the google groups without finding an solution. I am kind of Newbie to DB2 and JSP and therefore working with JSP4Dummies (not sure whether I should recommend this book): Invoking the following jsp-file in the browser I get an ClassNotFoundException for the COM.ibm.db2.jdbc.app.DB2Driver. JSP-File
2
4479
by: charl | last post by:
Hi, My apologies in advance if my terminology is slightly confused, I am new to all this. We have previously been running some SQL on DB2 (v7) utilising a type 3 driver, and have made the change to DB2 v8 with a type 2 DB2 Universal JDBC Driver. SQL that ran successfully under the old setup now throws the following error...
1
4122
by: Dr X | last post by:
I have a problem updating date values using Java via JDBC-ODBC to Access. <snip rs.updateTimestamp(theIndex,theTimeStamp); rs.updateRow(); <snip> (where theTimeStamp is a valid Java Timestamp and theIndex is the column index in the resultset.) is the way specified in the Java API. The (very non-specific) error reported
1
11232
by: balleyman47 | last post by:
getting the following error when executing an insert statement: java.lang.IllegalArgumentException: Date/Time must be JDBC format running on UDB 8.2 fix pack 10 using IBM type IV driver. Query in JAVA program works successfully in old environment, converting from UDB 7 to above UDB version. Values passed during execution of insert statement (inserting into table A) are derived from date fields on another table B but will
9
8860
by: Roger | last post by:
Hi ! My dev team is getting this error when they run big load program : COM.ibm.db2.jdbc.DB2Exception: CLI0601E Invalid statement handle or statement is closed. SQLSTATE=S1000 .. The application runs fine when they run small loads. I checked the Db2 client FP..its on DV2 V8 FP10. Any input is greatly appreciated.
0
2473
by: Lars Hylleberg | last post by:
In my company we are runing java Webshere applications on Windows platform and connecting to DB2 V8 on z/OS mainframe. Until now we have used the type 2 JDBC driver, which are using the DB2 Connect software to obtain connection. Right now we are planning to move to type 4 JDBC driver, which supports direct connection to DB2 V8 on z/OS using native TCP/IP. That is, we don't need the DB2 Connect overhead anymore. But we seem to have a problem...
1
1891
by: wnaveenkumar | last post by:
package com.trewport.orderprocess.action; import java.io.*; import java.sql.*; import java.util.*; import java.util.Date; import java.lang.Object; import javax.servlet.*; import javax.servlet.http.*;
19
20257
by: robtyketto | last post by:
Greetings, I have the following code below which allows the date to be added via a JDBC connection as a STRING. The value of dateString is inserted into the MS ACCESS database. What is the easiest method to add the current date/time (dd/mm/yy hh:mm:ss) via JDBC in DATE format i.e. convert STRING to DATE or format the DATE without changing its type and insert it. I'm a newbie to Java/Jsp too.
0
2876
by: caesarkim | last post by:
I need to connect to the db (created with "IBM-943" codeset) on DB2 AIX . I am having a problem retrieving data with japanese character in 'where' clause something like this. SELECT * FROM \"TEST\".\"TABLE1\" WHERE COL2 like '%㈱%' It returns nothing. So I want to try to set the codepage(the same code page that db uses) in JDBC level to see if it retrieves something. Here is what I am trying. But I am not sure if I am using valid...
0
11178
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10882
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10438
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7148
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4243
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.