Can't get correct result when call store procedure in IBMDB2

[manhha1006]

In IBMDB2, I can't call procedure with type parameter is output. I always get result is null array

---- Store Procedure SP_PROC1
CREATE PROCEDURE sp_proc1 (IN VARNAME VARCHAR(128), OUT VARCOUNT INTEGER)
P1: BEGIN
-- #######################################################################
-- # Returns count of tables created by ROOT and like VARNAME
-- #######################################################################
SELECT COUNT(*) INTO VARCOUNT FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'ROOT' AND NAME LIKE CAST('%'||VARNAME||'%' as VARCHAR(128));
END P1

In the db2 studio I created store procedure and run test with input parameter VARNAME = "CA" and it return value with output parameter VARCOUNT = 3

code in nodejs

db.query("call ROOT.SP_PROC1(?,?)", ['CA',0] , function (err, rows, more) {
      console.log( err, rows, more );
      db.close(function () {
        console.log("Connection Closed");
      });
})

console log

null [] false

Can you help me? Thanks you so much.

[wankdanker]

We don't handle bound parameters for output as you are intending to use here.

You'd be better off with something like :

---- Store Procedure SP_PROC1
CREATE PROCEDURE sp_proc1 (IN VARNAME VARCHAR(128))
P1: BEGIN
-- #######################################################################
-- # Returns count of tables created by ROOT and like VARNAME
-- #######################################################################
SELECT COUNT(*) as VARCOUNT FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'ROOT' AND NAME LIKE CAST('%'||VARNAME||'%' as VARCHAR(128));
END P1

I'm not sure if that is valid SQL for DB2, but what I'm attempting to do is just return a record set with VARCOUNT as the only column. That should definitely work.

Sorry it took so long to reply to this.

[willfarrell]

I've run into this same issue. I found http://sqlrelay.sourceforge.net/sqlrelay/programming/nodejs.html#storedprocedures, not on npm, may help in getting this feature implemented.

[himanshusoni]

@willfarrell what was the solution for your case ? For me, the SP cannot be changed and hence i have to find a solution to bind the output parameters in nodejs.

[willfarrell]

I put the stored procedure part of my project on the back burner for now. There are two options I plan to try when I revisit.

1. sqlrelay - Ask David Muse for the lastest build. I found a bug in the current build. Also it cannot accept a connect pass odbc connection string yet (just params right now).
2. Use a temp table. Code snippet from Howard F Arner Jr. Quicks test didn't result in anything.

SELECT * FROM qtemp.temp1;
CREATE TABLE qtemp.temp1
(ITEM CHAR(7), UOM CHAR(3), QUANTITY CHAR(7), UPRICE CHAR(7), UCOST CHAR(7), BRANCH CHAR(3), CUSTOMER CHAR(6));

INSERT INTO qtemp.temp1 VALUES ('  11694',' LB','      1','       ','       ','677','  1280');

SELECT weboe.partprice(item,uom,quantity,UPRICE,UCOST,BRANCH,CUSTOMER) FROM qtemp.temp1;

Let me know how you fare with these @himanshusoni . I'll likely need to circle back to this part of my project in a couple weeks. Hope this helps.

[himanshusoni]

@willfarrell i found it as a dead end for me, since amount of work to be invested in either working on 'feature for node-odbc' or 'changing the SP or a workaround like above' was more than simply adding a WS endpoint in my existing services stack in java.

Thanks for looking into it.

[zenflow]

ibmdb/node-ibm_db is a fork of this repo, and has the same exact same issue... ibmdb/node-ibm_db#7

I had success using the work-around contributed by @RocketRooster, i.e. return results via a cursor rather than "out parameters"

[rhtpandeyIN]

@zenflow , @manhha1006 , @himanshusoni If you guys still stuck with this issue then you can try this with "node-ibm_db" https://github.com/ibmdb/node-ibm_db . It does support OUT parms now. Thanks

[RocketRooster]

Whoohoo!

So, uh, who's in for another round of regression testing on their production code....?

[rhtpandeyIN]

@RocketRooster , Here is the doc link : https://github.com/ibmdb/node-ibm_db#bindingparameters Hope it will help you.