ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jesse Reimann" <jreim...@ctigroup.com>
Subject RE: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
Date Wed, 19 Mar 2008 15:35:04 GMT
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];   

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


--- The error occurred while applying a result map.  

--- Check the TEST_TESTPROC.CallDataDetailResult.  

--- Check the result mapping for the 'localInitiationTime' property.  

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


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


--- The error occurred while applying a result map.  

--- Check the TEST_TESTPROC.CallDataDetailResult.  

--- Check the result mapping for the 'localInitiationTime' property.  

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

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

      at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)

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

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

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

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

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   

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


--- The error occurred while applying a result map.  

--- Check the TEST_TESTPROC.CallDataDetailResult.  

--- Check the result mapping for the 'localInitiationTime' property.  

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

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

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

      at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)

      at org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInSqlMapClient(SqlMapClientTemplate.java:413)

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

      ... 3 more

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

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

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

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

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

      at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getPrimitiveResultMappingValue(BasicResultMap.java:611)

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

      at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleOutputParameterResults(SqlExecutor.java:444)

      at com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlExecutor.java:406)

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

      at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteUpdate(ProcedureStatement.java:30)

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

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


Mime
View raw message