Statement with bindParam equal does not work correctly

[jhonnattan123]

• Node.js version: 14.6.0
• idb-connector version: idb-connector@1.2.9
• idb-pconnector version: idb-pconnector@1.0.8
• IBM i version: 3 7

Describe the bug the equal operator is supposed to work the same with both a direct query and a prepared query, however they are working differently

To Reproduce

const { Connection, Statement, IN, CHAR } = require('idb-pconnector');

const sql = 'SELECT * FROM QIWS.QCUSTCDT WHERE CITY = ?';
let bindParams = [ [ 'Dallas2', IN, CHAR  ] ]

let connection = new Connection({ url: '*LOCAL' });
let statement = new Statement(connection);

await statement.bindParam(bindParams);
await statement.execute(sql);
let resultSet = await statement.fetchAll();

Expected behavior It is assumed that no record in the test table matches the city Dallas2, since Dallas is actually written without the number 2. but the system still finds Dallas being Dallas ! = Dallas2

Screenshots with bindParam statement

normal query

Additional context It can be solved by modifying the sentence but it is a waste of resources. ej SELECT * FROM QIWS.QCUSTCDT WHERE CITY = %?%

[abmusse]

This behavior also occurs when using idb-connector directly:

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

    const sql = `SELECT CITY FROM QIWS.QCUSTCDT WHERE CITY = 'Dallas2'`;
    const connection = new dbconn();
    connection.conn('*LOCAL');
    const statement = new dbstmt(connection);

    statement.exec(sql, (result) => {
      console.log(`Result is : ${JSON.stringify(result)}`);
      statement.close();
      connection.disconn();
      connection.close();
    });

Output:

Result is : []

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

    const sql = 'SELECT CITY FROM QIWS.QCUSTCDT WHERE CITY = ?';
    const params = [ [ 'Dallas2', IN, CHAR  ] ];
    const connection = new dbconn();
    connection.conn('*LOCAL');
    const statement = new dbstmt(connection);

    statement.prepare(sql, (error) => {
      statement.bindParam(params, (error) => {
      statement.execute(() => {
        statement.fetchAll((result) => {
          console.log(`Result is : ${JSON.stringify(result)}`);
          statement.close();
          connection.disconn();
          connection.close();
        });
      });
     });
   });

Output:

Result is : [{"CITY":"Dallas"},{"CITY":"Dallas"}]

Maybe this behavior difference caused by using SQLPrepare and SQLExecute rather than SQLExecDirect

I'm going to transfer this issue up to idb-connector issue to help the resolve the issue.

[abmusse]

CC @dmabupt

Any idea why the the result set differs when using a parameter marks and execute vs just using exec with the where clause?

[ThePrez]

Since the CITY column is only 6 characters long, the parameterized condition is presumably getting truncated from "Dallas2" to "Dallas".

[ThePrez]

CC @forstie, any insight/thoughts on what the proper behavior is here? Should we instead be throwing truncation warnings/errors?

[jhonnattan123]

Since the column is only 6 characters long, the parameterized condition is presumably getting truncated from "Dallas2" to "Dallas".CITY

makes sense but the truth is unexpected. In my opinion, they should behave in the same way as the simple query, as it can lead to unexpected or unwanted results when looking for precision and validation. I think in this case I should not return anything or issue a warning.

that point has to be highlighted more in the documentation

[ThePrez]

makes sense but the truth is unexpected. In my opinion, they should behave in the same way as the simple query, as it can lead to unexpected or unwanted results when looking for precision and validation. I think in this case I should not return anything or issue a warning.

I agree

[forstie]

This looks to be working as expected, although I do have to question only having 6 characters for a city name.

Retrieval assignment: The length of a string assigned to a variable (but not a parameter or transition variable) can be greater than the length attribute of the variable. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the string is truncated on the right by the necessary number of characters. When this occurs, an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or the value 'W' is assigned to the SQLWARN1 field of the SQLCA).

SQLBindParam has the concept of SQL_SUCCESS_WITH_INFO. I'm not sure if that or the aforementioned warning would be surfacable here.

[jhonnattan123]

This looks to be working as expected, although I do have to question only having 6 characters for a city name.

Retrieval assignment: The length of a string assigned to a variable (but not a parameter or transition variable) can be greater than the length attribute of the variable. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the string is truncated on the right by the necessary number of characters. When this occurs, an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or the value 'W' is assigned to the SQLWARN1 field of the SQLCA).

SQLBindParam has the concept of SQL_SUCCESS_WITH_INFO. I'm not sure if that or the aforementioned warning would be surfacable here.

in a few hours I will have access to an iseries, I will confirm if the response to SQL_SUCCESS_WITH_INFO arrives. in case it reaches SQL_SUCCESS_WITH_INFO: What do you think of creating a function or setting in the library that allows you to ignore that response and return as if nothing has been found? (although results were actually found) would be a waste of memory and perhaps a vulnerability. Is there a possibility to modify this behavior from the query?

[dmabupt]

This looks to be working as expected, although I do have to question only having 6 characters for a city name. Retrieval assignment: The length of a string assigned to a variable (but not a parameter or transition variable) can be greater than the length attribute of the variable. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the string is truncated on the right by the necessary number of characters. When this occurs, an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or the value 'W' is assigned to the SQLWARN1 field of the SQLCA). SQLBindParam has the concept of SQL_SUCCESS_WITH_INFO. I'm not sure if that or the aforementioned warning would be surfacable here.

in a few hours I will have access to an iseries, I will confirm if the response to SQL_SUCCESS_WITH_INFO arrives. in case it reaches SQL_SUCCESS_WITH_INFO: What do you think of creating a function or setting in the library that allows you to ignore that response and return as if nothing has been found? (although results were actually found) would be a waste of memory and perhaps a vulnerability. Is there a possibility to modify this behavior from the query?

Hello @jhonnattan123, have you got the SQL_SUCCESS_WITH_INFO returned code? I can not get that when debugging the code.

If this is not an idb-connector issue. I will assume that it is working as expected and add corresponding test cases.

[jhonnattan123]

This looks to be working as expected, although I do have to question only having 6 characters for a city name. Retrieval assignment: The length of a string assigned to a variable (but not a parameter or transition variable) can be greater than the length attribute of the variable. When a string is assigned to a variable and the string is longer than the length attribute of the variable, the string is truncated on the right by the necessary number of characters. When this occurs, an SQLSTATE of '01004' is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or the value 'W' is assigned to the SQLWARN1 field of the SQLCA). SQLBindParam has the concept of SQL_SUCCESS_WITH_INFO. I'm not sure if that or the aforementioned warning would be surfacable here.

in a few hours I will have access to an iseries, I will confirm if the response to SQL_SUCCESS_WITH_INFO arrives. in case it reaches SQL_SUCCESS_WITH_INFO: What do you think of creating a function or setting in the library that allows you to ignore that response and return as if nothing has been found? (although results were actually found) would be a waste of memory and perhaps a vulnerability. Is there a possibility to modify this behavior from the query?

Hello @jhonnattan123, have you got the SQL_SUCCESS_WITH_INFO returned code? I can not get that when debugging the code.

If this is not an idb-connector issue. I will assume that it is working as expected and add corresponding test cases.

Does that mean that with the last commit now if they should work like the normal query?._ I had problems that day, to put the console log, and I was waiting this week (by issues of work permits) to put the console.log

[dmabupt]

Does that mean that with the last commit now if they should work like the normal query?._ I had problems that day, to put the console log, and I was waiting this week (by issues of work permits) to put the console.log

No, the latest version does not change current behavior. As far as I can see, the truncation does not happen in the idb-connector level.

[jhonnattan123]

Does that mean that with the last commit now if they should work like the normal query?._ I had problems that day, to put the console log, and I was waiting this week (by issues of work permits) to put the console.log

No, the latest version does not change current behavior. As far as I can see, the truncation does not happen in the idb-connector level.

Okay, I understand. Thanks for the help. we will have to control the data in the backend and front