Robert Klemme wrote:
> Robert Klemme wrote:
>> 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
>
> PS: Strangely enough setSessionTimeZone() seems to be superfluous on
> Windows - same driver and DB...
Update: now I've got code in place that fetches the TZ id from the session
and sets it locally via setSessionTimeZone(). Works like a charm, but now
I still cannot fetch TIMEZONE WITH LOCAL TIMESTAMP on all platforms:
java.sql.SQLException: Timezone not supported
at
oracle.jdbc.driver.TimestampltzAccessor.TimeZoneAdjust(TimestampltzAccesso
r.java:394)
at
oracle.jdbc.driver.TimestampltzAccessor.getTimestamp(TimestampltzAccessor.
java:295)
at
oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.ja
va:796)
...
Shouldn't the driver at least support those time zones that the products
knows? This grows into a major nuisance....
Regards
robert
Received on Tue Dec 06 2005 - 07:52:45 CST
