Wierd JDBC behaviour

[Fabio Luis De Paoli]

I am getting wierd results reading a datetime year to fraction(3) from an
Informix IDS 7.30U C6 database. My code is compiled by VisualCafe 3
(JDK 1.1) and my tests were done in two machines:

- An HP-UX 10.20 server with JRE 1.1 and the Informix JDBC 1.5 JAR,
with TZ set to BRA3VER (GMT -2 at this moment)
- My Windows98 PC with VisualCafe's JRE 1.1 and the Informix JDBC
1.5 JAR set to Rio TimeZone with DST (GMT -2 at this moment)

The results, obtained executing the exact same .class file (source listed
further ahead) are:

Running on the HP-UX server:
Debug get, AWB: 2405604751, db TS: 2000-12-12 13:58:41.000 GMT,
long : 976629521000

Running on my PC:
Debug get, AWB: 2405604751, db TS: 2000-12-12 16:58:41.000 GMT,
long : 976640321000

In dbaccess this is what I get with statment select loguser, logdate from
shipser where awb = "2405604751" :
loguser logdate
pegadm 2000-12-12 11:58:41.180

The code being executed is listed below:

/////////////// START OF CODE //////////////////

anSQL = new String("select logdate "
+ "from shipser "
+ "where awb = ?");
aStmt = aConnex.prepareStatement(anSQL);
aStmt.setString(1, awbNumber);
aResult = aStmt.executeQuery();
aResult.next();

java.sql.Timestamp dbTimeStamp = aResult.getTimestamp("logdate");

SimpleDateFormat formatter =
(SimpleDateFormat) Sage.getDateFormatter().clone();
// Instruction above equal to
// new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z", Sage.STD_LOCALE);
// followed by a clone, where STD_LOCALE = Locale.US;

formatter.setTimeZone(new SimpleTimeZone(0, "GMT"));
formatter.applyPattern("yyyy-MM-dd HH:mm:ss.SSS z");

System.out.println("Debug AWB: " + awbNumber
+ ", db TS: " + formatter.format(dbTimeStamp)
+ ", long : " + dbTimeStamp.getTime());

/////////////// END OF CODE //////////////////

It seems that the JDBC driver is applying a GMT offset when it reads the
datetime... and a different offset depending on which machine the code is
executing. This does not make sense to me - the driver should not touch
the Date value. Am I missing something?

Thanks

Fabio

[Ruchi Dhar]

I never see anytime JDBC adding offset to the Date/Time. Try to print the
actual value returned by the driver with out formatting. Use the following
code to format your prints

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");

System.out.println(format.format(dbTimeStamp));

Fabio Luis De Paoli <fpaoli@br.dhl.com> wrote:
>This is a multi-part message in MIME format.
>--------------C975D7FCAA9FB0030C26A1B6
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>I am getting wierd results reading a datetime year to fraction(3) from an
>Informix IDS 7.30U C6 database. My code is compiled by VisualCafe 3
>(JDK 1.1) and my tests were done in two machines:
>
>- An HP-UX 10.20 server with JRE 1.1 and the Informix JDBC 1.5 JAR,
>with TZ set to BRA3VER (GMT -2 at this moment)
>- My Windows98 PC with VisualCafe's JRE 1.1 and the Informix JDBC
>1.5 JAR set to Rio TimeZone with DST (GMT -2 at this moment)
>
>The results, obtained executing the exact same .class file (source listed
>further ahead) are:
>
>Running on the HP-UX server:
>Debug get, AWB: 2405604751, db TS: 2000-12-12 13:58:41.000 GMT,
>long : 976629521000
>
>Running on my PC:
>Debug get, AWB: 2405604751, db TS: 2000-12-12 16:58:41.000 GMT,
>long : 976640321000
>
>In dbaccess this is what I get with statment select loguser, logdate from
>shipser where awb = "2405604751" :
>loguser logdate
>pegadm 2000-12-12 11:58:41.180
>
>The code being executed is listed below:
>
>/////////////// START OF CODE //////////////////
>
>anSQL = new String("select logdate "
> + "from shipser "
> + "where awb = ?");
>aStmt = aConnex.prepareStatement(anSQL);
>aStmt.setString(1, awbNumber);
>aResult = aStmt.executeQuery();
>aResult.next();
>
>java.sql.Timestamp dbTimeStamp = aResult.getTimestamp("logdate");
>
>SimpleDateFormat formatter =
> (SimpleDateFormat) Sage.getDateFormatter().clone();
>// Instruction above equal to
>// new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z", Sage.STD_LOCALE);
>// followed by a clone, where STD_LOCALE = Locale.US;
>
>formatter.setTimeZone(new SimpleTimeZone(0, "GMT"));
>formatter.applyPattern("yyyy-MM-dd HH:mm:ss.SSS z");
>
>System.out.println("Debug AWB: " + awbNumber
> + ", db TS: " + formatter.format(dbTimeStamp)
> + ", long : " + dbTimeStamp.getTime());
>
>/////////////// END OF CODE //////////////////
>
>It seems that the JDBC driver is applying a GMT offset when it reads the
>datetime... and a different offset depending on which machine the code is
>executing. This does not make sense to me - the driver should not touch
>the Date value. Am I missing something?
>
>Thanks
>
>Fabio
>
>
>
>
>
>--------------C975D7FCAA9FB0030C26A1B6
>Content-Type: text/x-vcard; charset=us-ascii;
> name="fpaoli.vcf"
>Content-Transfer-Encoding: 7bit
>Content-Description: Card for Fabio Luis De Paoli
>Content-Disposition: attachment;
> filename="fpaoli.vcf"
>
>begin:vcard
>ne Paoli;Fabio Luis
>tel;fax:55 11 3618-3311
>tel;work:55 11 3618-3231
>x-mozilla-html:TRUE
>url:www.dhl.com.br
>org:Business Consultant for Ops/Logistics;Information Services - Brazil
>adr:;;Av Santa Marina 1660;São Paulo;SP;05036-001;Brazil
>version:2.1
>email;internet:fpaoli@br.dhl.com
>title:<img src=http://www.dhl.com/art/logo.gif>
>fn:Fabio Luis De Paoli
>end:vcard
>
>--------------C975D7FCAA9FB0030C26A1B6--
>

[Fabio Luis De Paoli]

I get the same result when I print the internal long using the command below (it
is equal to the number of miliseconds since 1970-01-01 0:00:00 GMT):

System.out.println("Debug AWB: " + awbNumber
+ ", db TS: " + formatter.format(dbTimeStamp)
+ ", long : " + dbTimeStamp.getTime());

It shows the dates are different:

Running on the HP-UX server: long : 976629521000
Running on my PC: long : 976640321000

See? They are 10800000 miliseconds different, which means 3 hours different
(3*60*60*1000).... Super wierd...

Fabio

Ruchi Dhar wrote:

> I never see anytime JDBC adding offset to the Date/Time. Try to print the
> actual value returned by the driver with out formatting. Use the following
> code to format your prints
>
> SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
>
> System.out.println(format.format(dbTimeStamp));
>
> Fabio Luis De Paoli <fpaoli@br.dhl.com> wrote:
> >This is a multi-part message in MIME format.
> >--------------C975D7FCAA9FB0030C26A1B6
> >Content-Type: text/plain; charset=us-ascii
> >Content-Transfer-Encoding: 7bit
> >
> >I am getting wierd results reading a datetime year to fraction(3) from an
> >Informix IDS 7.30U C6 database. My code is compiled by VisualCafe 3
> >(JDK 1.1) and my tests were done in two machines:
> >
> >- An HP-UX 10.20 server with JRE 1.1 and the Informix JDBC 1.5 JAR,
> >with TZ set to BRA3VER (GMT -2 at this moment)
> >- My Windows98 PC with VisualCafe's JRE 1.1 and the Informix JDBC
> >1.5 JAR set to Rio TimeZone with DST (GMT -2 at this moment)
> >
> >The results, obtained executing the exact same .class file (source listed
> >further ahead) are:
> >
> >Running on the HP-UX server:
> >Debug get, AWB: 2405604751, db TS: 2000-12-12 13:58:41.000 GMT,
> >long : 976629521000
> >
> >Running on my PC:
> >Debug get, AWB: 2405604751, db TS: 2000-12-12 16:58:41.000 GMT,
> >long : 976640321000
> >
> >In dbaccess this is what I get with statment select loguser, logdate from
> >shipser where awb = "2405604751" :
> >loguser logdate
> >pegadm 2000-12-12 11:58:41.180
> >
> >The code being executed is listed below:
> >
> >/////////////// START OF CODE //////////////////
> >
> >anSQL = new String("select logdate "
> > + "from shipser "
> > + "where awb = ?");
> >aStmt = aConnex.prepareStatement(anSQL);
> >aStmt.setString(1, awbNumber);
> >aResult = aStmt.executeQuery();
> >aResult.next();
> >
> >java.sql.Timestamp dbTimeStamp = aResult.getTimestamp("logdate");
> >
> >SimpleDateFormat formatter =
> > (SimpleDateFormat) Sage.getDateFormatter().clone();
> >// Instruction above equal to
> >// new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z", Sage.STD_LOCALE);
> >// followed by a clone, where STD_LOCALE = Locale.US;
> >
> >formatter.setTimeZone(new SimpleTimeZone(0, "GMT"));
> >formatter.applyPattern("yyyy-MM-dd HH:mm:ss.SSS z");
> >
> >System.out.println("Debug AWB: " + awbNumber
> > + ", db TS: " + formatter.format(dbTimeStamp)
> > + ", long : " + dbTimeStamp.getTime());
> >
> >/////////////// END OF CODE //////////////////
> >
> >It seems that the JDBC driver is applying a GMT offset when it reads the
> >datetime... and a different offset depending on which machine the code is
> >executing. This does not make sense to me - the driver should not touch
> >the Date value. Am I missing something?
> >
> >Thanks
> >
> >Fabio
> >
> >
> >
> >
> >
> >--------------C975D7FCAA9FB0030C26A1B6
> >Content-Type: text/x-vcard; charset=us-ascii;
> > name="fpaoli.vcf"
> >Content-Transfer-Encoding: 7bit
> >Content-Description: Card for Fabio Luis De Paoli
> >Content-Disposition: attachment;
> > filename="fpaoli.vcf"
> >
> >begin:vcard
> >ne Paoli;Fabio Luis
> >tel;fax:55 11 3618-3311
> >tel;work:55 11 3618-3231
> >x-mozilla-html:TRUE
> >url:www.dhl.com.br
> >org:Business Consultant for Ops/Logistics;Information Services - Brazil
> >adr:;;Av Santa Marina 1660;São Paulo;SP;05036-001;Brazil
> >version:2.1
> >email;internet:fpaoli@br.dhl.com
> >title:<img src=http://www.dhl.com/art/logo.gif>
> >fn:Fabio Luis De Paoli
> >end:vcard
> >
> >--------------C975D7FCAA9FB0030C26A1B6--
> >