Error: SQLSTATE=PAERR SQLCODE=8014

[abamara]

When calling fetchAll() I have this Error: SQLSTATE=PAERR SQLCODE=8014 There is no result set to be queried. Please execute a SQL command first.

Normaly an asynchrone function, should not throw an exception. The error should be returned as expected, through the callback error parameter.

[jasonclake]

@abamara I believe the problem is you aren't getting to the async part of the function fetchAll(). Your statement object is not in the proper state to start any async work. Can you provide a code example? With output? Also could you provide node and idb-connector versions?

For example: This fails because it never ran the query that was prepared

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

const { dbstmt, dbconn } = require('idb-connector');

const connection = new dbconn();
connection.debug(true);
connection.conn('*LOCAL');

const statement = new dbstmt(connection);
statement.prepare(`
        WITH testTable (id, name) AS(
            SELECT 1, 'Alice' FROM sysibm.sysdummy1
            UNION ALL 
            SELECT 2, 'Bob' FROM sysibm.sysdummy1
            )
        SELECT * FROM testTable;`
    , (error) => {
        if (error) {
            throw error;
        }
        //commented out to produce the error
        //   statement.execute((error) => {
        //     if (error) {
        //       throw error;
        //     }
        statement.fetchAll((result, error) => {
            if (error) {
                throw error;
            }
            console.log(`Result Set:${JSON.stringify(result)}\n`);
            statement.close();
            connection.disconn();
            connection.close();
        });
    });
// });

output:

Node:  v10.15.3
idb: 1.2.0
SQLConnect(0): conn obj [1803454f0] handler [2]
Prepare().
SQLPrepare(0):
    WITH testTable as(
        SELECT 1 as id, 'Alice' as name from sysibm.sysdummy1
        UNION All
        SELECT 2 as id, 'Bob' as name from sysibm.sysdummy1)
    SELECT * from testTable;

FetchAllAsync().
/db2/nodejs-idb-connector/btest.js:27
        statement.fetchAll((result, error) => {
                  ^

Error: SQLSTATE=PAERR SQLCODE=8014 There is no result set to be queried. Please execute a SQL command first.
    at statement.prepare (/db2/nodejs-idb-connector/btest.js:27:19)

[abamara]

I have this issue with idb 1.2 and Node 10.

I see that you tried to reproduce the issue with commenting execute part. But you can easily reproduce the issue by replacing the select query by an update query. Or by a query that return no result.

My issue was with fetchAll() that return errors through a callback and also throw some exceptions. And also, the error returned is not a standard DB2 error, it's a bit confusing.

[jasonclake]

What are you expecting to get back with fetchAll() after executing an update? The exception is thrown when the program is checking the input parameters...

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

I tried an empty set query and I do not get an exception...

Node:  v10.15.3
idb: 1.2.0
SQLConnect(0): conn obj [1803454f0] handler [2]
Prepare().
SQLPrepare(0):
        WITH testTable (id, name) AS(
            SELECT 1, 'Alice' FROM sysibm.sysdummy1
            UNION ALL
            SELECT 2, 'Bob' FROM sysibm.sysdummy1
            )
        SELECT * FROM testTable where id = 3;
Execute().
SQLExecuteAsync(0):
SQLNUMRESULTSCOLS(0) Column Count = 2
SQLDescribeCol(0)       index[0]        sqlType[4]      colScale[0]     colPrecise[4]
SQLDescribeCol(0)       index[1]        sqlType[12]     colScale[0]     colPrecise[5]
FetchAllAsync().
Result Set:[]

SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)
SQLDisconnect: conn obj [1803454f0] handler [2]
SQLFreeConnect: conn obj [1803454f0] handler [2]
SQLFreeConnect[0]

[abamara]

I am still getting the exception.

Why i am calling fetchall() ? It's simple, i made a function(sql) wich take an sql an then execute it. My question was not how to modify the function to avoid crashing. It's why it's crash instead of returning an error in the callback parameter.

This is my sample code

`function sql(dbconn, sqltxt, sqlparam, callback) { var wDbconn = dbconn;

const wStmt = new db.dbstmt(wDbconn);

function onResult(outputParams, error) {

    wStmt.fetchAll((result, error) => {
            wStmt.close();
            delete wStmt;

            callback(wDbconn, result, error);
        });
}

function stmtExecute(error)
{
    if (error)
        callback(wDbconn, null, error);
    else
        wStmt.execute(onResult);
}

function stmtBindParam(error)
{
    if (error)
        callback(wDbconn, null, error);
    else if ( sqlparam != null)
        wStmt.bindParam(sqlparam, stmtExecute);
    else
        stmtExecute(error)
}

wStmt.prepare(sqltxt, stmtBindParam);

}`

[jasonclake]

I do agree consistency is easier to understand. So it would be more consistent to return the parameter check error message in the callback.

But that would set up another issue... I am wondering if returning an array of rows as the result is too limiting. Although parameter checks should be errors, running a query that returns "no data" isn't always an error, as in your update statement case.

What if the result looked something like:

result = {rc: 0,
data: [],
getDiagRec(): []
}

rc being one of the following

/* RETCODE values             */
#define  SQL_SUCCESS             0
#define  SQL_SUCCESS_WITH_INFO   1
#define  SQL_NEED_DATA           99
#define  SQL_NO_DATA             100
#define  SQL_STILL_EXECUTING     2
#define  SQL_ERROR               -1
#define  SQL_INVALID_HANDLE      -2

Then if the rc was not enough info you could call getDiagRec() for an array of SQL error/warning objects. "getDiagRec()" or something like it could be a separate function as part of the statement as I suggest in issue #81

Whereas the error of the callback would be reserved for exceptions and/or SQL errors like SQL_ERROR, SQL_INVALID_HANDLE etc still returning error strings.

And make the rule that async functions never throw exceptions as you suggest. Meaning that all parameter validation for async functions would move to the async worker execute function if that is possible.

[github-actions[bot]]

:wave: Hi! This issue has been marked stale due to inactivity. If no further activity occurs, it will automatically be closed.