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 Cyril <cy...@yahoo.fr> on 2006/05/16 19:38:47 UTC
Re : Problem with iBatis SQLMap and stored procedure
Hi,
I tried your suggestion and it works !
I was very surprised, because I downloaded DBCP sources and managed to continue my debugging. It suggested that the CallbackStatement.execute() didn't return any result; but since it is a DelegatingCallableStatement, I suspected there may be some kind of lazy initialisation. My conclusion was there is some kind of bug in Sybase JDBC driver, or special parameters which prevent us to use iBatis with Tomcat and Sybase.
I am ecstatic you found the solution to my problem so easily. Have you met this problem before? Do you know any problem between DBCP and Sybase or iBatis?
Thank you very much, you saved my (professional) life (I did push iBatis on this project and another project very strongly) !
Cyril
PS: my post can also be viewed as an exemple of how to use iBatis and stored procedures. The documentation of iBatis is not very wordy on this.
----- Message d'origine ----
De : Beemsterboer Software <ha...@beemsterboer-software.nl>
À : user-java@ibatis.apache.org
Cc : cyril_jade@yahoo.fr
Envoyé le : Mardi, 16 Mai 2006, 6h49mn 30s
Objet : Re: Problem with iBatis SQLMap and stored procedure
Hi Cyril,
Can you try to configure the Tomcat Datasource without DBCP?
For example, here is a configuration for MySQL:
<Resource name="xxxx" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="xxxx">
<parameter>
<name>username</name>
<value></value>
</parameter>
<parameter>
<name>password</name>
<value></value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql:///test?jdbcCompliantTruncation=false&zeroDateTimeBehavior=convertToNull</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>8</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>4</value>
</parameter>
</ResourceParams>
Cyril wrote:
> Hi everybody,
>
> Please, can you help me?
>
> I'm migrating a web application from proprietary JDBC framework to iBatis. Every data retrieving from the database come from stored procedure.
> I managed to refactor a small unit, after some difficulties finding the correct syntax for stored procedure returning an object, and a problem with Websphere creating a new transaction never commited. Everything was alright, and I set up a small training for my team to present iBatis with a live exemple.
>
> Then, we decided to use Tomcat instead of Websphere, in dev environment, and we noticed my code didn't work anymore.
> It works on Webphere, and not in Tomcat. Another topic in the mailing list is very similar:
>
> http://www.mail-archive.com/user-java@ibatis.apache.org/msg00942.html
>
> but since the procedure returns several columns (we map an object from them), I can't use the answer here: http://www.mail-archive.com/user-java@ibatis.apache.org/msg00946.html
>
> Here are some more informations:
>
> I debugged from the iBatis source, and the deeper I managed to get was :
> File: SqlExecutor.java, method: handleResults(RequestScope, ResultSet, int, int, RowHandlerCallback)
> Line: if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY)
> I suspect rs.getType throws a nullpointerexception (but rs is a DelegationResultSet, and is not null)
>
> The stack trace is:
>
> 2006-05-16 15:02:48,413 ERROR[http-8080-Processor5] xxxxx.model.client.dao.impl.ClientDescriptionIbatisDAO(41) - SQLException
> --- The error occurred in xxxxx/ibatis/sqlmap/ClientDescription.xml.
> --- The error occurred while applying a parameter map.
> --- Check the ClientDescription.clientDescriptionParams.
> --- Check the results (failed to retrieve results).
> --- Cause: java.lang.NullPointerException
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in xxxxx/ibatis/sqlmap/ClientDescription.xml.
> --- The error occurred while applying a parameter map.
> --- Check the ClientDescription.clientDescriptionParams.
> --- Check the results (failed to retrieve results).
> --- Cause: java.lang.NullPointerException
> Caused by: java.lang.NullPointerException
> at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
> at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
> at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
> at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
> at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
> at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
> ...........
> Caused by: java.lang.NullPointerException
> at org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132)
> at com.ibatis.sqlmap.engine.execution.SqlExecutor.closeResultSet(SqlExecutor.java:423)
> at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:305)
> at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
> at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
> ... 60 more
>
> Caused by:
> java.lang.NullPointerException
> at org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132)
> at com.ibatis.sqlmap.engine.execution.SqlExecutor.closeResultSet(SqlExecutor.java:423)
> at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:305)
> at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
>
>
> My sqlmap-config file is:
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMapConfig
> PUBLIC "-//iBATIS.com//DTD SQL MAP Config 2.0//EN"
> "http://www.ibatis.com/dtd/sql-map-config-2.dtd";>
>
> <sqlMapConfig>
>
> <settings
> cacheModelsEnabled="true"
> enhancementEnabled="false"
> lazyLoadingEnabled="true"
> maxRequests="256"
> maxSessions="32"
> maxTransactions="16"
> useStatementNamespaces="false"
> />
>
> <!-- Normal version, but we can't use it because of Websphere
> <transactionManager type="JDBC">
> <dataSource type="JNDI">
> <property name="DataSource" value="java:comp/env/jdbc/xxxxx"/>
> </dataSource>
> </transactionManager>
> -->
>
> <!-- Special version, because Websphere create a transaction itself, and never commit or rollback it -->
> <transactionManager commitRequired="true" type="EXTERNAL">
> <property name="DefaultAutoCommit" value="false"/>
> <property name="SetAutoCommitAllowed" value="false"/>
> <dataSource type="JNDI">
> <property name="DataSource" value="java:comp/env/jdbc/xxxxx"/>
> </dataSource>
> </transactionManager>
>
> <sqlMap resource="xxxxx/ibatis/sqlmap/ClientDescription.xml"/>
>
> </sqlMapConfig>
>
>
> NOTE : I TRY WITH THE "NORMAL SECTION" AND TOMCAT TOO. I tried both, and had the same results.
>
> My sqlmap file:
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
> "http://www.ibatis.com/dtd/sql-map-2.dtd";>
>
> <sqlMap namespace="ClientDescription">
>
> <typeAlias alias="clientDescription" type="xxxxx.ibatis.pojo.IbatisClientDescription"/>
>
> <parameterMap id="clientDescriptionParams" class="java.util.Map">
> <parameter property="contextId"/>
> <parameter property="ricosId"/>
> </parameterMap>
>
> <resultMap id="clientDescriptionResult" class="clientDescription">
> <result property="sector" column="SECTOR"/>
> <result property="sectorLab" column="SECTORLABEL"/>
> <result property="category" column="CATEGORY"/>
> <result property="categoryLab" column="CATEGORYLABEL"/>
> <result property="economicSector" column="ECONOMICSECTOR"/>
> <result property="economicSectorLab" column="BUSAREALABEL"/>
> <result property="businessArea" column="BUSAREA"/>
> <result property="businessAreaLab" column="BUSAREALABEL"/>
> <result property="country" column="COUNTRY"/>
> <result property="countryLab" column="COUNTRYLABEL"/>
> <result property="residence" column="RESIDENCE"/>
> <result property="residenceLab" column="RESIDENCELABEL"/>
> <result property="region" column="REGION"/>
> <result property="regionLab" column="REGIONLABEL"/>
> <result property="rating" column="RATING"/>
> <result property="ratingLab" column="RATINGLABEL"/>
> <result property="freezeDateAsSybase" column="FREEZEDATE"/>
> <result property="comment" column="COMMENT"/>
> </resultMap>
>
>
> <procedure id="getClientDescription" parameterMap="clientDescriptionParams" resultMap="clientDescriptionResult">
> <![CDATA[
> {call CCCCustomersDetail(?, ?)}
> ]]>
> </procedure>
>
> </sqlMap>
>
> The problem may come from org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132) (which seems to be a Tomcat class), anybody found some way to make calling stored procedure (Database is SYBASE) work?
>
>
> I hope you could help me,
> sorry for the bad english,
>
> Cyril
>
>
>
>
Re: Re : Problem with iBatis SQLMap and stored procedure
Posted by Beemsterboer Software <ha...@beemsterboer-software.nl>.
Hi,
As you've already pointed out in the stacktrace, the problem "clearly"
comes from DBCP:
Caused by:
java.lang.NullPointerException
at org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132)
I haven't met this problem before, but I already had Tomcat working with
iBatis using the Datasource configuration.
By the way, you don't need the <![CDATA[ part in your stored procedure
mapping.
Good luck with your team training!
Greetings,
Hans Beemsterboer.
Cyril wrote:
> Hi,
>
> I tried your suggestion and it works !
>
> I was very surprised, because I downloaded DBCP sources and managed to continue my debugging. It suggested that the CallbackStatement.execute() didn't return any result; but since it is a DelegatingCallableStatement, I suspected there may be some kind of lazy initialisation. My conclusion was there is some kind of bug in Sybase JDBC driver, or special parameters which prevent us to use iBatis with Tomcat and Sybase.
>
> I am ecstatic you found the solution to my problem so easily. Have you met this problem before? Do you know any problem between DBCP and Sybase or iBatis?
>
> Thank you very much, you saved my (professional) life (I did push iBatis on this project and another project very strongly) !
>
> Cyril
>
> PS: my post can also be viewed as an exemple of how to use iBatis and stored procedures. The documentation of iBatis is not very wordy on this.
>
>
> ----- Message d'origine ----
> De : Beemsterboer Software <ha...@beemsterboer-software.nl>
> À : user-java@ibatis.apache.org
> Cc : cyril_jade@yahoo.fr
> Envoyé le : Mardi, 16 Mai 2006, 6h49mn 30s
> Objet : Re: Problem with iBatis SQLMap and stored procedure
>
> Hi Cyril,
>
> Can you try to configure the Tomcat Datasource without DBCP?
>
> For example, here is a configuration for MySQL:
>
> <Resource name="xxxx" auth="Container"
> type="javax.sql.DataSource"/>
> <ResourceParams name="xxxx">
> <parameter>
> <name>username</name>
> <value></value>
> </parameter>
> <parameter>
> <name>password</name>
> <value></value>
> </parameter>
> <parameter>
> <name>driverClassName</name>
> <value>com.mysql.jdbc.Driver</value>
> </parameter>
> <parameter>
> <name>url</name>
>
> <value>jdbc:mysql:///test?jdbcCompliantTruncation=false&zeroDateTimeBehavior=convertToNull</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>8</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>4</value>
> </parameter>
> </ResourceParams>
>
> Cyril wrote:
>
>> Hi everybody,
>>
>> Please, can you help me?
>>
>> I'm migrating a web application from proprietary JDBC framework to iBatis. Every data retrieving from the database come from stored procedure.
>> I managed to refactor a small unit, after some difficulties finding the correct syntax for stored procedure returning an object, and a problem with Websphere creating a new transaction never commited. Everything was alright, and I set up a small training for my team to present iBatis with a live exemple.
>>
>> Then, we decided to use Tomcat instead of Websphere, in dev environment, and we noticed my code didn't work anymore.
>> It works on Webphere, and not in Tomcat. Another topic in the mailing list is very similar:
>>
>> http://www.mail-archive.com/user-java@ibatis.apache.org/msg00942.html
>>
>> but since the procedure returns several columns (we map an object from them), I can't use the answer here: http://www.mail-archive.com/user-java@ibatis.apache.org/msg00946.html
>>
>> Here are some more informations:
>>
>> I debugged from the iBatis source, and the deeper I managed to get was :
>> File: SqlExecutor.java, method: handleResults(RequestScope, ResultSet, int, int, RowHandlerCallback)
>> Line: if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY)
>> I suspect rs.getType throws a nullpointerexception (but rs is a DelegationResultSet, and is not null)
>>
>> The stack trace is:
>>
>> 2006-05-16 15:02:48,413 ERROR[http-8080-Processor5] xxxxx.model.client.dao.impl.ClientDescriptionIbatisDAO(41) - SQLException
>> --- The error occurred in xxxxx/ibatis/sqlmap/ClientDescription.xml.
>> --- The error occurred while applying a parameter map.
>> --- Check the ClientDescription.clientDescriptionParams.
>> --- Check the results (failed to retrieve results).
>> --- Cause: java.lang.NullPointerException
>> com.ibatis.common.jdbc.exception.NestedSQLException:
>> --- The error occurred in xxxxx/ibatis/sqlmap/ClientDescription.xml.
>> --- The error occurred while applying a parameter map.
>> --- Check the ClientDescription.clientDescriptionParams.
>> --- Check the results (failed to retrieve results).
>> --- Cause: java.lang.NullPointerException
>> Caused by: java.lang.NullPointerException
>> at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
>> at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
>> at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
>> at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
>> at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
>> at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
>> ...........
>> Caused by: java.lang.NullPointerException
>> at org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132)
>> at com.ibatis.sqlmap.engine.execution.SqlExecutor.closeResultSet(SqlExecutor.java:423)
>> at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:305)
>> at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
>> at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
>> ... 60 more
>>
>> Caused by:
>> java.lang.NullPointerException
>> at org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132)
>> at com.ibatis.sqlmap.engine.execution.SqlExecutor.closeResultSet(SqlExecutor.java:423)
>> at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:305)
>> at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
>>
>>
>> My sqlmap-config file is:
>>
>> <?xml version="1.0" encoding="UTF-8" ?>
>> <!DOCTYPE sqlMapConfig
>> PUBLIC "-//iBATIS.com//DTD SQL MAP Config 2.0//EN"
>> "http://www.ibatis.com/dtd/sql-map-config-2.dtd";>
>>
>> <sqlMapConfig>
>>
>> <settings
>> cacheModelsEnabled="true"
>> enhancementEnabled="false"
>> lazyLoadingEnabled="true"
>> maxRequests="256"
>> maxSessions="32"
>> maxTransactions="16"
>> useStatementNamespaces="false"
>> />
>>
>> <!-- Normal version, but we can't use it because of Websphere
>> <transactionManager type="JDBC">
>> <dataSource type="JNDI">
>> <property name="DataSource" value="java:comp/env/jdbc/xxxxx"/>
>> </dataSource>
>> </transactionManager>
>> -->
>>
>> <!-- Special version, because Websphere create a transaction itself, and never commit or rollback it -->
>> <transactionManager commitRequired="true" type="EXTERNAL">
>> <property name="DefaultAutoCommit" value="false"/>
>> <property name="SetAutoCommitAllowed" value="false"/>
>> <dataSource type="JNDI">
>> <property name="DataSource" value="java:comp/env/jdbc/xxxxx"/>
>> </dataSource>
>> </transactionManager>
>>
>> <sqlMap resource="xxxxx/ibatis/sqlmap/ClientDescription.xml"/>
>>
>> </sqlMapConfig>
>>
>>
>> NOTE : I TRY WITH THE "NORMAL SECTION" AND TOMCAT TOO. I tried both, and had the same results.
>>
>> My sqlmap file:
>>
>> <?xml version="1.0" encoding="UTF-8"?>
>> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
>> "http://www.ibatis.com/dtd/sql-map-2.dtd";>
>>
>> <sqlMap namespace="ClientDescription">
>>
>> <typeAlias alias="clientDescription" type="xxxxx.ibatis.pojo.IbatisClientDescription"/>
>>
>> <parameterMap id="clientDescriptionParams" class="java.util.Map">
>> <parameter property="contextId"/>
>> <parameter property="ricosId"/>
>> </parameterMap>
>>
>> <resultMap id="clientDescriptionResult" class="clientDescription">
>> <result property="sector" column="SECTOR"/>
>> <result property="sectorLab" column="SECTORLABEL"/>
>> <result property="category" column="CATEGORY"/>
>> <result property="categoryLab" column="CATEGORYLABEL"/>
>> <result property="economicSector" column="ECONOMICSECTOR"/>
>> <result property="economicSectorLab" column="BUSAREALABEL"/>
>> <result property="businessArea" column="BUSAREA"/>
>> <result property="businessAreaLab" column="BUSAREALABEL"/>
>> <result property="country" column="COUNTRY"/>
>> <result property="countryLab" column="COUNTRYLABEL"/>
>> <result property="residence" column="RESIDENCE"/>
>> <result property="residenceLab" column="RESIDENCELABEL"/>
>> <result property="region" column="REGION"/>
>> <result property="regionLab" column="REGIONLABEL"/>
>> <result property="rating" column="RATING"/>
>> <result property="ratingLab" column="RATINGLABEL"/>
>> <result property="freezeDateAsSybase" column="FREEZEDATE"/>
>> <result property="comment" column="COMMENT"/>
>> </resultMap>
>>
>>
>> <procedure id="getClientDescription" parameterMap="clientDescriptionParams" resultMap="clientDescriptionResult">
>> <![CDATA[
>> {call CCCCustomersDetail(?, ?)}
>> ]]>
>> </procedure>
>>
>> </sqlMap>
>>
>> The problem may come from org.apache.commons.dbcp.DelegatingResultSet.close(DelegatingResultSet.java:132) (which seems to be a Tomcat class), anybody found some way to make calling stored procedure (Database is SYBASE) work?
>>
>>
>> I hope you could help me,
>> sorry for the bad english,
>>
>> Cyril
>>
>>
>>