Page 1 of 1
Stored Procedure Dynamic Result Set
Posted: Tue Apr 23, 2019 3:06 pm
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.
Re: Stored Procedure Dynamic Result Set
Posted: Tue Apr 23, 2019 3:50 pm
by Scott Klement
Are you trying to do this with pjs.query() or with one of the other methods?
Re: Stored Procedure Dynamic Result Set
Posted: Wed Apr 24, 2019 2:21 pm
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"));
Re: Stored Procedure Dynamic Result Set
Posted: Wed Apr 24, 2019 3:26 pm
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.
Re: Stored Procedure Dynamic Result Set
Posted: Wed Apr 24, 2019 4:40 pm
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 ;