Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- From: "Robert Klemme" <bob.news@xxxxxxx>
- Date: Tue, 6 Dec 2005 10:33:07 +0100
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@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...
robert
.
- Follow-Ups:
- Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- From: Robert Klemme
- Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- References:
- Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- From: Joe Weinstein
- Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- From: Robert Klemme
- Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- Prev by Date: Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- Next by Date: Pivot query
- Previous by thread: Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- Next by thread: Re: Ora10 JDBC Driver with TIMESTAMP WITH LOCAL TIME ZONE
- Index(es):
Relevant Pages
|
