Re: nested java stored procedure question

Here is a more detailed description.
1) this is the process I create stored procedure called GETMEASURES. It
works fine.

public class RetrieveInfo {

public static void getMeasures(ResultSet[] rs)
throws SQLException {
Connection con =
DriverManager.getConnection("jdbc:default:connection");
Statement stmt = null;
String sql = "select * from measures where EXCLUDES <> 'X'";
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs[0] = stmt.executeQuery(sql);

if (con != null) {
con.close();
}

}
}


CREATE PROCEDURE someSchema/GETMEASURES ( )
DYNAMIC RESULT SETS 1
LANGUAGE JAVA
SPECIFIC someSchema/GETMEASURES
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'test/RetrieveInfo.getMeasures'
PARAMETER STYLE JAVA ;

Messages:

call IDIGTRUNK/getMeasures()

Return Code = 0

SQL State: 0100C
Vendor Code: 466
Message: [SQL0466] 1 result sets are available from procedure GETMEASURES in
someSchema. Cause . . . .

. : Procedure GETMEASURES in someSchema was called and has returned one or
more result sets.

Recovery . . . : None.

Statement ran successfully, with warnings (23 ms)

------------------------------------------------------------
2) then I was trying to create another stored procedure TESTNESTEDJAVASP,
which calls the previous sp.
-------------------------------------------------------------
public class TestNestedJavaSP {

public static void comparison(
ResultSet[] rs)
throws SQLException, Exception {

Connection con =
DriverManager.getConnection("jdbc:default:connection");
CallableStatement stmt = null;


String sql = "call someSchema.getMeasures()";
stmt = con.prepareCall(sql);
stmt.execute();
rs[0] = stmt.getResultSet();

/* if (rs[0] != null) {
while (rs[0].next()) {
// this is a funcion in a predefined class
Logging.writeDebugTbl("aSchema", con, "TestNestedJavaSP test
rs[0] ", rs

[0].getString(1));

}
} */

if (con != null) {
con.close();
}
}
}



CREATE PROCEDURE IDIGTRUNK/TESTNESTEDJAVASP ( )
DYNAMIC RESULT SETS 1
LANGUAGE JAVA
SPECIFIC someSchema/TESTNESTEDJAVASP
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'someSchema/TestNestedJavaSP.comparison'
PARAMETER STYLE JAVA ;


Messages:

call IDIGTRUNK/TESTNESTEDJAVASP()

Return Code = 0

Statement ran successfully (24 ms)


I got above messages, but no result set shown.


thanks,
yan

On Sun, Mar 28, 2010 at 10:49 PM, Yan Zhang <yzhtech@xxxxxxxxx> wrote:

I am using V6R1 system.
I created a java stored procedure sp1, which takes some input parameters,
gets connection to database, and eventually generates a result set.
sp1 runs well from the iSeries navigator "run scripts" window.

Then I created a java stored procedure sp2, which calls sp1, and will
return a result set generated by sq1.
When I run sp2, it shows "return code = 0", but does not show the generated
result set.

If I go back to my java code, the code to generate the result set is
" rs[0] = ps.getResultSet();
While(rs[0].next()){
String tmp = rs[0].getString(1);
// insert this tmp string to some database table for debug purpose
}

It successfully finished the while loop.

Can someone shed some light on why I couldn't see the result set from SQL
interface? Sample codes are welcome too!


Yan