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,
- Is there batter way to return multiple cursors from DB2 Proc ?
- 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.