Error SQLSTATE=02000 SQLCODE=100

[abamara]

Hello

When calling execute() with a sql that do some update, if there is no update to do, i have this Error: SQLSTATE=02000 SQLCODE=100.

For me there is an issue, this should return a message or a warning and certainly not an error.

Regards

[abmusse]

Hello :wave:

Can you provide a simple code snippet that reproduces the issue?

[abamara]

Hello

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

const sSql = 'UPDATE MY.TABLE SET COL0=0 WHERE COL1 is not null';
const connection = new dbconn();
connection.conn('*LOCAL');
const statement = new dbstmt(connection);

statement.prepare(sSql, () => {
  statement.execute((result, error) => {
    console.log(error);
    statement.close();
  });
});

[abamara]

After looking at source code of the Execute function, i noticed that if sqlReturnCode is different than SQL_SUCCESS or SQL_SUCCESS_WITH_INFO an error is returned.

 void Execute()
  {
    SQLRETURN sqlReturnCode;

    //Doc https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cli/rzadpfnexecd.htm
    sqlReturnCode = SQLExecDirect(dbStatementObject->stmth, //SQLHSTMT hstmt -Statement handle
                                  sqlStatement,             //SQLCHAR* szSQLStr -SQL statement string
                                  SQL_NTS);                 //SQLINTEGER cbSQlStr -Length of szSQLStr
    DEBUG(dbStatementObject, "SQLExecDirect(%d): %s\n", sqlReturnCode, sqlStatement);
    if (sqlReturnCode == SQL_SUCCESS_WITH_INFO)
    {
      sqlError errObj = returnErrObj(SQL_HANDLE_STMT, dbStatementObject->stmth);
      DEBUG(dbStatementObject, "SQLExecDirect SUCCESS_WITH_INFO (%d) %s\n", errObj.sqlCode, errObj.sqlState);
      if (!strcmp(errObj.sqlState, "0100C") && errObj.sqlCode == 466)
      {
        // Stored Procedures with Result Sets
        dbStatementObject->resultSetAvailable = true;
      }
    }
    else if (sqlReturnCode != SQL_SUCCESS)
    {
      std::string errorMessage = returnErrMsg(SQL_HANDLE_STMT, dbStatementObject->stmth);
      SetError(errorMessage);
      return;
    }

So I don't know why the code assume that SQL_NO_DATA_FOUND is an error.

[abmusse]

I agree setting an error for SQL_NO_DATA_FOUND is unexpected.

Here is a snippet from SQLExecDirect docs

....

Return codes
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE
SQL_NO_DATA_FOUND

SQL_NO_DATA_FOUND is returned if the SQL statement is a Searched 
UPDATE or Searched DELETE and no rows satisfy the search condition.

....

We should patch this and return null for the resultSet instead.

https://github.com/IBM/nodejs-idb-connector/tree/master/docs#dbstmtexecsql-callback

...
callback(resultSet, error): function to process after exec is complete.

resultSet: array of objects each object represents a row of data.
If an error occurred or there is no resultSet it is set to null.
...