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.
Stored Procedure Dynamic Result Set
-
- 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:
-
- 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
Are you trying to do this with pjs.query() or with one of the other methods?
-
- 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
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"));
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"));
-
- 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
I tried with executeDirect() like this and it sort-of works:
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.
Code: Select all
var stm = pjs.allocStmt();
stm.executeDirect("call testsp");
while (row = stm.fetch()) {
// use row here
}
stm.close();
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.
-
- 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
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();
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 ;
Who is online
Users browsing this forum: No registered users and 0 guests