Stored Procedure Dynamic Result Set

Use this board to ask questions or have discussions with other Profound.js users.
trjlove
New User
Posts: 4
Joined: Tue Apr 23, 2019 2:00 pm
First Name: Tim
Last Name: Love
Company Name: Wright Flood
Phone: 7275685518
State / Province: Florida
Zip / Postal Code: 33702
Country: United States
Contact:

Stored Procedure Dynamic Result Set

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Stored Procedure Dynamic Result Set

Post by Scott Klement »

Are you trying to do this with pjs.query() or with one of the other methods?
trjlove
New User
Posts: 4
Joined: Tue Apr 23, 2019 2:00 pm
First Name: Tim
Last Name: Love
Company Name: Wright Flood
Phone: 7275685518
State / Province: Florida
Zip / Postal Code: 33702
Country: United States
Contact:

Re: Stored Procedure Dynamic Result Set

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: Stored Procedure Dynamic Result Set

Post by 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
New User
Posts: 4
Joined: Tue Apr 23, 2019 2:00 pm
First Name: Tim
Last Name: Love
Company Name: Wright Flood
Phone: 7275685518
State / Province: Florida
Zip / Postal Code: 33702
Country: United States
Contact:

Re: Stored Procedure Dynamic Result Set

Post by 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 ;             
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests