The NPE is clearly coming from the MySQL driver - not an iBATIS problem I
think. It would be interesting to try calling the SP from JDBC to verify.
I'll have to think about the hanging issue.
Jeff Butler
On 11/22/06, Peter Andrews <[EMAIL PROTECTED]> wrote:
Thanks for pointing that out.
However, I retried with the curly braces and both errors (procedure: null
pointer exception; statement: hanging without commit) still occur.
To be thorough, I updated my subversion copy of iBATIS to current
(revision 478164) but still get errors...
Jeff Butler wrote:
Part of the problem is improper syntax for the procedure call. It should
look like this:
<procedure id="deleteDatasetByResearcherIdAndIdProcedure"
resultClass="Integer" parameterClass="Map">
{CALL deleteDatasetByResearcherIdAndId(
#pResearcherId:NUMERIC#,
#pDatasetId:NUMERIC#)}
</procedure>
Note the curly braces - this is the JDBC standard excape sequence for
procdure calls. That's probably why the MySql driver is choking - although
it would be better if they had a more intelligent error message!
Jeff Butler
On 11/21/06, Peter Andrews <[EMAIL PROTECTED]> wrote:
>
> I know it is poor form to report two things at once but the replication
> instructions are very similar and bugs may be similar.
>
> I am using iBATIS from subversion updated Friday am.
>
> I am interacting with my db solely through stored procedures.
>
> Earlier, I had problems with iBATIS not realizing that my calls had
> altered the database and therefore not committing. To work around this,
> I set my transactionManager to commitRequired="true"
> <transactionManager type="JDBC" commitRequired="true">
>
> Here is the signature of the Stored Proc. I am using MySql 5.1.12-beta.
> CREATE PROCEDURE deleteDatasetByResearcherIdAndId(
> pResearcherId INT(11),
> pDatasetId INT(11)
> )
> DETERMINISTIC
> MODIFIES SQL DATA
> BEGIN
> blah blah .....
> END
>
>
> However, I have found a case where iBATIS is not sending the commit and
> the call to the db is basically hanging waiting for the commit. To test
> this I have tested my call with three different iBATIS xml elements. I
> had trouble with two of the cases and have full details below.
> 1) using statement, I get an indefinite hang because no commit is
> ever sent by iBATIS
> 2) using procedure I get a NullPointerException
> 3) using a delete element, I got it to work
>
>
> #1 using statement
>
> <statement id="deleteDatasetByResearcherIdAndIdStatement"
> resultClass="Integer" parameterClass="Map">
> CALL deleteDatasetByResearcherIdAndId(
> #pResearcherId:NUMERIC#,
> #pDatasetId:NUMERIC#
> )
> </statement>
>
> public static Integer deleteDatasetByResearcherIdAndIdStatement(int
> pResearcherId, int pDatasetId) {
> Map<String, Object> map = new HashMap<String, Object>();
> map.put("pResearcherId", pResearcherId);
> map.put("pDatasetId", pDatasetId);
> return
> cast(sqlMapper.queryForObject
> ("deleteDatasetByResearcherIdAndIdStatement",
> map));
> }
>
> The MySQL log shows this:
> 240 Query SET autocommit=1
> 240 Query SET autocommit=0
> 240 Query CALL
> deleteDatasetByResearcherIdAndId( 1, 1 )
> NOTE: NO COMMIT
>
> #2 using Procedure
>
> <procedure id="deleteDatasetByResearcherIdAndIdProcedure"
> resultClass="Integer" parameterClass="Map">
> CALL deleteDatasetByResearcherIdAndId(
> #pResearcherId:NUMERIC#,
> #pDatasetId:NUMERIC#
> )
> </procedure>
>
> public static Integer deleteDatasetByResearcherIdAndIdProcedure(int
> pResearcherId, int pDatasetId) {
> Map<String, Object> map = new HashMap<String, Object>();
> map.put("pResearcherId", pResearcherId);
> map.put("pDatasetId", pDatasetId);
> return
> cast(sqlMapper.queryForObject
> ("deleteDatasetByResearcherIdAndIdProcedure",
> map));
> }
>
> 2006-11-21 18:33:48,381 DEBUG: queryForObject
> deleteDatasetByResearcherIdAndIdProcedure passing: {pDatasetId=1,
> pResearcherId=1}
> 2006-11-21 18:33:48,384 DEBUG: {conn-100003} Connection
> 2006-11-21 18:33:48,392 DEBUG: {conn-100003} Preparing Call: CALL
> deleteDatasetByResearcherIdAndId( ?, ? )
> 2006-11-21 18:33:48,428 ERROR: Error calling Connection.prepareCall:
> java.lang.NullPointerException
> at
> com.mysql.jdbc.StringUtils.indexOfIgnoreCaseRespectQuotes(
> StringUtils.java:959)
> com.ibatis.common.jdbc.exception.NestedSQLException:
> at
> com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(
> DatabaseMetaData.java:1296)
> --- The error occurred in org/epistasis/db/geneDataset/GeneDataset.xml.
> at
> com.mysql.jdbc.DatabaseMetaData.getProcedureColumns (
> DatabaseMetaData.java:3670)
> --- The error occurred while executing query procedure.
> at
> com.mysql.jdbc.CallableStatement.determineParameterTypes(
> CallableStatement.java:702)
> --- Check the CALL deleteDatasetByResearcherIdAndId( ?,
> ? ).
> --- Check the SQL Statement (preparation failed).
> at
> com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
> --- Cause: java.lang.NullPointerException
> at
> com.mysql.jdbc.Connection.parseCallableStatement (Connection.java:4422)
> Caused by: java.lang.NullPointerException
> at com.mysql.jdbc.Connection.prepareCall(Connection.java:4496)
> at com.mysql.jdbc.Connection.prepareCall(Connection.java:4470)
> at
>
>
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:188)
> at
> org.apache.commons.dbcp.DelegatingConnection.prepareCall(
> DelegatingConnection.java:212)
> at
>
>
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareCall
> (PoolingDataSource.java:268)
> at
>
>
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject
> (GeneralStatement.java :104)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(
> SqlMapExecutorDelegate.java:566)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke (
> NativeMethodAccessorImpl.java:39)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(
> SqlMapExecutorDelegate.java:541)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(
> DelegatingMethodAccessorImpl.java :25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(
> SqlMapSessionImpl.java:106)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject (
> SqlMapClientImpl.java:83)
> at
> com.ibatis.common.jdbc.logging.ConnectionLogProxy.invoke(
> ConnectionLogProxy.java:60)
> at $Proxy0.prepareCall(Unknown Source)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareCall (
> SqlExecutor.java:500)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(
> SqlExecutor.java:260)
> at
>
> com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(
> ProcedureStatement.java:34)
> at
> org.epistasis.db.SqlMapClientWrapper.queryForObject(
> SqlMapClientWrapper.java:46)
> at
>
>
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(
> GeneralStatement.java:173)
> at
>
>
org.epistasis.db.geneDataset.GeneDatasetDbAccess.deleteDatasetByResearcherIdAndIdProcedure
> (GeneDatasetDbAccess.java:84)
> at
>
>
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(
> GeneralStatement.java:104)
> at
> org.epistasis.db.geneDataset.GeneDatasetDbAccess.main(
> GeneDatasetDbAccess.java:99)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(
> SqlMapExecutorDelegate.java :566)
>
> #3 using Delete worked!
> <delete id="deleteDatasetByResearcherIdAndId" parameterClass="Map">
> CALL deleteDatasetByResearcherIdAndId(
> #pResearcherId:NUMERIC#,
> #pDatasetId:NUMERIC#
> )
> </delete>
>
> public static int deleteDatasetByResearcherIdAndId(int pResearcherId,
> int pDatasetId) {
> Map<String, Object> map = new HashMap<String, Object>();
> map.put("pResearcherId", pResearcherId);
> map.put("pDatasetId", pDatasetId);
> return sqlMapper.delete("deleteDatasetByResearcherIdAndId", map);
> }
>
> --
> Peter Andrews
> Software Engineer
> Dartmouth Medical School
> Computational Genetics
> Rubin 707
> (603) 653-3598
> [EMAIL PROTECTED]
>
>
--
Peter Andrews
Software Engineer
Dartmouth Medical School
Computational Genetics
Rubin 707
(603) [EMAIL PROTECTED]
