ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrey Rogov" <andrey.ro...@gmail.com>
Subject Re: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
Date Tue, 18 Mar 2008 23:44:38 GMT
Jess, there is an offer to attack a problem under a correct angle :),

1. I have created two fields
     - TIMESTAMP(6) WITH TIME ZONE
     - TIMESTAMP(6) WITH LOCAL TIME ZONE

2. Has changed xml file. Instead of sql has written stored procedure
     <parameterMap id="timeZoneParameters" class="Map">
            <parameter property="timezone"  jdbcType="VARCHAR" javaType="
java.lang.String" mode="INOUT" />
     </parameterMap>
     <procedure id="setTimeZone" parameterMap="timeZoneParameters">
             { call projects.tz_set_and_show( ? ) }
     </procedure>

3. Stored procedure to set and show time zone .
    CREATE OR REPLACE PROCEDURE tz_set_and_show (tz_in IN VARCHAR2 := null)
    IS
    BEGIN

        IF tz_in IS NOT NULL
             THEN EXECUTE IMMEDIATE 'alter session set time_zone = ''' ||
tz_in || '''' ;
        END IF;
     --   DBMS_OUTPUT.put_line ( 'SESSIONTIMEZONE   = '|| SESSIONTIMEZONE )
;
     --   DBMS_OUTPUT.put_line ( 'CURRENT_TIMESTAMP = '|| CURRENT_TIMESTAMP
) ;
     --   DBMS_OUTPUT.put_line ( 'LOCALTIMESTAMP = ' || LOCALTIMESTAMP ) ;
     --   DBMS_OUTPUT.put_line ( 'SYS_EXTRACT_UTC (LOCALTIMESTAMP) = '||
sqlexpr('SYS_EXTRACT_UTC (LOCALTIMESTAMP)') );

    END ;

4.  Model .
          - DAO set timezone 'America/Denver'
          - DAO getdata
          - DAO set timezone 'Turkey'
          - DAO getdata

5.  Results

    DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100022} PreparedStatement: { call    projects.tz_set_and_show( ? ) }
    DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100022} Parameters: [America/Denver]
    Result [ 18-МАР-08 06.19.40,000000 PM +03:00, *** 18-МАР-08 08.19.40,000000
AM, 8 ***]

    DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100027} PreparedStatement: { call projects.tz_set_and_show( ? ) }
DEBUG [http-8080-Processor23] java.sql.PreparedStatement debug-
{pstm-100027} Parameters: [Turkey]
   Result: [ 18-МАР-08 06.19.40,000000 PM +03:00, *** 18-МАР-08 05.19.40,000000
PM, 8 ***]


Andrey .


2008/3/18, Jesse Reimann <jreimann@ctigroup.com>:
>
>  Thanks for the suggestion, but that didn't help since it is the actual
> oracle jdbc driver that is throwing the exception and just setting the
> session within oracle doesn't stop prevent this.
>
>
>
> I think I'm just going to abandon this approach and attack the problem
> from a different angle and handle all my time zone conversion stuff
> explicitly within Oracle. Thinking about it more setting a connection level
> local time zone won't solve the business need since we need to allow the
> time zone to be changed on the individual SQL statement execution basis.
>
>
>
>
>
> Jesse
>
>
>  ------------------------------
>
> *From:* Andrey Rogov [mailto:andrey.rogov@gmail.com]
> *Sent:* Monday, March 17, 2008 9:43 PM
> *To:* user-java@ibatis.apache.org
> *Subject:* Re: Retrieving Oracle column with data type TIMESTAMP WITH
> LOCAL TIME ZONE
>
>
>
> Jess,
>    try to execute sql operator ALTER SESSION before accessing TIMESTAMP
> WITH LOCAL TIME ZONE data
>
>    <update id="setTimeZone" parameterClass="java.lang.String">
>         ALTER SESSION SET time_zone = #timezonevalue#
>    </update>
>
>
>
>
>
> 2008/3/17, Jesse Reimann <jreimann@ctigroup.com>:
>
> I'm trying to retrieve an Oracle column that is defined as TIMESTAMP WITH
> LOCAL TIME ZONE. Currently I'm receiving an error stating
>
>
>
> --- Cause: *java.sql.SQLException*: Session Time Zone not set!
>
>
>
> Looking into it I need to specify the Session Time Zone as part of the
> Oracle JDBC Connection properties.
>
>
>
> I found this in an Oracle document when searching for a solution:
>
>
>
> 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.
>
>
>
>
>
> So my question is how would I go about calling the setSessionTimeZone
> method of the OracleConnection when using iBATIS (and Spring).
>
>
>
> Thanks,
>
>
>
> Jesse Reimann
>
>
>
Mime
View raw message