Bug: Issue with bindingsparameters

[huineng]

node v18.15.0 ibm_db v3.1.0

I'm using the ibm_db for running queries without any problem.. in a lot of cases i'm using the bindings parameters

const data = await conn.query(_sql, bindings);

the code above never caused any problems, until i switched last week testing a new db on cloud instance

DB2 v11.5.7.0

Nailing down the issue is that i could not longer get a binding value to work with a CHAR datatype it works with other fields though that are VARCHAR for info

eg I have a table with a column DSWC CHAR(5) filled up with NUll or 'DSWC' as a value

when i run the query wit input

 { ParamType: 'INPUT', SQLType: 'CHAR', Data: 'DSWC' },

it doesn't work, (it works in dbeaver which uses java)

when on the other hand i match the length of the column and add an extra blank it works

 { ParamType: 'INPUT', SQLType: 'CHAR', Data: 'DSWC ' },  <-  added an extra blank

What could be the reason for this strange situation, any idea ?

also doing a simple sql adding a lot of blanks it still work

WHERE DSWC = 'DSWC          '

thanks

[bimalkjha]

@huineng I have executed below test file against Db2 V11.5.7.0 server and I am unable to reproduce the problem.

let ibmdb = require('ibm_db');
let connStr = "DATABASE=<dbname>;HOSTNAME=<myhost>;UID=db2user;PWD=password;PORT=<dbport>;PROTOCOL=TCPIP";
let conn = null;

main();
async function main() {
  conn = await ibmdb.open(connStr );
  await testCharInsert();
  await conn.close();
}

async function testCharInsert() {
    await conn.query("create table test1(c1 int, c2 char(5))");
    await conn.query("insert into test1 values (1, 'abcde')");
    await conn.query("insert into test1 values (2, 'abcd')");
    await conn.query("insert into test1 values (?, ?)", [3, 'ABCDE']);
    await conn.query("insert into test1 values (?, ?)", [4, 'ABCD']);
    await conn.query("insert into test1 values (?, ?)", [5, { ParamType: 'INPUT', SQLType: 'CHAR', Data: 'DSWC' }]);
    console.log(conn.querySync("select * from test1"));
    await conn.query("drop table test1");
}

C:\nodework\master\ibm_db\test>node test-char-insert.js
[
  { C1: 1, C2: 'abcde' },
  { C1: 2, C2: 'abcd ' },
  { C1: 3, C2: 'ABCDE' },
  { C1: 4, C2: 'ABCD ' },
  { C1: 5, C2: 'DSWC ' }
]

I have updated a test file on github to test above case. Please try to reproduce the problem using above test program and share here. If you have installed ibm_db on non-windows OS, then copy your test program under ibm_db\test as test.js and run below commands:

cd ....\ibm_db\test
.\trace test.js

zip all the generated 1.* files along with output of test program and share here.

From the problem description, it looks something related to the STRICT MODE of SQL Execution controlled by Db2 server. I am not sure if Db2 has feature of strict mode and it is enabled on your cloud instance or not. I checked on google and found one link describing similar behavior for mysql server: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/char.html

By looking on the trace files, I can figure out the error is coming from server or it is due to some bug in the client code. Also, add Length: 5 in the param object as { ParamType: 'INPUT', SQLType: 'CHAR', Data: 'DSWC', Length: 5 } and verify the behavior. Length: 5 in the param object will tell driver that this column has size of 5 bytes. Thanks.

[huineng]

Hi Bimal , thanks your test were incomplete so i added 2 lines, which in fact prove my issue

console.log(conn.querySync("select * from test1"));
console.log('normal:', conn.querySync("select * from test1 where C2 = ?", ['DSWC']));
console.log('normal_l5:', conn.querySync("select * from test1 where C2 = ?", [{ ParamType: 'INPUT', SQLType: 'CHAR', Data: 'DSWC', Length: 5 }]));
console.log('white_space_added:', conn.querySync("select * from test1 where C2 = ?", ['DSWC ']));
console.log('white_space_added_l5:', conn.querySync("select * from test1 where C2 = ?", [{ ParamType: 'INPUT', SQLType: 'CHAR', Data: 'DSWC ', Length: 5 }]));
await conn.query("drop table test1");

you see the issue at the end

Result from testColAlias =  [ { COLINT: 1, COLTEXT: 'some test' } ]
Result from testDupColNames=  [ { NAME: 'text 2' } ]
node-ibm_db logs enabled.
5.278 ::  odbc.js:querySync() =>  select 'abc' as first, 'dcd' as second, 'efg', 'pqr', 'xyz' as last from SYSIBM.SYSDUMMY1
Result from testColumnAlias=  [
  { '3': 'efg', '4': 'pqr', FIRST: 'abc', SECOND: 'dcd', LAST: 'xyz' }
]
node-ibm_db logs disabled.
[
  { C1: 1, C2: 'abcde' },
  { C1: 2, C2: 'abcd ' },
  { C1: 3, C2: 'ABCDE' },
  { C1: 4, C2: 'ABCD ' },
  { C1: 5, C2: 'DSWC ' }
]
6.517 ::  odbc.js:querySync() params =  [ 'DSWC' ]
normal: []
6.741 ::  odbc.js:querySync() params =  [ [ 1, 1, 1, 'DSWC', 5 ] ]
normal_l5: []
6.951 ::  odbc.js:querySync() params =  [ 'DSWC ' ]
white_space_added: [ { C1: 5, C2: 'DSWC ' } ]
7.168 ::  odbc.js:querySync() params =  [ [ 1, 1, 1, 'DSWC ', 5 ] ]
white_space_added_l5: [ { C1: 5, C2: 'DSWC ' } ]
All tests executed.

i have the zipped files , but they might contain userid and password, i will sent them to you via slack

thanks

[huineng]

I assume it was caused by setting the oracle compatibility when provisioning the db I solved it by setting DeferredPrepare=0 in the connection string and for the moment our problems are solved

thanks for your support