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 .
