StoredProcedure Limitation?

[savan]

There seems to be a limitation to the StoredProcedure abstract class. Hopefully someone can verify this for me.

When using a DB2 stored proc i was unable to get the ResultSet from what would be the CallableStatement that was executed. To generalize, the output params are basically metadata about the statement's execution and there's a ResultSet that's in the CallableStatement that I want. Spring's .execute(Map) method seems only to return the defined output parameters.

The stored proc as defined below had five output params but the CallableStatement also has a ResultSet that's accessible by calling

CallableStatement.getResultSet():ResultSet

Has anyone else run into this problem? Is it a real limitation? If it's not how do i get the ResultSet from the underlying CallableStatement?

Thanks,
Savan

Below is the code i'm using to define my Spring StoredProcedure:

/*
 * Created on Jul 18, 2005
 *
 * Copyright 2005 myCo , Inc. All rights reserved.
 * myCo  PROPRIETARY/CONFIDENTIAL.
 */
package com.myCo.xx.yy.util.dao;

import java.sql.Types;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

import com.myCo.xx.yy.util.service.ApplicationContext;

/**
 * @author Savan Thongvanh
 */
public class ClaimNumberByClaimNumberStoredProcedure extends StoredProcedure {
    private ApplicationContext ctx = ApplicationContext.getInstance();
    private DataSource ds;
    private String procName;

    /**
     * 
     */
    public ClaimNumberByClaimNumberStoredProcedure() {
        super();
        ds = (DataSource) ctx.getBean("crossRefDatasource");
        procName = "mySchema.myProcName";
        initProc();
    }

    /**
     * @param arg0
     * @param arg1
     */
    public ClaimNumberByClaimNumberStoredProcedure(
        DataSource arg0,
        String arg1) {
        super(arg0, arg1);
        procName = arg1;
        ds = arg0;
        initProc();

    }

    /**
     * @param arg0
     * @param arg1
     */
    public ClaimNumberByClaimNumberStoredProcedure(
        JdbcTemplate arg0,
        String arg1) {
        super(arg0, arg1);
        procName = arg1;
        ds = arg0.getDataSource();
        initProc();
    }

    private void initProc() {
        setDataSource(ds);
        setSql(procName);

        declareParameter(new SqlOutParameter("SQLCODE", Types.DOUBLE));
        declareParameter(new SqlOutParameter("SQLERRMESG", Types.VARCHAR));
        declareParameter(new SqlOutParameter("DEBUGINFO1", Types.VARCHAR));
        declareParameter(new SqlOutParameter("DEBUGINFO2", Types.VARCHAR));
        declareParameter(new SqlOutParameter("RESPONSE", Types.VARCHAR));

        declareParameter(new SqlParameter("myCoPOLNUM", Types.VARCHAR));
        declareParameter(new SqlParameter("myCoMAJORP", Types.VARCHAR));
        declareParameter(new SqlParameter("myCoMINORP", Types.VARCHAR));
        declareParameter(new SqlParameter("INTERFACEFLG", Types.VARCHAR));
        declareParameter(new SqlParameter("APPNAME", Types.VARCHAR));
        declareParameter(new SqlParameter("CLIENTID", Types.VARCHAR));
        compile();
    }

}

[trisberg]

Declare a "SqlReturnResultSet(String name, RowMapper rm) " as the first parameter. The RowMapper is responsible for creating an object and populating it with data from each returned row. See http://forum.springframework.org/showthread.php?t=10004 for a brief example.

The List of objects created by the RowMapper is stored in the Map returned with a key that is the name specified for theSqlReturnResultset parameter.

[pglezen]

Hi Folks,

I'm also having difficulty getting started with stored procedures.
In my case, the DB is Oracle. The arguments are

• in - String
  in - String
  out - ResultSet
  in - Integer

Presently, the SP is invoked through
JDBC in the following manner:

  //  call is CallableStatement, conn is Connection
  call = conn.prepareCall("{call pkg_product.p_get_by_uid (?,?,?,?)}");
  call.setString(1, uid);
  call.setString(2, companyId);
  call.registerOutParameter(3, OracleTypes.CURSOR);
  call.setInt(4,  MyConstants.RESULTSET_TYPE_WEB);
  call.execute();
  rs = (ResultSet) call.getObject(3);

I'm trying to invoke it through String's StoredProcedure mechanism
like this:

  public GetProdByUid(DataSource ds) {
    super(ds, SP_NAME);
    setParameters();
  }

  private void setParameters() {
    declareParameter(new SqlParameter(PARAM_UID_LIST, Types.VARCHAR));
    declareParameter(new SqlParameter(PARAM_COMPANY_ID, Types.VARCHAR));
    declareParameter(new SqlReturnResultSet(PARAM_PRODUCT_RS, new ProductRowMapper()));
    declareParameter(new SqlParameter(PARAM_RESULT_TYPE, Types.INTEGER));
    compile();
  }
	
  public ProductResultBean getProdByUid(String uid) {
    Map inputMap = new HashMap(5);
    inputMap.put(PARAM_UID_LIST, uid);
    inputMap.put(PARAM_COMPANY_ID, null);
    inputMap.put(PARAM_PRODUCT_RS, new Integer(OracleTypes.CURSOR));
    inputMap.put(PARAM_RESULT_TYPE, new Integer(RMSConstants.RESULTSET_TYPE_WEB));
    Map outputMap = execute(inputMap);
    List productList = (List) outputMap.get(PARAM_PRODUCT_RS);
    ProductResultBean result = (ProductResultBean) productList.get(0);
	
    return result;
  }

I get the following error:
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar

[{call pkg_product.p_get_by_uid(?, ?, ?)}];

The declareParameter does not seem to count the SqlReturnResultSet
as a parameter. I've tried moving the out-parameter to the beginning,
which seems to have worked for others. But I still get the error. I
suspect the SP on the Oracle side wants four parameters, but the
Spring base class is only registering three. Any hints?

Thanks,
- Paul

[trisberg]

From a JDBC perspective Oracle does not return resultsets, but you can use a ref cursor as a return type. To get to the ref cursor you declare an SqlOutparameter of type OracleTypes.CURSOR and a RowMapper as the third parameter. See
http://forum.springframework.org/showthread.php?t=11216 for an example. The end results are pretty much the same.

[pglezen]

Thanks, Thomas. I no longer receive grief regarding the SP grammar. But now I am having trouble with a ClassCastException

java.lang.ClassCastException: java.lang.Integer
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2022)
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2052)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:173)
	at 
....(snip)

I suspect it's because the JDBC driver through which I am trying to
invoke the SP is expecting a Java int.
The original JDBC code invoking the SP has the following statement
for the fourth parameter.

call.setInt(4,  MyConstants.RESULTSET_TYPE_WEB);

Since I need to use a java.util.Map implementation to pass my parameters,
I wrapped the int in an Integer.

declareParameter(new SqlParameter(PARAM_RESULT_TYPE, java.sql.Types.INTEGER));
...
inputMap.put(PARAM_RESULT_TYPE, new Integer(MyConstants.RESULTSET_TYPE_WEB));

Is there a standard way to repace the call.setInt() method?

Thanks,
- Paul

[trisberg]

That part looks fine. I'm wondering if you have an extra parameter in your input Map. You should have 3 entries in the Map you are passing in - one for each in-parameter. Something like:

    Map inputMap = new HashMap(3);
    inputMap.put(PARAM_UID_LIST, uid);
    inputMap.put(PARAM_COMPANY_ID, null);
    inputMap.put(PARAM_RESULT_TYPE, new Integer(RMSConstants.RESULTSET_TYPE_WEB));
    Map outputMap = execute(inputMap);
    List productList = (List) outputMap.get(PARAM_PRODUCT_RS);
    ProductResultBean result = (ProductResultBean) productList.get(0);

[pglezen]

That did the trick. I'll keep in mind to only include as many elements in the
input Map as I have explicitly declared as input parameters.

Thanks for the prompt help.

- Paul