ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Whipple <dwhip...@dtcc.com>
Subject Re: Output parameter problems in Sybase stored procedure
Date Fri, 02 Dec 2005 13:31:17 GMT
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/DTCC@DTCC                                             
                                                                        To 
             11/29/2005 03:50          user-java@ibatis.apache.org         
             PM                                                         cc 
                                                                           
                                                                   Subject 
             Please respond to         Output parameter problems in Sybase 
             user-java@ibatis.         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



Mime
View raw message