I think the null pointer exception is related to iBATIS because if I
wrap the identical call in a delete element rather than a procedure
element, the procedure is called and works just fine.
This works:
<delete id="deleteDatasetByResearcherIdAndId"
parameterClass="Map">
{CALL deleteDatasetByResearcherIdAndId(#pResearcherId:NUMERIC#,
#pDatasetId:NUMERIC#)}
</delete>
This throws null pointer exception:
<procedure id="deleteDatasetByResearcherIdAndIdProcedure"
resultClass="Integer" parameterClass="Map">
{CALL deleteDatasetByResearcherIdAndId(#pResearcherId:NUMERIC#,
#pDatasetId:NUMERIC# )}
</procedure>
This hangs:
<statement id="deleteDatasetByResearcherIdAndIdStatement"
resultClass="Integer" parameterClass="Map">
{CALL deleteDatasetByResearcherIdAndId(#pResearcherId:NUMERIC#,
#pDatasetId:NUMERIC#)}
</statement>
Jeff Butler wrote:
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) 653-3598
[EMAIL PROTECTED]
--
Peter Andrews
Software Engineer
Dartmouth Medical School
Computational Genetics
Rubin 707
(603) 653-3598
[EMAIL PROTECTED]
|
