how call sql procedure that returns result set?

[abmusse]

Original report by Steve_Richter (Bitbucket: Steve_Richter, GitHub: Unknown).

---

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 )

#!js

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.

#!sql

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                                                

[abmusse]

Original comment by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

Below is the test case -->

#!javascript

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
});

[abmusse]

Original comment by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

@Steve_Richter I have updated idb-connector to v1.1.1 to support fetching result set in stored procedures.

Details see -> 6fa8b0e

[abmusse]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

Assigning to @dmabupt to address the remaining question in @abmusse's latest update.

[abmusse]

Maybe @dmabupt can chime in and let us know if the design only allows stored procedure calls result sets through prepare() , execute() , and fetchAll() combination.

[abmusse]

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:

#!sql

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

#!javascript
    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:

#!sql
Error: SQLSTATE=0100C SQLCODE=466 1 result sets are available from procedure SAMPLEPROC in QIWS.

Then ran the exec().

#!javascript
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:

#!javascript
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().

#!javascript
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

#!javascript
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"}]

[abmusse]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

@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.

[abmusse]

Original comment by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

@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.