Error: SQLSTATE=58004 SQLCODE=-901 using function in prepared SQL queries

[Phenry-Gaia]

Hi,

The executeSync() function appears to fail when the bind parameter is enclosed to a SQL function (UPPER() in our case).

The documentation report a deprecated function bindParametersSync() remplaced by bindParameterSync() -> we kept bindParametersSync() as bindParameterSync() isn't recognise in the version we use.

Node.js version: v20.5.1
idb-connector version: v1.2.19
IBM i version: 4 7

import pkg from 'idb-connector';
const { dbconn: idbconn, dbstmt: dbstmt } = pkg;

// Original query
const sSql = "SELECT TRIM(TEXT_DESCRIPTION) AS NAME FROM QSYS2.USER_INFO SYS WHERE AUTHORIZATION_NAME = UPPER(?)";
// Alternative
//const sSql = "SELECT TRIM(TEXT_DESCRIPTION) AS NAME FROM QSYS2.USER_INFO SYS WHERE AUTHORIZATION_NAME = ?"

const connection = new idbconn();
connection.conn('*LOCAL');
const stmt = new dbstmt(connection);

// Username
const login = "po"; // your username

stmt.prepareSync(sSql);
// Original
stmt.bindParametersSync([login]);
// Alternative
//stmt.bindParametersSync([login.toUpperCase()]);
stmt.executeSync();

const row = stmt.fetchSync();

console.log(`Row:${JSON.stringify(row)}\n`);
console.log(row["NAME"]);

stmt.close();
connection.disconn();
connection.close();

Output:
stmt.executeSync();
^
Error: SQLSTATE=58004 SQLCODE=-901 Erreur système SQL.

Describe the bug
The executeSync() function appears to fail when the bind parameter is enclosed to a SQL function (UPPER() in our case).

To Reproduce
Steps to reproduce the behavior:

1. copy paste provided code, adapt the login variable
2. run in a node idb-connector environment
3. uncomment/comment Alternative/Original scenario to see success/fail output

Thank you

[abmusse]

Thanks for the detailed bug report!

I will start investigating this one when I have more cycles next week.

[GautierFR]

Hi @Phenry-Gaia,
I don't know if it helps, but using odbc module, this scenario works correctly.

Code sample with node v20.5.1 :

const odbc = require('odbc');

odbc.connect('DSN=*LOCAL', (error, connection) => {
  if (error) { throw error; }
  connection.query('SELECT * FROM QIWS.QCUSTCDT WHERE STATE = UPPER( ? )', ['NY'] ,(error, result) => {
    if (error) { throw error; }
    console.log(result);
  })
});

[abmusse]

Hello @Phenry-Gaia

I took a look at the issue and confirmed that the bug occurs on my end.

I used wrksplf to get access to the job log and found a lot more info there:

 CPF4204    Escape                  50   07/03/24  16:24:12.859025  QQQQUERY     QSYS        *STMT    QQQQUERY    QSYS        *STMT
                                      From user . . . . . . . . . :   AMUSSE
                                      From module . . . . . . . . :   QQQQUERY
                                      From procedure  . . . . . . :   QQQQUERY
                                      Statement . . . . . . . . . :   35099
                                      To module . . . . . . . . . :   QQQQUERY
                                      To procedure  . . . . . . . :   QQQQUERY
                                      Statement . . . . . . . . . :   35099
                                      Message . . . . :   Internal failure occurred in query processor.
                                      Cause . . . . . :   A system failure has occurred in the query processor
                                        program.  The query definition template number is 1. Recovery  . . . :   See
                                        the low-level messages.  Correct any errors and try your request again.  If
                                        the problem continues, report the problem (ANZPRB command).

SQL0901    Diagnostic              50   07/03/24  16:24:13.392013  QSQRUN3      QSYS        *STMT    QSQRUN3     QSYS        *STMT
                                      From user . . . . . . . . . :   AMUSSE
                                      From module . . . . . . . . :   QSQOPEN
                                      From procedure  . . . . . . :   CLEANUP
                                      Statement . . . . . . . . . :   28317
                                      To module . . . . . . . . . :   QSQOPEN
                                      To procedure  . . . . . . . :   CLEANUP
                                      Statement . . . . . . . . . :   28317
                                      Message . . . . :   SQL system error.
                                      Cause . . . . . :   An SQL system error has occurred.  The current SQL
                                        statement cannot be completed successfully.  The error will not prevent
                                        other SQL statements from being processed. Previous messages may indicate

Looks like there is an issue processing the query internally and we will need to get the database team involved to resolve the issue.

Also you should have similar spool files with the error messages above on your system. Can you kindly confirm?

Please open an IBM Support ticket to the Database team so we can further resolve the issue.

ref: https://www.ibm.com/mysupport/s/?language=en_US

In the meantime, you can use the alternative method you have in the original issue to workaround the issue.

[staplJason]

Casting it to a string seems to make it work for me?
'SELECT TRIM(TEXT_DESCRIPTION) AS NAME FROM QSYS2.USER_INFO SYS WHERE AUTHORIZATION_NAME = UPPER(CAST(? AS CHAR(10)))';