I am still not able to find anything for this. Could it be that there is
trouble with the Sybase drivers?
Thanks,
Dave
David
Whipple/[EMAIL PROTECTED]
To
11/29/2005 03:50 [email protected]
PM cc
Subject
Please respond to Output parameter problems in Sybase
[EMAIL PROTECTED] stored procedure
apache.org
I am having trouble getting an output parameter to work in calling a Sybase
stored procedure. The stored procedure is definied as:
CREATE proc pWEBComm1UnbatchedCount
@iDebugMode int,
@szParticipant char( 4),
@szAgent char( 4),
@nOperator numeric(10),
@szGuiRef char( 3) = 'CBA',
@iTranTotal int output
as
declare @iRetStat int
select @iRetStat = 0
. . .
select @iTranTotal = ISNULL( COUNT(*), 0 )
from comm_bill
where agent = @szAgent
and batch_nbr = 0
and trn_st = 'NEW'
and mkt = @mkt
. . .
return @iRetStat
go
my parameter map is defined as:
<parameterMap id="productUnbatchedCountParameters"
class="com.dtcc.pcwd.common.send.business.UnbatchedCountParameter">
<parameter property="rc" jdbcType="NUMERIC" javaType="int"
mode="OUT"/>
<parameter property="participant"/>
<parameter property="agent"/>
<parameter property="guiRef"/>
<parameter property="transactionTotal" jdbcType="NUMERIC"
javaType="int" mode="INOUT"/>
</parameterMap>
<procedure id="getUnbatchedCount"
parameterMap="productUnbatchedCountParameters">
{? = call pWEBComm1UnbatchedCount 0, ?, ?, 0, ?, ? }
</procedure>
java code:
Reader reader = Resources.getResourceAsReader(IBATIS_XML);
sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
UnbatchedCountParameter ubc = new UnbatchedCountParameter();
ubc.setAgent("MKT1");
ubc.setParticipant("0161");
ubc.setGuiRef("CBA");
sqlMapClient.insert("getUnbatchedCount", ubc);
System.out.println("ubc: " + ubc.getTransactionTotal());
The error I get is:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/dtcc/pcwd/common/send/dao/ibatis/Send.sybase.sp.xml.
--- The error occurred while applying a parameter map.
--- Check the send.productUnbatchedCountParameters.
--- Check the output parameters (retrieval of output parameters failed).
--- Cause: java.sql.SQLException: JZ0SG: A CallableStatement did not return
as many output parameters as the application had registered for it.
Caused by: java.sql.SQLException: JZ0SG: A CallableStatement did not return
as many output parameters as the application had registered for it.
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:442)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:81)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:58)
at
com.dtcc.commissionbilling.dao.springibatis.TestSendDaoIbatis.testUnbatchedCount(TestSendDaoIbatis.java:27)
at java.lang.reflect.Method.invoke(Native Method)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)
Caused by:
java.sql.SQLException: JZ0SG: A CallableStatement did not return as many
output parameters as the application had registered for it.
at
com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:500)
at
com.sybase.jdbc2.jdbc.ParamManager.nextResult(ParamManager.java:626)
at
com.sybase.jdbc2.jdbc.ParamManager.getOutValueAt(ParamManager.java:419)
at
com.sybase.jdbc2.jdbc.SybCallableStatement.getInt(SybCallableStatement.java:271)
at java.lang.reflect.Method.invoke(Native Method)
at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:80)
at $Proxy1.getInt(Unknown Source)
at
com.ibatis.sqlmap.engine.type.IntegerTypeHandler.getResult(IntegerTypeHandler.java:55)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlExecutor.java:350)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdateProcedure(SqlExecutor.java:240)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteUpdate(ProcedureStatement.java:30)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:442)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:81)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:58)
at
com.dtcc.commissionbilling.dao.springibatis.TestSendDaoIbatis.testUnbatchedCount(TestSendDaoIbatis.java:27)
at java.lang.reflect.Method.invoke(Native Method)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)
I can not see any problems with this, and we have stored procedures doing
similar things (with a different database) just fine. The Sybase stuff
works fine for result sets as well.
I have searched as much as I could find about this and am at a loss.
Does anyone know what the issue might be? Could it be an issue with the
Sybase drivers?
Thanks,
Dave
