how call sql procedure that returns result set?

Issue #10 resolved
Steve_Richter created an issue

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)

  1. Kristopher Baehr

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

  2. Jesse G

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

  3. Musse

    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"}]
    
  4. Musse

    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.

  5. mengxumx Account Deactivated

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

    Details see -> 6fa8b0e

  6. mengxumx 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
    });
    
  7. Log in to comment