statement.stmtError does not return a proper error message

[jasonclake]

In the example below: I expected stmtError() to return the same string as the error from the statement.exec() function. But the error was no where to be found.

I know it seems redundant but we have a bigger agenda What we actually want is to retrieve an SQLError Object/s without having to parse the string/s. This was just preliminary testing.

Code Example:

console.log("Node: ", process.version);
console.log('idb:', require("idb-connector/package.json").version);

//const { dbconn, dbstmt, SQL_HANDLE_STMT, SQL_HANDLE_DBC,SQL_HANDLE_ENV } = require('./lib/db2a');
const { dbconn, dbstmt, SQL_HANDLE_STMT, SQL_HANDLE_DBC,SQL_HANDLE_ENV } = require('idb-connector');

function runsqlp(sql, conn) {
    return new Promise((resolve,reject) => {
        let statement = new dbstmt(conn);
        statement.exec(sql, async (result, error) => {
            if (error) {
                console.log("Error string from exec():", error);
                let stmtErrorString = await stmtErrorp(statement);
                //First of all what is happening to the error?
                console.log("stmtError() is empty... Where did the error go?:", stmtErrorString);
                // Could stmtError or a new function sqlErrors(maxCountofErrorsToGet?)
                // return an array of objects instead of a string where if count is not passed it gets all
                let stmtError = {SQLSTATE: "42703", SQLCODE: -206, MSG: "Column or global variable NOCOLUMN not found."};
                statement.close();
                reject(stmtError);
                return;
            }
            statement.close();
            resolve(result);
        });
    });
}

function stmtErrorp(statement) {
    return new Promise((resolve,reject) => {
       try {
           statement.stmtError(SQL_HANDLE_STMT,1, (errormsg) => {
               resolve(errormsg);
            });
        } catch (error) {
            resolve(error);
        }
    });
}

async function test() {
    const connection = new dbconn();
    let result = {};

    connection.debug(true);
    connection.conn("*LOCAL");

//failing example
    try {
        result = await runsqlp(
            `SELECT nocolumn as field1 from sysibm.sysdummy1`,connection);
            console.log(`Result: \n \`\`\` \n ${JSON.stringify(result)} \n\`\`\` \n`);

    } catch (err) {
        console.log(`Error: \n \`\`\` \n ${JSON.stringify(err)} \n\`\`\` \n`);
    }

}

test();

Output:

Node:  v10.15.3
idb: 1.2.0
SQLConnect(0): conn obj [1803454f0] handler [2]
SQLExecDirect(-1): SELECT nocolumn as field1 from sysibm.sysdummy1

 **** ERROR *****
SQLSTATE: 42703
Native Error Code: -206
Column or global variable NOCOLUMN not found.
Error string from exec(): [Error: SQLSTATE=42703 SQLCODE=-206 Column or global variable NOCOLUMN not found.]
stmtError() is empty... Where did the error go?: SQLSTATE=ððððð SQLCODE=0 :
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)

[dmabupt]

@jasonclake I found something interesting -- you can reproduce the error only when you enable debug(true)

[jasonclake]

Makes sense if SQLError() is destructive -- Maybe just a doc update to idb-connector stmtError calling out the debug issue?

My fault -I read into the SQLError CLI doc that you could call SQLError multiple times since it says "before any function other than". Whereas SQLGetDiagRec() specifically says calling it clears the diagnostic info.

https://github.com/IBM/nodejs-idb-connector/blob/a580efce5eff667566747014f4bf3e9debe216fe/src/db2ia/dbstmt.cc#L2323

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfnerror.htm If diagnostic information generated by one DB2 for i CLI function is not retrieved before a function other than SQLError() is called with the same handle, the information for the previous function call is lost.

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfndrec.htm Diagnostic information stored under a given handle is cleared when a call is made to SQLGetDiagRec() with that handle, or when another DB2 for i CLI function call is made with that handle.

[dmabupt]

idb-connector v1.2.1 has updated the doc to indicate that dbstmt.stmtError() may not work properly when using with dbconn.debug(true). Close this issue now.