Access to stored procedure result set

[Rudi Potgieter]

How do I access the result set of a stored procedure when called from nodejs? I'm guessing we need to change something in the stored procedure?

I get the following error message after calling the stored procedure:

* ERROR **
SQLSTATE: 0100C
Native Error Code: 466
1 result sets are available from procedure VOB_SEARCHVOBNAME in BAYTST2. [Error: SQLSTATE=0100C SQLCODE=466 1 result sets are available from procedure VOB_SEARCHVOBNAME in BAYTST2.]

--  Generate SQL 
--  Version:                    V7R1M0 100423 
--  Generated on:               18/12/15 14:38:02 
--  Relational Database:        BAYCORP2 
--  Standards Option:           DB2 for i 
SET PATH *LIBL ; 

CREATE PROCEDURE BAYTST2.VOB_SEARCHVOBNAME ( 
    IN "SEARCH" VARCHAR(40) , 
    IN CLIENT NUMERIC(6, 0) , 
    IN FROMDATE DATE , 
    IN TODATE DATE ) 
    DYNAMIC RESULT SETS 1 
    LANGUAGE SQL 
    SPECIFIC BAYTST2.VOB_SEARCHVOBNAME 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DLYPRP = *NO , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 
DECLARE CURSORALREADYOPEN CONDITION FOR '24502' ; 
DECLARE VOBCURSOR CURSOR FOR 
SELECT VOBSNAM , VOBCNM1 , VOBCNM2 , VOBBDAY , VOBGNDR , VOBADR1 , 
VOBADR2 , VOBTITL , VOBADR3 , VOBADR4 , VOBREFID 
, VOBSTRDAT , VOBENDDAT , VOBSTATUS 
FROM VOBCUS0 
WHERE VOBCLNT = CLIENT AND 
( UPPER ( VOBSNAM || ' ' || VOBCNM1 ) LIKE UPPER ( SEARCH ) 
OR UPPER ( VOBCNM1 || ' ' || VOBSNAM ) LIKE UPPER ( SEARCH ) ) 
AND DATE ( VOBSTRDAT ) BETWEEN FROMDATE AND TODATE 
ORDER BY VOBCLNT , UPPER ( VOBSNAM ) , UPPER ( VOBCNM1 ) , UPPER ( VOBCNM2 ) 
FETCH FIRST 100 ROWS ONLY ; 
DECLARE CONTINUE HANDLER FOR CURSORALREADYOPEN 
BEGIN 
CLOSE VOBCURSOR ; 
OPEN VOBCURSOR ; 
END ; 
OPEN VOBCURSOR ; 
END  ; 

GRANT ALTER , EXECUTE   
ON SPECIFIC PROCEDURE BAYTST2.VOB_SEARCHVOBNAME 
TO BAYUSE ; 

GRANT EXECUTE   
ON SPECIFIC PROCEDURE BAYTST2.VOB_SEARCHVOBNAME 
TO PUBLIC ;

var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');
var xt = require('/QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/itoolkit');
var DBname = "*LOCAL ";

function testSimpleSp(sql) {
    var term = '%a%';
    var client = '416';
    var startdate = '2015-12-01';
    var enddate = '2015-12-09';
    try {
        db.debug(true);  // Enable Debug Mode if needed.
        db.init(function () {  // Initialize the environment for database connections.
            db.serverMode(true); // Enable Server Mode if needed
        });
        db.conn(DBname, function () { // Connect to a database
            db.autoCommit(true); // Enable the Auto Commit feature if needed.
        });
        db.prepare(sql); // Prepare for the SQL statement.
        db.bindParam([  // Bind parameters for each markers in the SQL statement.
            [term, db.SQL_PARAM_INPUT, 1],
            [client, db.SQL_PARAM_INPUT, 1],
            [startdate, db.SQL_PARAM_INPUT, 1],
            [enddate, db.SQL_PARAM_INPUT, 1],
        ]);
        db.execute(function callback(out, resultset) {  //Out is an array of each output parameters.
            for (i = 0; i < out.length; i++)
                console.log("Output Param[%d] = %s \n", i, out[i]);
        });
        db.close();  // Release any used resource.
    } catch (e) {  // Exception handler
        console.log(e);
    }
};

testSimpleSp("call baytst2.vob_searchvobname(?, ?, ?, ?)");

[Aaron Bartell]

Does the store procedure work from other tools other than Node.js? Also, take a peek at this thread that also had issues with store procs from Node.js.

[Rudi Potgieter]

Yes we use that stored procedure from RPG.

I looked at that thread before posting this. That stored procedure does not return a result set.

Here is a simpler example:

Create the stored procedure:

CREATE PROCEDURE rudip.resset () LANGUAGE SQL 
 DYNAMIC RESULT SETS 1 
 BEGIN 
 DECLARE C1 CURSOR FOR SELECT * FROM QIWS.QCUSTCDT;
 OPEN C1;
 RETURN; 
 END 

Call the node script:

var db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');

var DBname = "*LOCAL ";

function testSimpleSp(sql) {
    try {
        db.debug(true);  // Enable Debug Mode if needed.
        db.init(function () {  // Initialize the environment for database connections.
            db.serverMode(true); // Enable Server Mode if needed
        });
        db.conn(DBname, function () { // Connect to a database
            db.autoCommit(true); // Enable the Auto Commit feature if needed.
        });
        db.prepare(sql); // Prepare for the SQL statement.
        db.execute(function callback(out, resultset) {  //Out is an array of each output parameters.
            console.log(JSON.stringify(resultset));
            for (i = 0; i < out.length; i++)
                console.log("Output Param[%d] = %s \n", i, out[i]);
        });
        db.close();  // Release any used resource.
    } catch (e) {  // Exception handler
        console.log(e);
    }
};

testSimpleSp("call rudip.resset");

Debug output:

node sp2.js                                                                                    
SetEnvAttr() attr = 10004, value = 1, rc = 0                                                   
Connect to *LOCAL                                                                              
SetConnAttr() attr = 10003, value = 1, rc = 0                                                  
Connected to *LOCAL  with ID .                                                                 
SQL: call rudip.resset                                                                         
SQLExecute() rc=1.                                                                             

 **** ERROR *****                                                                              
SQLSTATE: 0100C                                                                                
Native Error Code: 466                                                                         
1 result sets are available from procedure RESSET in RUDIP.                                    
[Error: SQLSTATE=0100C SQLCODE=466 1 result sets are available from procedure RESSET in RUDIP.]

So how do I access the resultset returned by the stored procedure?

[Rudi Potgieter]

I've been reading through the DB2 for i access APIs trying to find a solution.

I found an article, Stored procedure result sets, which I thought provided the solution.

I updated the stored procedure to use SCROLL CURSOR, but that didn't work. I then set the SQL_ATTR_CURSOR_TYPE to SQL_CURSOR_DYNAMIC in the nodejs script, but that also didn't fix it.

var mode = db.SQL_CURSOR_DYNAMIC;
db.cursorType(mode);

Then I tried setting SQL_ATTR_CURSOR_SCROLLABLE to true, but same result.

var mode2 = db.SQL_ATTR_CURSOR_SCROLLABLE;
var dbtrue = db.SQL_TRUE;
db.setStmtAttr(mode2, dbtrue);

Any suggestions would be appreciated.

[Aaron Bartell]

Found this, though it is for DB2 on zOS. Everything seemingly points to the stored procedure call completing successfully. I am getting the same result as you. Not sure where to go from here as I've tried a number of variances without success.

[Rudi Potgieter]

I'm guessing I'm either doing it wrong or maybe fetching the result set of a procedure is not yet supported.

Anyway I noticed that the itoolkit also does sql so I tried it and it worked, but it is allot slower than calling the sql using db2i.

var xt = require('/QOpenSys/QIBM/ProdData/Node/os400/xstoolkit/lib/itoolkit');

var DBname = "*LOCAL ";
var conn = new xt.iConn(DBname);

var sql = new xt.iSql();
sql.prepare("call rudip.resset");
sql.execute();
sql.fetch();
sql.free();
conn.add(sql);
function cb(str) {
    console.log(JSON.stringify(xt.xmlToJson(str)[0].result));
}
conn.run(cb);

Do you know of any other forums I could post this issue?

[Aaron Bartell]

I noticed that the itoolkit also does sql so I tried it and it worked, but it is allot slower than calling the sql using db2i.

The itoolkit approach, while still using a DB2 stored proc to process the request, is fairly different than the Node.js DB2 adapter. The itoolkit is serializing xml under the covers with the calls to sql.prepare, sql.execute, sql.fetch, and sql.free.

Do you know of any other forums I could post this issue?

Put a comment on the Node.js DB2 adapter developerWorks page and link them back here for a history of the problem. One of the authors of the Node.js DB2 adapter is the also the author of the documentation (as I understand it).

[Rudi Potgieter]

I put a comment on the developerWorks page, but haven't had any reply. I also tried calling a stored procedure using the itoolkit xmlcgi interface and that also fails with a similar message.

[Aaron Bartell]

Hi Rudi,

Your best bet at this point is probably to open a PMR with IBM so it can be prioritized appropriately. I believe their responses on the developerWorks pages are more "nice to have" but they aren't required to respond, so that's why a PMR is probably the way to go because they are contractually bound (if you have a maintenance contract) to respond.

[Jimmy]

Hello Rudi,

Did you ever get a resolution for the result set issue? I have run into the same problem as you.

[Rudi Potgieter]

No could not resolve the issue. I'm hoping that the issue has already been resolved in a later version so when we upgrade to 7.3 the problem will hopefully be gone.

[Former user]

Has this been solved - using the latest deliveries with Node v4?

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/The%20new%20DB2%20driver%20and%20toolkit%20for%20Node.js%20on%20IBM%20i

[Markus A. Litters]

Hi everybody,

I am using Node 6.9 on IBM i 7.3 and everything works fine - only this issue Looks like it is still open.

Does anybody have a solution for this?

[Aaron Bartell]

Can you post your test back to this thread? Make sure it has enough info so we can recreate the error.

[Markus A. Litters]

Yes - of course. After getting these Errors and finding this Thread I also tried it with the above examples and modified them.

I tried a lot of more things which went to other Errors...

Please use the Stored Procedure from above. I also tested the iToolkit Version which is running but this shouldn't be the solution...

process.env.DB2CCSID = '1208';

var db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a')

var connection = new db.dbconn();
connection.conn("*LOCAL");

var sqlA = new db.dbstmt(connection);
var sql = "call rudip.resset()";

 /* sqlA.prepare(sql, function () {
        sqlA.execute(function (out) {
            console.log(out);
            sqlA.close();
            connection.disconn();
            connection.close();
    });
}); */

/* sqlA.prepareSync(sql, function () {
    sqlA.executeSync(function (rs) {
        sqlA.fetchAllSync(function callback(out) {
            console.log(out);
            delete sqlA;
            connection.disconn();
            delete connection;
        });
    });
}) */

sqlA.prepareSync(sql);
sqlA.executeSync(function callback(out) {
    console.log("Callback");
console.log("Result: %s", JSON.stringify(out));
});
delete sqlA;
connection.disconn();
delete connection;

[Former user]

Unfortunately you will need the node db2a guys to fix this in the driver.

Demo ... works for php ...

<?php
require_once('connection.inc');
$conn = db2_connect($database, $user, $password);
$sql = <<<"FOOBAR"
CREATE PROCEDURE resset () LANGUAGE SQL 
 DYNAMIC RESULT SETS 1 
 BEGIN 
 DECLARE C1 CURSOR FOR SELECT * FROM QIWS.QCUSTCDT;
 OPEN C1;
 RETURN; 
 END
FOOBAR;
echo $sql;
$ret = db2_exec($conn,$sql);
echo("prepare ".db2_stmt_errormsg()."\n");
$sql = "call resset()";
echo $sql;
$stmt = db2_exec($conn,$sql);
echo("call ".db2_stmt_errormsg()."\n");
while ($row = db2_fetch_assoc($stmt)) {
  var_dump($row);
}


output:

bash-4.3$ php spset.php           
CREATE PROCEDURE resset () LANGUAGE SQL 
 DYNAMIC RESULT SETS 1 
 BEGIN 
 DECLARE C1 CURSOR FOR SELECT * FROM QIWS.QCUSTCDT;
 OPEN C1;
 RETURN; 
 ENDprepare 
call resset()call 
array(11) {
  ["CUSNUM"]=>
  string(6) "938472"
  ["LSTNAM"]=>
  string(8) "Henning "
  ["INIT"]=>


?>

Not work for node db2a (you need help) ...

var db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a');
var dbconn = new db.dbconn();  // Create a connection object.
dbconn.conn("*LOCAL");  // Connect to a database.
var callSync = function(sql,callback) {
  var stmt = new db.dbstmt(dbconn);
  stmt.prepareSync(sql, function(){
    stmt.executeSync(function(){
      stmt.fetchAllSync(function(result){
        callback(JSON.stringify(result));
        delete stmt;
       });
    });
  });
}
var sql = "call db2.resset()";
callSync(sql, function(yap) { console.log(yap); });

output:

bash-4.3$ node --version
v6.9.1
bash-4.3$ node sptest.js 
/home/ADC/node_db2a_basic/sptest.js:7
    stmt.executeSync(function(){
         ^

Error: SQLSTATE=0100C SQLCODE=466 1 result sets are available from procedure RESSET in DB2.
    at Error (native)
    at /home/ADC/node_db2a_basic/sptest.js:7:10

[Former user]

Aaron, I can't remember ... did we ever here back on making the db2a driver open source so we could play with it (fix)?

(*) Not me owner this case. I have no access to the source code.

[Former user]

FYI -- Readers may be asking why i tried the operation in PHP?

Both node db2a and php ibm_db2 use same base driver PASE libdb400.a. In fact nearly all new IBM i scripting languages use libdb400.a. Anyway, PHP ibm_db2 works, so we can suggest a choice can made in node db2a to 'follow php ibm_db2 pattern'. The good IBM node guys can work this out fairly easily i imagine.

BTW -- A new version of libdb400.a is being developed Open Source at litmis/db2sock should you care to look into how PASE db2 driver works.

[Aaron Bartell]

@rangercairns

Aaron, I can't remember ... did we ever here back on making the db2a driver open source so we could play with it (fix)?

The RFE for open sourcing the Node.js DB2 driver is here (must login to see, otherwise server 500 error). It has a status of "Uncommitted Candidate" which has a definition of the following:

Uncommitted Candidate: This request may not be delivered within the release currently under development, but the theme is aligned with the current multi-year strategy. IBM may consider and evaluate any RFE Community feedback for this request through activities such as voting. IBM will update this request in the future.

@ThePrez might have more info on that RFE.

[Markus A. Litters]

Many thanks for all those info...

I already created some Node programs with SQL and even with calling SP's but they return "OUT" Parameters, which is working fine.

So I never thought that something like returning a Result Set will make Problems. But who will return Result Sets from a SQL Stored Procedure on a Database machine like IBM i.... (end of ironic...). :-)

For your Info: I opend a Ticket at IBM and look forward to get some help.

In the meanwhile I created a workaround and return my values as JSON in an OUT Parameter which works fine and fast but is not that nice... I will also look for a solution with noxDB of Niels Liisberg.

I Keep you informed about the IBM Response.

[Jesse G]

@aaronbartell, the RFE state is accurate. The request is (at a minimum) aligned with the multi-year strategy and I do suspect we will deliver on this request at some point.

[Markus A. Litters]

Hi everybody,

after some trials IBM fixed the problem with the Stored Procedure Result Set now and will provide 2 new PTF's within the next days: SI65160 SI65161

I already installed and tested them and it looks good!

[Aaron Bartell]

Closing issue.