You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jesse Reimann <jr...@ctigroup.com> on 2008/03/17 21:53:28 UTC
Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
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
RE: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
Posted by Jesse Reimann <jr...@ctigroup.com>.
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 .
Re: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
Posted by Andrey Rogov <an...@gmail.com>.
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 <jr...@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 <jr...@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
>
>
>
Re: about bean with no GET method performance
Posted by j-lists <ja...@gmail.com>.
I think the real news here is that alibaba.com is using ibatis! I did
a quick search and found this:
http://code.google.com/p/ibatis-sharding/
It looks like it is a project from the devs at alibaba.
-J
Re: about bean with no GET method performance
Posted by Koka Kiknadze <22...@gmail.com>.
I've done some performance measurements too over an year ago and seems I've
pinpointed (with the help of Clinton) where one can loose dozen of
percents with Oracle driver, take a look at the thread
http://mail-archives.apache.org/mod_mbox/ibatis-user-java/200701.mbox/%3c46d2280d0701280636q385cc028l3cb29d937cbf8785@mail.gmail.com%3e
GL
Re: about bean with no GET method performance
Posted by Clinton Begin <cl...@gmail.com>.
>> Above calling take about 1700ms -1800ms. JDBC query take about 200ms.
Does your JDBC example include creating all of the classes and setting
all of the properties? Calling resultSet.getXxxxx() and setting
properties 840,000 times in 1.8 seconds actually sounds pretty good.
I hate to discourage you from using iBATIS, but honestly if you're
worried about a 500ms difference in performance of a query of nearly
1,000,000 values, you might be better off without iBATIS. :-/
Clinton
On Tue, Mar 18, 2008 at 10:32 AM, Leon Liu <lj...@alibaba-inc.com> wrote:
>
> Hi ,
> These days I test ibatis performance with large result set and large
> bean. I do following step,
> 1. A bean have 84 properties and a tabble have 84 columns.
> 2. and I set enhencmentEnable=true,
> 3. I query 10000 result from oracle database. Oracle database with JDBC
> driver set defaultRowFetch=50.
> Above calling take about 1700ms -1800ms. JDBC query take about 200ms.
> But when I check my bean code, I found there is a property with no get
> method, so I add the property's Getter Method.
> Then I get a surprised, The query formance got a great improvement. It
> is take about 1200ms-1300ms.
> I debug ibatis code, I find follwing code may cause big performance:
>
> com.ibatis.sqlmap.engine.accessplan.AccessPlanFactory Line 60
> if (bytecodeEnhancementEnabled) {
> try {
> plan = new EnhancedPropertyAccessPlan(clazz, propertyNames);
> } catch (Throwable t) {
> try {
> plan = new PropertyAccessPlan(clazz, propertyNames);
> } catch (Throwable t2) {
> plan = new ComplexAccessPlan(clazz, propertyNames);
> }
> }
> }
> com.ibatis.common.beans.ClassInfo Line256
> public Method getGetter(String propertyName) {
> Method method = (Method) getMethods.get(propertyName);
> if (method == null) {
> throw new ProbeException("There is no READABLE property named '" +
> propertyName + "' in class '" + className + "'");
> }
> return method;
> }
>
> class EnhancedPropertyAccessPlan/PropertyAccessPlan call
> com.ibatis.common.beans.ClassInfo.getGetter(String) that cause an exception
> when a bean have no Getter method, AccessPlan object choose
> ComplexAccessPlan.
>
> I think ComplexAccessPlan cause the performance problem.
>
> IBtatis automatic decide a simple bean that property have no Getter method
> to be Complex type. IBatis does not prompt any warning enhancementEnable
> option will be skipped. I think these ibatis exception handling is not
> smooth. and If user's bean loose some Getter method, a common user does
> not know why ibatis performance become bad.
>
> BR
> Leon Liu
>
>
>
>
>
>
>
RE: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
Posted by Jesse Reimann <jr...@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 <jr...@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
Re: Retrieving Oracle column with data type TIMESTAMP WITH LOCAL TIME ZONE
Posted by Andrey Rogov <an...@gmail.com>.
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 <jr...@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
>