Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedure Limitation? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure Limitation?

    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
    Code:
    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:
    Code:
    /*
     * 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();
        }
    
    }

  • #2
    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.
    Last edited by robyn; May 14th, 2006, 10:45 AM.

    Comment


    • #3
      Similar trouble with Oracle

      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:

      Code:
        //  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:

      Code:
        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

      Comment


      • #4
        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.
        Last edited by robyn; May 14th, 2006, 10:43 AM.

        Comment


        • #5
          Primitive types

          Thanks, Thomas. I no longer receive grief regarding the SP grammar. But now I am having trouble with a ClassCastException
          Code:
          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.
          Code:
          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.
          Code:
          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

          Comment


          • #6
            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:

            Code:
                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);

            Comment


            • #7
              That's it.

              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

              Comment

              Working...
              X