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

  • Accessing resultset returned by DB2 Stored procedure

    Hi,

    I am not able to access the result set returned by a DB2 Stored procedure. All i do in the SP is open a cursor for select and leaving the curson open. The map returned by execute is always empty

    Could someone share a sample code and executes a DB2 SP and iterates the resultset returned by the SP using Spring JDBC API?

    Would appreciate if someone could be of any help to me

    Thanks
    Badri

  • #2
    Have you already done this in JDBC and you're trying to port this to Spring?
    Last edited by karldmoore; Aug 27th, 2007, 04:19 PM.

    Comment


    • #3
      Can you post the stored procedure declaration? That will give me some info as what you need to do to get it working. If you have declared your DB2 proc with "DYNAMIC RESULT SETS 1" then you could declare a SqlReturnResultSets as the first parameter for your StoredProcedure. Include a RowMapper to map the results. See http://static.springframework.org/sp...ResultSet.html

      Comment


      • #4
        I have simillar problem:
        Code:
        CREATE procedure DrawReport1(ip_draw_id decimal,
        ip_draw_dt timestamp,
        ip_u1 decimal,
        ip_u2 integer,
        ip_u3 decimal,
        ip_u4 decimal,
        ip_u5 decimal,
        ip_u6 decimal
         ) LANGUAGE SQL
         DYNAMIC RESULT SETS 1
         BEGIN
         DECLARE C1 CURSOR FOR SELECT * FROM bet;
         OPEN C1;
         RETURN;
         END
        and here is java code:
        Code:
        	public DrawReportSP(DataSource ds) {
        		super(ds, "DrawReport1");
        		declareParameter(new SqlReturnResultSet("C1", new RowMapper() {
        			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        				System.out.println("rs=" + rs);
        				return "XXX";
        			}
        		}
        		));
        		declareParameter(new SqlParameter("ip_draw_id", Types.INTEGER));
        		declareParameter(new SqlParameter("ip_draw_dt", Types.DATE));
        		declareParameter(new SqlParameter("ip_u1", Types.INTEGER));
        		declareParameter(new SqlParameter("ip_u2", Types.INTEGER));
        		declareParameter(new SqlParameter("ip_u3", Types.INTEGER));
        		declareParameter(new SqlParameter("ip_u4", Types.INTEGER));
        		declareParameter(new SqlParameter("ip_u5", Types.INTEGER));
        		declareParameter(new SqlParameter("ip_u6", Types.INTEGER));
        		compile();
        	}
        
        	public Map execute(Draw draw) {
        			Map params = new HashMap();
        			params.put("ip_draw_id", draw.getId());
        			params.put("ip_draw_dt", draw.getDrawDt());
        			params.put("ip_u1", new Integer(1));
        			params.put("ip_u2", new Integer(2));
        			params.put("ip_u3", new Integer(3));
        			params.put("ip_u4", new Integer(4));
        			params.put("ip_u5", new Integer(5));
        			params.put("ip_u6", new Integer(45));
        
        			System.out.println("CallString=" + super.getCallString());
        			Map result = execute(params);
        			System.out.println("result=" + result);
        			System.out.println("result c1=" + result.get("C1"));
        			return result;
        	}
        I always get empty response...
        Please help!!!
        And here is log:
        Code:
        CallString={call DrawReport1(?, ?, ?, ?, ?, ?, ?, ?)}
        result={}
        result c1=null

        Comment


        • #5
          Anyone!!!!


          What is NAME in SqlReturnResultSet. SHould I have some name C1 defined in db2 stored procedure?
          new SqlReturnResultSet(NAME, ...

          Comment

          Working...
          X