Output params are not returns in Execute sp invocation

[anilkasalanati]

We need help in reading out parameters from out DB2 when we use execute ( async method). We are getting null response. But when we call in sync mode ( executeSync) it works. Please suggest how we can read out parameters in async mode.

IBM_DB package used - "ibm_db": "^3.2.1"

Example - The following code never returns any result or outParams when we use async version of execute. Data and outparams both are null here. Async Code ( Not working)

db = await ibm_db.open(this.#db2_connectionStr)
const statement = await db.prepare(sp_query)
statement.execute(params, (err, result, outparams) => {
   // outparams is null here 
   // result is empty here 
})

The following synchronous code works and returns the result and outparams Sync code ( Working)

db = await ibm_db.open(this.#db2_connectionStr)
const statement = await db.prepare(sp_query)
const res = statement.executeSync(params)
let [result,  outparams] = Array.isArray(res) ? res : []
const data = result.fetchAllSync();

[girish-jha]

it will work only if all of open, prepare & execute functions use callback method rather than await. we can wrap them all in a single function which returns a promise. Following worked for me

function executeAsync(query, params){
    return new Promise((resolve, reject) => {
        ibm_db.open(db2_connectionStr, (err, db) => {
            if (err) {
                dispose([db])
                reject(err)
                return
            }
            db.prepare(query, (err, statement) => {
                if (err) {
                    dispose([db])
                    reject(err)
                    return
                }
                else statement.execute(params, (err, result, outparams) => {
                    if (err) {
                        dispose([result, db])
                        reject(err)
                        return
                    }
                    else if (!outparams) {
                        dispose([result, db])
                        reject(err)
                        return
                    }
                    else {
                        const data = []
                        data.push(result?.fetchAllSync())
                        while (result.moreResultsSync())
                            data.push(result?.fetchAllSync())
                        dispose([result, db])
                        resolve({ data, outparams })
                        return
                    }
                })
            })
        })

    })

    function dispose(disposables) {
        disposables.forEach(d => d?.closeSync())
    }
}

[bimalkjha]

@anilkasalanati Please update database connection info in ibm_db/test/config.testConnectionStrings.json file and then execute node test/test-sp-resultset-execute.js command. This test file verifies the similar scenario and we can see stmt.execute() is returning outparams at https://github.com/ibmdb/node-ibm_db/blob/master/test/test-sp-resultset-execute.js#L89C16-L89C16 You can check the result of test program. This test case works fine for us. Thanks.

[bimalkjha]

@girish-jha Fixed the issue in latest commit. Closing the issue now. Thanks.