Re: JDBC with Timestamp with local time zone column

<thtsang_yh_at_yahoo.com.hk> wrote in message news:1109389553.433904.240820_at_f14g2000cwb.googlegroups.com...
>I am using JDK 1.3 and Oracle 8i JDBC driver against a 9iR2 DB. And I
> am having problem with 'Timestamp with local time zone' columns. And
> actually, I've tested the 9i JDBC driver (Oracle9i 9.2.0.5, freshly
> downloaded from Oracle) and the same problem occur.
>
> Both the DB sever and client environment have set the timezone to
> HongKong or equivalent (GMT+8). I have a table with the following data:
>
> SQL> select * from timezone_test;
>
> TS D
> ----------------------------------- ---------------------
> 26-FEB-05 11.06.28.6042310 AM 26-FEB-05 11.06.28 AM
>
> I run the following SQL in a java program and retrieve the results
> using getString().
>
> select cast(ts as date) ts_d, d d1
> from timezone_test
>
> If I use 8i/9i JDBC driver, the result is
> ts_d: 2005-02-26 03:06:29.0
> d: 2005-02-26 11:06:28.0
> (Seems Oracle is assuming I am in London)
>
> If I use 10g JDBC driver, the result is
> ts_d: 2005-02-26 11:06:29.0
> d: 2005-02-26 11:06:28.0
>
> The SQL returns the following if run in SQL*Plus
> TS_D D1
> --------------------------- ---------------------------
> 26-FEB-05 11.06.29 AM 26-FEB-05 11.06.28 AM
>
> The actual JAVA code sniplet:
>
> String sqlString = "select cast(ts as date) ts_d, d d1 ";
> sqlString += "from timezone_test ";
> System.out.println(sqlString);
> pstmt = conn.prepareStatement(sqlString);
> rs = pstmt.executeQuery();
> rs.next();
> System.out.println("ts_d: " + rs.getString("ts_d"));
> System.out.println("d: " + rs.getString("d1"));
>
> getTimestamp() seems to give the correct result with 9i JDBC. However,
> with the 8i JDBC driver, getTimestamp() throws exception.

Just a coupel of thoughts:

[1] check that your session is usingcorrect timezone, verify through your driver that 'select dbtimezone from dual' and and 'select sessiontimezone from dual' return the correct settings.
[2] if the above are correct, trace the session/turn on jdbc logging and make sure something isn't setting them
[3] don't use the 8i driver Received on Sun Feb 27 2005 - 11:07:37 CST