Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 74595 invoked from network); 19 Mar 2008 15:36:17 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 19 Mar 2008 15:36:17 -0000 Received: (qmail 30392 invoked by uid 500); 19 Mar 2008 15:36:11 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 30378 invoked by uid 500); 19 Mar 2008 15:36:11 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 30367 invoked by uid 99); 19 Mar 2008 15:36:11 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Mar 2008 08:36:11 -0700 X-ASF-Spam-Status: No, hits=2.7 required=10.0 tests=HTML_MESSAGE,SPF_SOFTFAIL X-Spam-Check-By: apache.org Received-SPF: softfail (nike.apache.org: transitioning domain of jreimann@ctigroup.com does not designate 64.132.87.194 as permitted sender) Received: from [64.132.87.194] (HELO EXCHANGE2K3.ctigroup.com) (64.132.87.194) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 Mar 2008 15:35:21 +0000 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C889D6.CE5D771B" Subject: RE: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE Date: Wed, 19 Mar 2008 11:35:04 -0400 Message-ID: In-Reply-To: <43c99d630803181644r10abff7yfc6c38711d04eacf@mail.gmail.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE Thread-Index: AciJUgpQazO8rNTFRLCU41RlsQPwzQAgruLg References: <43c99d630803171843l3a872858t5190fa71b877ba28@mail.gmail.com> <43c99d630803181644r10abff7yfc6c38711d04eacf@mail.gmail.com> From: "Jesse Reimann" To: "Andrey Rogov" Cc: X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------_=_NextPart_001_01C889D6.CE5D771B Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: quoted-printable Thanks for the help... I've implemented it and it is really helpful with = setting the session timezone but I still end up with my original problem = with the TIMESTAMP(6) WITH LOCAL TIME ZONE failing because of Session = Time Zone not being set assertion being thrown from the jdbc driver when = I try to retrieve data. Since you seem to have this working could you = post the definition of your resultmap where the TIMESTAMP(6) WITH LOCAL = TIME ZONE is being assigned? Also which Oracle jdbc driver are you using = as well? =20 I get this error stack: =20 Exception in thread "main" = org.springframework.jdbc.UncategorizedSQLException: SqlMapClient = operation; uncategorized SQLException for SQL []; SQL state [null]; = error code [0];=9A=9A=20 --- The error occurred in = com/ctigroup/brickyard/dataaccess/sqlmappings/TEST_GET_CDDRPT_SqlMap.xml.= =9A=20 --- The error occurred while applying a result map.=9A=20 --- Check the TEST_TESTPROC.CallDataDetailResult.=9A=20 --- Check the result mapping for the 'localInitiationTime' property.=9A=20 --- Cause: java.sql.SQLException: Session Time Zone not set!; nested = exception is com.ibatis.common.jdbc.exception.NestedSQLException:=9A=9A=20 --- The error occurred in = com/ctigroup/brickyard/dataaccess/sqlmappings/TEST_GET_CDDRPT_SqlMap.xml.= =9A=20 --- The error occurred while applying a result map.=9A=20 --- Check the TEST_TESTPROC.CallDataDetailResult.=9A=20 --- Check the result mapping for the 'localInitiationTime' property.=9A=20 --- Cause: java.sql.SQLException: Session Time Zone not set! =9A=9A=9A=9A=9A at = org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate= (SQLStateSQLExceptionTranslator.java:124) =9A=9A=9A=9A=9A at = org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.trans= late(SQLErrorCodeSQLExceptionTranslator.java:322) =9A=9A=9A=9A=9A at = org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientT= emplate.java:212) =9A=9A=9A=9A=9A at = org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTe= mplate.java:411) =9A=9A=9A=9A=9A at = com.ctigroup.brickyard.dataaccess.dao.CallDataDetailDAOImpl.selectCDDRpt(= CallDataDetailDAOImpl.java:43) =9A=9A=9A=9A=9A at = com.ctigroup.brickyard.dataaccess.Tester.main(Tester.java:98) Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:=9A=9A=20 --- The error occurred in = com/ctigroup/brickyard/dataaccess/sqlmappings/TEST_GET_CDDRPT_SqlMap.xml.= =9A=20 --- The error occurred while applying a result map.=9A=20 --- Check the TEST_TESTPROC.CallDataDetailResult.=9A=20 --- Check the result mapping for the 'localInitiationTime' property.=9A=20 --- Cause: java.sql.SQLException: Session Time Zone not set! =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate= (GeneralStatement.java:91) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecuto= rDelegate.java:505) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.= java:90) =9A=9A=9A=9A=9A at = org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInSqlMapClient(S= qlMapClientTemplate.java:413) =9A=9A=9A=9A=9A at = org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientT= emplate.java:209) =9A=9A=9A=9A=9A ... 3 more Caused by: java.sql.SQLException: Session Time Zone not set! =9A=9A=9A=9A=9A at = oracle.jdbc.driver.TimestampltzAccessor.getTimestamp(TimestampltzAccessor= .java:271) =9A=9A=9A=9A=9A at = oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.j= ava:796) =9A=9A=9A=9A=9A at = oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:1661= ) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.type.DateTypeHandler.getResult(DateTypeHandler.j= ava:38) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getPrimitiveResult= MappingValue(BasicResultMap.java:611) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(BasicRe= sultMap.java:344) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.execution.SqlExecutor.handleOutputParameterResul= ts(SqlExecutor.java:444) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(S= qlExecutor.java:406) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdateProcedure(Sql= Executor.java:233) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteU= pdate(ProcedureStatement.java:30) =9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate= (GeneralStatement.java:78) =9A=9A=9A=9A=9A ... 7 more =20 =20 =20 ________________________________ From: Andrey Rogov=20 Sent: Tuesday, March 18, 2008 7:45 PM To: Jesse Reimann Cc: user-java@ibatis.apache.org Subject: Re: Retrieving Oracle column with data type TIMESTAMP WITH = LOCAL TIME ZONE =20 Jess, there is an offer to attack a problem under a correct angle :), 1. I have created two fields=20 - TIMESTAMP(6) WITH TIME ZONE=20 - TIMESTAMP(6) WITH LOCAL TIME ZONE 2. Has changed xml file. Instead of sql has written stored procedure =20 =20 { call projects.tz_set_and_show( ? ) } =20 3. Stored procedure to set and show time zone . CREATE OR REPLACE PROCEDURE tz_set_and_show (tz_in IN VARCHAR2 :=3D = null) IS BEGIN IF tz_in IS NOT NULL THEN EXECUTE IMMEDIATE 'alter session set time_zone =3D ''' = || tz_in || '''' ; END IF; -- DBMS_OUTPUT.put_line ( 'SESSIONTIMEZONE =3D '|| = SESSIONTIMEZONE ) ; -- DBMS_OUTPUT.put_line ( 'CURRENT_TIMESTAMP =3D '|| = CURRENT_TIMESTAMP ) ; -- DBMS_OUTPUT.put_line ( 'LOCALTIMESTAMP =3D ' || LOCALTIMESTAMP = ) ; -- DBMS_OUTPUT.put_line ( 'SYS_EXTRACT_UTC (LOCALTIMESTAMP) =3D = '|| sqlexpr('SYS_EXTRACT_UTC (LOCALTIMESTAMP)') ); END ; 4. Model . - DAO set timezone 'America/Denver' - DAO getdata - DAO set timezone 'Turkey' - DAO getdata=20 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-=ED=E1=F2-08 06.19.40,000000 PM +03:00, *** = 18-=ED=E1=F2-08 08.19.40,000000 AM, 8 ***] =20 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-=ED=E1=F2-08 06.19.40,000000 PM +03:00, *** = 18-=ED=E1=F2-08 05.19.40,000000 PM, 8 ***] Andrey . ------_=_NextPart_001_01C889D6.CE5D771B Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

Thanks for the help… = I’ve implemented it and it is really helpful with setting the session = timezone but I still end up with my original problem with the TIMESTAMP(6) WITH LOCAL TIME ZONE failing because of Session Time Zone not being set = assertion being thrown from the jdbc driver when I try to retrieve data. Since you = seem to have this working could you post the definition of your resultmap = where the TIMESTAMP(6) WITH LOCAL TIME ZONE is being assigned? Also which Oracle jdbc driver are you using as = well?

 

I get this error = stack:

 

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized = SQLException for SQL []; SQL state [null]; error code [0];=9A=9A =

--- The error occurred in = com/ctigroup/brickyard/dataaccess/sqlmappings/TEST_GET_CDDRPT_SqlMap.xml.= =9A

--- The error occurred while applying a result map.=9A =

--- Check the TEST_TESTPROC.CallDataDetailResult.=9A =

--- Check the result mapping for the 'localInitiationTime' property.=9A

--- Cause: java.sql.SQLException: Session Time Zone not set!; nested exception is = com.ibatis.common.jdbc.exception.NestedSQLException:=9A=9A

--- The error occurred in com/ctigroup/brickyard/dataaccess/sqlmappings/TEST_GET_CDDRPT_SqlMap.xml.= =9A

--- The error occurred while applying a result map.=9A =

--- Check the TEST_TESTPROC.CallDataDetailResult.=9A =

--- Check the result mapping for the 'localInitiationTime' property.=9A

--- Cause: java.sql.SQLException: Session Time Zone not set!

=9A=9A=9A=9A=9A at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate= (SQLStateSQLExceptionTranslator.java:124)

=9A=9A=9A=9A=9A at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.trans= late(SQLErrorCodeSQLExceptionTranslator.java:322)

=9A=9A=9A=9A=9A at = org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)

=9A=9A=9A=9A=9A at = org.springframework.orm.ibatis.SqlMapClientTemplate.update(= SqlMapClientTemplate.java:411)

=9A=9A=9A=9A=9A at com.ctigroup.brickyard.dataaccess.dao.CallDataDetailDAOImpl.selectCDDRpt(= CallDataDetailDAOImpl.java:43)

=9A=9A=9A=9A=9A at = com.ctigroup.brickyard.dataaccess.Tester.main(Tester.java:98)

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:=9A=9A

--- The error occurred in com/ctigroup/brickyard/dataaccess/sqlmappings/TEST_GET_CDDRPT_SqlMap.xml.= =9A

--- The error occurred while applying a result map.=9A =

--- Check the TEST_TESTPROC.CallDataDetailResult.=9A =

--- Check the result mapping for the 'localInitiationTime' property.=9A

--- Cause: java.sql.SQLException: Session Time Zone not set!

=9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate= (GeneralStatement.java:91)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)

=9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(<= font size=3D2 color=3Dnavy face=3D"Courier New">SqlMapSessionImpl.java:90)

=9A=9A=9A=9A=9A at org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInSqlMapClient(<= /span>SqlMapClientTemplate.java:413)

=9A=9A=9A=9A=9A at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)

=9A=9A=9A=9A=9A ... 3 more

Caused by: java.sql.SQLException: Session Time Zone not set!

=9A=9A=9A=9A=9A at = oracle.jdbc.driver.TimestampltzAccessor.getTimestamp(TimestampltzAccessor.java:271)

=9A=9A=9A=9A=9A at = oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:796)

=9A=9A=9A=9A=9A at = oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:1661)

=9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.type.DateTypeHandler.getResult(= DateTypeHandler.java:38)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getPrimitiveResult= MappingValue(BasicResultMap.java:611)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(= BasicResultMap.java:344)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleOutputParameterResul= ts(SqlExecutor.java:444)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(<= /span>SqlExecutor.java:406)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdateProcedure(SqlExecutor.java:233)

=9A=9A=9A=9A=9A at = com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteU= pdate(ProcedureStatement.java:30)

=9A=9A=9A=9A=9A at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate= (GeneralStatement.java:78)

=9A=9A=9A=9A=9A ... 7 more

 

 

 

From: = Andrey Rogov
Sent: Tuesday, March 18, = 2008 7:45 PM
To: Jesse Reimann
Cc: = user-java@ibatis.apache.org
Subject: Re: Retrieving = Oracle column with data type TIMESTAMP WITH LOCAL TIME = ZONE

 

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=3D"timeZoneParameters" class=3D"Map">
            <parameter property=3D"timezone"  jdbcType=3D"VARCHAR" javaType=3D"java.lang.String" mode=3D"INOUT" = />
     </parameterMap>
     <procedure id=3D"setTimeZone" parameterMap=3D"timeZoneParameter

s">  
             { 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 :=3D null)
    IS
    BEGIN

        IF tz_in IS NOT NULL
             = THEN EXECUTE IMMEDIATE 'alter session set time_zone =3D ''' || tz_in || '''' = ;
        END IF;
     --   DBMS_OUTPUT.put_line ( 'SESSIONTIMEZONE   =3D '|| SESSIONTIMEZONE ) ;
     --   DBMS_OUTPUT.put_line ( 'CURRENT_TIMESTAMP = =3D '|| CURRENT_TIMESTAMP ) ;
     --   DBMS_OUTPUT.put_line ( = 'LOCALTIMESTAMP =3D ' || LOCALTIMESTAMP ) ;
     --   DBMS_OUTPUT.put_line ( = 'SYS_EXTRACT_UTC (LOCALTIMESTAMP) =3D '|| 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-=ED=E1=F2-08 06.19.40,000000 PM +03:00, *** = 18-=ED=E1=F2-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-=ED=E1=F2-08 06.19.40,000000 PM +03:00, *** = 18-=ED=E1=F2-08 05.19.40,000000 PM, 8 ***]


Andrey .

------_=_NextPart_001_01C889D6.CE5D771B--