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]
