Stored Procedure Dynamic Result Set

[trjlove]

looking for help calling a program on the IBM i or a Stored Procedure that returns a result set.

I have tried about every possible way and I am not able to figure out how to capture the return. I am able to call both the program or the stored procedure.

[Scott Klement]

Are you trying to do this with pjs.query() or with one of the other methods?

[trjlove]

I have tried many different methods, below are a few different ways. The stored procedure and/or the program calls both work successfully without error. my only issue is trying to figure out how to access the result set.

var sqlStmt = "call someLib.someStoredProc ('" + Parm1 + "','" + Parm2 + "','" + Parm3 + "','" + Parm4 + "','" + Parm5 + "','" + Parm6 + "','" + Parm7 + "','" + Parm8 + "')";
stmt.executeDirect(stmt);
var row = stmt.fetch();
// pjs.fetch(DSCLEAN, DSADR1, DSADR2, DSCITYN, DSSTCD, DSPC, DSCC3166);

stmt.close();
// response.json(row);


pjs.call("someLib/someProgram", pjs.refParm("Parm1"), pjs.refParm("Parm2"), pjs.refParm("Parm3"), pjs.refParm("Parm4"), pjs.refParm("Parm5"), pjs.refParm("Parm6"), pjs.refParm("Parm7"), pjs.refParm("Parm8"));

[Scott Klement]

I tried with executeDirect() like this and it sort-of works:

Code: Select all

    var stm = pjs.allocStmt();
    
     stm.executeDirect("call testsp");

    while (row = stm.fetch()) {
        // use row here
    }

    stm.close();

I say "sort-of" because I'm getting some extra ("garbage") characters in one of the fields, and also it only works with a single result set, not with procedures that return multiple result sets.

It did not work at all for me with pjs.query(), though.

I see that you've already reported this to Profound Logic Support, so they will look into it and get back to you.

If you have any additional information (such as error messages, etc) please provide that, too.

[trjlove]

Code: Select all

        var stmt = pjs.allocStmt();
        var sqlStmt = "call someLib.someSP ('" + P1 + "','" + P2 + "','" + P3 + "','" + P4  + "','" + P5 + "','" + P6 + "','" + P7 + "','" + P8 + "')";
        stmt.executeDirect(stmt);

        while (row = stmt.fetch()){
            console.log(row);
            console.log(row["someColName"]);
        }
        stmt.close();

the above code does not through any errors, but never makes it into the while loop. below is the SP definition on the IBM i. hope this helps.

Code: Select all

 700  CREATE PROCEDURE someLib/someSP (
 800    IN P1 CHAR(30) ,                
 900    IN P2 CHAR(30) ,                
1000    IN P3 CHAR(30) ,               
1100    IN P4 CHAR(2) ,                 
1200    IN P5 CHAR(15) ,                  
1300    IN P6 CHAR(2) ,               
1400    IN P7 CHAR(10) ,                
1500    IN P8 CHAR(10) )             
1600    DYNAMIC RESULT SETS 1             
1700    LANGUAGE RPGLE                    
1800    SPECIFIC someLib/someSP        
1900    NOT DETERMINISTIC                 
2000    MODIFIES SQL DATA                 
2100    CALLED ON NULL INPUT              
2200    EXTERNAL NAME 'someLib/someProgram' 
2300    PARAMETER STYLE SQL ;