Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE

Joe Weinstein wrote:
> Robert Klemme wrote:
>
>> All,
>>
>> DB is Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod. JDBC
>> driver is the newest driver for Ora 10.2.
>>
>> I got a strange error that I could find no explanation of.
>>
>> DDL:
>>
>> CREATE TABLE ag_hourlymaster
>> (
>> timestampid NOT NULL,
>> groupid NUMBER(5) NOT NULL,
>> urlid NUMBER(14) NOT NULL,
>> categoryid NUMBER(6) DEFAULT 1 NOT NULL,
>> userid NUMBER(9) NOT NULL,
>> referrerid NUMBER(9) NOT NULL,
>> serverinfoid NUMBER(9) NOT NULL,
>> applianceid NUMBER(9) NOT NULL,
>> transcodeid NUMBER(9) NOT NULL,
>> actionid NUMBER(5) NOT NULL,
>> status NUMBER(9) NOT NULL,
>> userinfoid NUMBER(9) NULL,
>> refcount NUMBER(9) NULL,
>> avgresponsetime NUMBER(14,3) NULL,
>> avgbytecount NUMBER(14,3) NULL,
>> avgbytesdropped NUMBER(14,3) NULL,
>> avgpacketsrequested NUMBER(14,3) NULL,
>> avgpacketsserved NUMBER(14,3) NULL,
>> avgpacketsresent NUMBER(14,3) NULL,
>> avgpacketsdropped NUMBER(14,3) NULL,
>> avgplaytime NUMBER(14,3) NULL,
>> bandwidth NUMBER(14,3) NULL,
>> avgconnecttime NUMBER(14,3) NULL,
>> avgbrowsetime NUMBER(14,3) NULL,
>> reqmodid NUMBER(6) NULL,
>> respmodid NUMBER(6) NULL,
>> virusid NUMBER(9) DEFAULT 1 NOT NULL,
>> CONSTRAINT PK_hourlymaster PRIMARY KEY
>> ( timestampid, applianceid, virusid, categoryid,
>> transcodeid, serverinfoid, actionid,
>> userid, status, urlid, referrerid, groupid)
>> );
>>
>> (There are 1,313,489 records in the table.)
>>
>> DML:
>>
>> select min(timestampid) from ag_hourlymaster
>>
>> When executed this exception shows up when trying to read the ts
>> value via ResultSet.getTimestamp():
>>
>> java.sql.SQLException: Session Time Zone not set!
>> at
>>

oracle.jdbc.driver.TimestampltzAccessor.getTimestamp(TimestampltzAccessor.
>> java:271)
>> at
>>

oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.ja
>> va:796)
>> ...
>>
>> When executing this and fetching results via ResultSet.getObject() I
>> see this
>>
>> oracle.sql.TIMESTAMPLTZ_at_1751a9e
>>
>> I.e. getObject() succeeds and the following toString() seems to be
>> the default implementation in class Object.
>>
>> Also, "select sessiontimezone from dual" returns "+01:00", i.e. the
>> database thinks a session TZ is set.
>>
>> Since there is no ORA message and the stacktrace originates in the
>> JDBC driver I suspect it's a JDBC driver issue. Also, this seems to
>> happen *only* on RHEL so far. DB is on a Windows box.
>>
>> Any ideas?
>>
>> Kind regards
>>
>> robert
>
> Hi. The issue is that the driver itself needs to have a time zone
> set. You have to call setSessionTimeZone() on the connection object.
> HTH,
Hi Joe, thanks for the informative feeback (as always)! With your help I actually found documentation about this:

"Before accessing TIMESTAMP WITH LOCAL TIME ZONE data, call the OracleConnection.setSessionTimeZone(String regionName) method to set the session time zone. When this method is called, the JDBC driver sets the session time zone of the connection and saves the session time zone so that any TIMESTAMP WITH LOCAL TIME ZONE data accessed through JDBC can be adjusted using the session time zone."
http://download-uk.oracle.com/docs/cd/B19306_01/java.102/b14355/oraint.htm#sthref281

An additional remark for Oracle JDBC driver developers since I didn't find a feedback channel for these issues yet: IMHO for consistency reasons every OracleConnection should by default inherit the session timezone that the Oracle instance assigns the connection ("select sessiontimezone from dual"). Reason: when doing queries that refer to date parts (year, month, hour, minutes etc.) consistent results will only be obtained when both settings are in sync. This is the most reasonable default and I don't see any reason why the driver on connection creation cannot use this default.

The alternative would be to derive the session timezone from TimeZone.getDefault() and then setting the session time zone on the server accordingly via "alter session set time_zone='<TZ>'".

In any case having a usable default would be better than leaving this uninitialized.

Kind regards

    robert Received on Tue Dec 06 2005 - 02:52:01 CST