Outparams is null when using connection pool and previously DB error in the connection

[girish-jha]

Hi Team,

I am using node ibm_db version 3.2.2. I am using the connection pool and suppose a database error occurred in one of the connections of the pool. In such a case in all the other successful queries we are getting outparams as null in that connection(where error had occurred previously.

this is how i am creating the connection pool:

const pool = new Pool({ maxPoolSize: 2 })
const cn = `DATABASE=${connection.DATABASE};HOSTNAME=${connection.HOSTNAME};PORT=${connection.PORT};PROTOCOL=${connection.PROTOCOL};UID=${connection.UID};PWD=${connection.PWD}`;
console.log("Init", new Date())
pool.init(2, cn)
console.log("Init complete", new Date())
resolve(pool)
return

This is how i am calling execute

        const cn = `DATABASE=${this.#db2_connectionStr.DATABASE};HOSTNAME=${this.#db2_connectionStr.HOSTNAME};PORT=${this.#db2_connectionStr.PORT};PROTOCOL=${this.#db2_connectionStr.PROTOCOL};UID=${this.#db2_connectionStr.UID};PWD=${this.#db2_connectionStr.PWD}`
        const connection = await this.pool.open(cn)
        const statement = await connection.prepare(query)
        return new Promise((resolve, reject) => {
            statement.execute(params, (err, result: any, outparams: any[]) => { //outparams is null here when second successful call when first one had errored
                if (err) {
                    this.logger.error("Failed in execute", err)
                    connection.closeSync()
                    reject(err)

                }
                else {
                    const data = []
                    data.push(result?.fetchAllSync())
                    while (result.moreResultsSync())
                        data.push(result?.fetchAllSync())
                    resolve({ data, outparams })
                    connection.closeSync()

                }

            })
        })

[anilkasalanati]

We have also faced the same issue. Any errors in executing sp corrupts the connection in the pool. The connection.close() doesn’t really restore the connection to a clean stats. Out params can never be fetched post a single error.

[bimalkjha]

@girish-jha I am trying to reproduce the problem. Meanwhile if you can share a complete test program to reproduce the problem, it would really help me to make progress. Thanks.

[bimalkjha]

@girish-jha For both success and error case, just before connection.closeSync(), add statement.closeSync() too and verify. Let me know if you see any difference. Thanks.

[girish-jha]

Hi @bimalkjha, i have created a PR that resolves this issue for us. Could you please review

https://github.com/ibmdb/node-ibm_db/pull/953

[bimalkjha]

ibm_db@3.2.3 should fix this issue. thanks.