Re: nested java stored procedure question

[Yan Zhang <yzhtech@xxxxxxxxx>]

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