Yes, and it does work as expected.
Dave
Larry Meadors
<[EMAIL PROTECTED]
org> To
Sent by: [email protected]
[EMAIL PROTECTED] cc
il.com
Subject
Re: Output parameter problems in
12/02/2005 08:39 Sybase stored procedure
AM
Please respond to
[EMAIL PROTECTED]
apache.org
That is a possibility.
Have you tried this with just JDBC?
Larry
On 12/2/05, David Whipple <[EMAIL PROTECTED]> wrote:
> 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
>
>
>
