问题描述:

I have a solution to retrieve DB2 cursors from a stored procedure but I am looking for a better approach. The current solution looks like described below:-

Stored Proc:-

CREATE OR REPLACE PROCEDURE Proc(c1 OUT SYS_REFCURSOR, c2 OUT SYS_REFCURSOR,

c3 OUT SYS_REFCURSOR, ....)

is

BEGIN

open c1 FOR select feild1,feild2,.... from RSPNSE_TABLE1;

open c2 FOR select feild1,feild2,.... from RSPNSE_TABLE2;

open c3 FOR select feild1,feild2,.... from RSPNSE_TABLE3;

open c4 FOR select feild1,feild2,.... from RSPNSE_TABLE4;

open c5 FOR select feild1,feild2,.... from RSPNSE_TABLE5;

// continue this i got least 30 tables

END;

The java code to retrieve these cursors which looks like this:-

public boolean callProc() throws Exception {

Connection conn = openConnection();

CallableStatement callableStatement = null;

ResultSet rs = null;

int rowCount = 0;

try {

String proc = "call Proc(?,?,?,?,.....)";

callableStatement = conn.prepareCall(proc);

callableStatement.registerOutParameter(1, DB2Types.CURSOR);

callableStatement.registerOutParameter(2, DB2Types.CURSOR);

callableStatement.registerOutParameter(3, DB2Types.CURSOR);

.....

.....

boolean results = callableStatement.execute();

rs = (ResultSet) callableStatement.getObject(1);

// retrieve data from resultset

rs = (ResultSet) callableStatement.getObject(2);

// retrieve data from resultset

......

......

} catch (Exception e) {

throw e;

}

return true;

}

I have following questions about this,

  1. Is there batter way to return multiple cursors from DB2 Proc ?
  2. If so then what is java code to retrieve it from callable statement ?

Note: Individual answer for both is excepted. Even pointing out to right resources where I can read and achieve is also excepted.

相关阅读:
Top