Announcement Announcement Module
Collapse
No announcement yet.
DB2 Stored procedure with Multiple resultset Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • DB2 Stored procedure with Multiple resultset

    I am using a DB2 stored procedure which returns two resultset.
    I am getting only one resultset back from my StoredProcedure class, not the second one.

    See my code. This calls only Result1Impl not Result2Impl.
    (NOTE: I tested this outside of spring that returns two resultset)
    Any help is greatly appreciated.

    Code:
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    import java.util.List;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Types;
    import javax.sql.DataSource;
    
    
    import org.apache.commons.lang.StringUtils;
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.core.SqlReturnResultSet;
    import org.springframework.jdbc.datasource.*;
    import org.springframework.jdbc.object.StoredProcedure;
    
    
    import com.proc.out.MyObj1;
    
    
    
    public class TestStoredProc {
    
    	private static Log log = LogFactory.getFactory().getInstance(TestStoredProc.class);
    
    	private static String db2Driver = "COM.ibm.db2.jdbc.app.DB2Driver";
    	private static String db2TestURL = "jdbc:db2:db2test";
    
    
        public static void main(String[] args)  {
            TestStoredProc t = new TestStoredProc();
            t.test();
            System.out.println("Done!");
        }
    
        void test() {
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName(db2Driver);
            ds.setUrl(db2TestURL);
            ds.setUsername("MYUSER");
            ds.setPassword("MYPWD");
    
            MyStoredProcedure sproc = new MyStoredProcedure(ds);
            Map res = sproc.fetchDetails("patham");
            printMap(res);
        }
    
        private class MyStoredProcedure extends StoredProcedure {
            public static final String SQL = "PRC200";
    
    		public MyStoredProcedure (DataSource ds) {
    			setDataSource(ds);
    			//setFunction(true);
    			setSql(SQL);
    
    			declareParameter(new SqlReturnResultSet("rs", new Result1Impl()));
    			declareParameter(new SqlReturnResultSet("rs1", new Result2Impl()));
    
    			declareParameter(new SqlParameter("Username",Types.VARCHAR));
    			declareParameter(new SqlOutParameter("outparam1",Types.VARCHAR));
    
    			compile();
    		}
    
    		/**
    		* fetchDetails
    		*
    		* @param username String
    		* @return Map
    		*/
    		public Map fetchDetails(String username)
    		{
    			HashMap params = new HashMap();
    			params.put("Username", username);
    			HashMap returnMap = new HashMap();
    
    			log.debug(" Before execute ");
    
    			Map out = execute(params);
    			log.debug(" Before rs");
    
    			List resultList1 = (List) out.get("rs");
    
    			log.debug(" Before rs1");
    
    			List resultList2 = (List) out.get("rs1");
    
    			returnMap.put("RESULT1",resultList1 );
    			returnMap.put("RESULT2",resultList2 );
    
    			log.debug(" return returnMap");
    
    			return returnMap;
    		}
    
    	    private class Result1Impl implements RowMapper {
            	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    			log.debug(" Inside Result1Impl");
    
    			MyObj1 myObj1 = new MyObj1();
    			myObj1.setAccount(StringUtils.left(rs.getString("ACCOUNT"),8));
    			myObj1.setDesc(StringUtils.trim(rs.getString("ACCOUNT_DESC")));
    
    			return myObj1;
            	}
        	   }
    
    
    	    private class Result2Impl implements RowMapper {
            	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    			log.debug(" Inside Result2Impl");
    
    			MyObj1 myObj1 = new MyObj1();
    			myObj1.setAccount(StringUtils.left(rs.getString("DEP2_ACCOUNT"),8));
    			myObj1.setDesc(StringUtils.trim(rs.getString("DEP2_ACCOUNT_DESC")));
    
    			return myObj1;
            	}
        	}
    
    
    	}
    
        private static void printMap(Map r) {
            Iterator i = r.keySet().iterator();
            while (i.hasNext()) {
                System.out.println((String) i.next().toString());
            }
        }
    }
    Thanks

    Patham

  • #3
    The following might be relevant:

    http://forum.springframework.org/showthread.php?t=44962

    Comment


    • #4
      what happens when you declare rs1 first?

      Comment

      Working...
      X