how call sql procedure that returns result set?
the dbstmt exec method is not return a result set from an sql procedure. I am using it wrong?
When the stmt passed to the exec method is an SQL SELECT, I get back a result set. But when that statement is an SQL CALL, I do not get back a result set.
Here is the code that fails ( is just a modification of IBM sample code )
var db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a');
var sql = "call qgpl.sample101 " ;
var dbconn = new db.dbconn(); // Create a connection object.
dbconn.conn("*LOCAL"); // Connect to a database.
var stmt = new db.dbstmt(dbconn); // Create a statement object of the connection.
stmt.exec(sql, function(result)
{
console.log("Result: %s", JSON.stringify(result));
var fieldNum = stmt.numFields();
console.log("There are %d fields in each row.", fieldNum);
console.log("Name | Length | Type | Precise | Scale | Null");
for(var i = 0; i < fieldNum; i++)
{
console.log("%s | %d | %d | %d | %d | %d",
stmt.fieldName(i), stmt.fieldWidth(i), stmt.fieldType(i), stmt.fieldPrecise(i),
stmt.fieldScale(i), stmt.fieldNullable(i));
}
delete stmt ;
dbconn.disconn(); // Disconnect from the database.
delete dbconn ;
});
and here is the code of the SQL procedure.
create OR REPLACE procedure qgpl.sample101
(
)
language sql
DYNAMIC RESULT SETS 1
BEGIN atomic
DECLARE C1 CURSOR FOR
select decimal(2,3,0) linn, char('abc',80) text
from sysibm.sysdummy1
union all
select decimal(1,3,0) linn, char('efg',80) text
from sysibm.sysdummy1
order by 1 ;
OPEN C1;
SET RESULT SETS WITH RETURN TO CLIENT CURSOR C1 ;
END
Comments (8)
-
-
-
assigned issue to
@krisbaehr describes the proper way to do this. We should update the doc accordingly.
Assigning to @abmusse to verify that exec does not work for procedures, and to document the shortcoming and alternative.
-
assigned issue to
-
Yes, after experimenting with this myself I can confirm that the exec method produces an error when executing this stored procedure.
The result set is also not returned.
I first created a stored procedure similar to the original post:
create OR REPLACE procedure QIWS.sampleProc ( ) language sql DYNAMIC RESULT SETS 1 BEGIN ATOMIC DECLARE C1 CURSOR FOR SELECT * FROM QIWS.QCUSTCDT; OPEN C1 ; SET RESULT SETS WITH RETURN TO CLIENT CURSOR C1 ; END
Then tried the execSync() mehod
const dba = require('idb-connector'); var dbconn = new dba.dbconn(); dbconn.conn("*LOCAL"); var stmt = new dba.dbstmt(dbconn); //change this to your stored procdure var proc = "call QIWS.sampleProc"; stmt.execSync(proc, function(result , dbError){ console.log("Result Exec: %s", JSON.stringify(result)); }) //end exec stmt.close();
the error generated is:
Error: SQLSTATE=0100C SQLCODE=466 1 result sets are available from procedure SAMPLEPROC in QIWS.
Then ran the exec().
const dba = require('idb-connector'); var dbconn = new dba.dbconn(); dbconn.conn("*LOCAL"); var stmt = new dba.dbstmt(dbconn); //change this to your stored procdure var proc = "call QIWS.sampleProc"; stmt.exec(proc, function(result , dbError){ console.log("Result Exec: %s", JSON.stringify(result)); }) //end exec stmt.close();
The result is:
Result Exec: []
However when I called prepare() , execute() , and fetchAll() combination instead the stored procedure was executed and the result set was shown properly.
I recommend using prepare() , execute() , and fetchAll() combination for displaying your stored procedure until the issue is fully resolved for exec().
const dba = require('idb-connector'); var dbconn = new dba.dbconn(); dbconn.conn("*LOCAL"); var stmt = new dba.dbstmt(dbconn); var proc = "call QGPL.SAMPLE101"; stmt.prepare(proc, function(err) { console.log("error = %s", err); console.log("Prepared"); stmt.execute(function(result){ console.log("Executing"); stmt.fetchAll(function(result, err) { console.log("error = %s", err); console.log(`Result is:\n ${JSON.stringify(result)}`); stmt.close(); }); //end fetchAll }) //end execute }); //end prepare
My Results Back were
error = undefined Prepared Executing error = undefined Result is: [{"CUSNUM":"938472","LSTNAM":"Henning ","INIT":"G K","STREET":"4859 Elm Ave ","CITY":"Dallas","STATE":"TX","ZIPCOD":"75217","CDTLMT":"5000","CHGCOD":"3","BALDUE":"37.00","CDTDUE":".00"},{"CUSNUM":"839283","LSTNAM":"Jones ","INIT":"B D","STREET":"21B NW 135 St","CITY":"Clay ","STATE":"NY","ZIPCOD":"13041","CDTLMT":"400","CHGCOD":"1","BALDUE":"100.00","CDTDUE":".00"},{"CUSNUM":"392859","LSTNAM":"Vine ","INIT":"S S","STREET":"PO Box 79 ","CITY":"Broton","STATE":"VT","ZIPCOD":"5046","CDTLMT":"700","CHGCOD":"1","BALDUE":"439.00","CDTDUE":".00"},{"CUSNUM":"938485","LSTNAM":"Johnson ","INIT":"J A","STREET":"3 Alpine Way ","CITY":"Helen ","STATE":"GA","ZIPCOD":"30545","CDTLMT":"9999","CHGCOD":"2","BALDUE":"3987.50","CDTDUE":"33.50"},{"CUSNUM":"397267","LSTNAM":"Tyron ","INIT":"W E","STREET":"13 Myrtle Dr ","CITY":"Hector","STATE":"NY","ZIPCOD":"14841","CDTLMT":"1000","CHGCOD":"1","BALDUE":".00","CDTDUE":".00"},{"CUSNUM":"389572","LSTNAM":"Stevens ","INIT":"K L","STREET":"208 Snow Pass","CITY":"Denver","STATE":"CO","ZIPCOD":"80226","CDTLMT":"400","CHGCOD":"1","BALDUE":"58.75","CDTDUE":"1.50"},{"CUSNUM":"846283","LSTNAM":"Alison ","INIT":"J S","STREET":"787 Lake Dr ","CITY":"Isle ","STATE":"MN","ZIPCOD":"56342","CDTLMT":"5000","CHGCOD":"3","BALDUE":"10.00","CDTDUE":".00"},{"CUSNUM":"475938","LSTNAM":"Doe ","INIT":"J W","STREET":"59 Archer Rd ","CITY":"Sutter","STATE":"CA","ZIPCOD":"95685","CDTLMT":"700","CHGCOD":"2","BALDUE":"250.00","CDTDUE":"100.00"},{"CUSNUM":"693829","LSTNAM":"Thomas ","INIT":"A N","STREET":"3 Dove Circle","CITY":"Casper","STATE":"WY","ZIPCOD":"82609","CDTLMT":"9999","CHGCOD":"2","BALDUE":".00","CDTDUE":".00"},{"CUSNUM":"593029","LSTNAM":"Williams","INIT":"E D","STREET":"485 SE 2 Ave ","CITY":"Dallas","STATE":"TX","ZIPCOD":"75218","CDTLMT":"200","CHGCOD":"1","BALDUE":"25.00","CDTDUE":".00"},{"CUSNUM":"192837","LSTNAM":"Lee ","INIT":"F L","STREET":"5963 Oak St ","CITY":"Hector","STATE":"NY","ZIPCOD":"14841","CDTLMT":"700","CHGCOD":"2","BALDUE":"489.50","CDTDUE":".50"},{"CUSNUM":"583990","LSTNAM":"Abraham ","INIT":"M T","STREET":"392 Mill St ","CITY":"Isle ","STATE":"MN","ZIPCOD":"56342","CDTLMT":"9999","CHGCOD":"3","BALDUE":"500.00","CDTDUE":".00"}]
-
Maybe @mengxumx can chime in and let us know if the design only allows stored procedure calls result sets through prepare() , execute() , and fetchAll() combination.
-
-
assigned issue to
Assigning to @mengxumx to address the remaining question in @abmusse's latest update.
-
assigned issue to
-
Account Deactivated @Steve_Richter I have updated idb-connector to v1.1.1 to support fetching result set in stored procedures.
Details see -> 6fa8b0e
-
Account Deactivated Below is the test case -->
var db = require('idb-connector'); var sql = "call qgpl.sample101" ; var dbconn = new db.dbconn(); // Create a connection object. dbconn.conn("*LOCAL"); // Connect to a database. var stmt = [ new db.dbstmt(dbconn), new db.dbstmt(dbconn), new db.dbstmt(dbconn), new db.dbstmt(dbconn) ]; // Create a statement object of the connection. stmt[0].execSync(sql, (result) => { console.log("[execSync]:\n\t%s", JSON.stringify(result)); stmt[0].close(); }); stmt[1].prepareSync(sql, (err) => { stmt[1].executeSync((err) => { stmt[1].fetchAllSync((result, err) => { console.log("[fetchAllSync]:\n\t%s", JSON.stringify(result)); stmt[1].close() }); }); }); stmt[2].exec(sql, (result) => { console.log("[exec]:\n\t%s", JSON.stringify(result)); stmt[2].close(); stmt[3].prepare(sql, (err) => { stmt[3].execute(() => { stmt[3].fetchAll((result, err) => { console.log("[fetchAll]:\n\t%s", JSON.stringify(result)); stmt[3].close(); dbconn.disconn(); // Disconnect from the database. }); //end fetchAll }) //end execute }); //end prepare });
-
Account Deactivated - changed status to resolved
- Log in to comment
@Steve_Richter I'm able to process result sets from a Stored Procedure call. I'm using stmt.prepare, stmt.bind (if needed), stmt.execute then stmt.fetchAll.