byte change when inserting hex byte array into varchar(16) for bit data column

Hi,

The rid_bit() function for optimistic locking (meant for highly-concurrent web apps) returns a varchar(16) for bit data. I query the value using nodejs+ibm_db and it matches the output from (select * from ...) CLP.

But a byte gets altered when I store the value (as SQL_BINARY, SQL_C_BINARY) into a "varchar(16) for bit data" column using nodejs+ibm_db.

output of my testcase:

str: 610001000000000000003DB337000000 put: <Buffer 61 00 01 00 00 00 00 00 00 00 3d b3 37 00 00 00> got: 610001000000000000003DFD37000000

For simplicity I reduced testcase (code is below) to insert a literal value (rid_bit value was copy-pasted from db2) into the vc(16) fbd column.

I have included CLI trace: p84548t140337255630592.txt p84548t140337264023296.txt p84548t140337272416000.txt p84548t140337524250560.txt

Please let me know how to fix, thanks!

$ db2level DB21085I This instance or install (instance name, where applicable: "homer") uses "64" bits and DB2 code release "SQL11057" with level identifier "0608010F". Informational tokens are "DB2 v11.5.7.0", "s2111221000", "DYN2111221000AMD64", and Fix Pack "0". Product is installed at "/home/homer/sqllib".

[ Process: 84424, Thread: 139697233159936 ] [ Date & Time: 2023-12-02 03.23.01.798434 ] [ Product: QDB2/LINUXX8664 DB2 v11.5.7.0 ] [ Level Identifier: 0608010F ] [ CLI Driver Version: 10.01.0000 ] [ Informational Tokens: "DB2 v11.5.7.0","s2111221000","DYN2111221000AMD64","Fixpack 0" ] [ Install Path: /home/homer/sqllib ] [ db2cli.ini Location: /home/homer/sqllib/cfg/db2cli.ini ] [ db2dsdriver.cfg Location: /home/homer/sqllib/cfg/db2dsdriver.cfg ] [ CLI Driver Type: IBM DB2 Enterprise Server Edition ] [ Hostname: homer-VirtualBox ]

$ uname -a Linux homer-VirtualBox 5.15.0-88-generic #98~20.04.1-Ubuntu SMP Mon Oct 9 16:43:45 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

$ node -v v18.17.1

$ locale LANG=en_CA.UTF-8 LANGUAGE=en_CA:en LC_CTYPE="en_CA.UTF-8" LC_NUMERIC="en_CA.UTF-8" LC_TIME="en_CA.UTF-8" LC_COLLATE="en_CA.UTF-8" LC_MONETARY="en_CA.UTF-8" LC_MESSAGES="en_CA.UTF-8" LC_PAPER="en_CA.UTF-8" LC_NAME="en_CA.UTF-8" LC_ADDRESS="en_CA.UTF-8" LC_TELEPHONE="en_CA.UTF-8" LC_MEASUREMENT="en_CA.UTF-8" LC_IDENTIFICATION="en_CA.UTF-8" LC_ALL=

const ibmdb = require('ibm_db');

// 1. CHANGE THESE const connStr = 'DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=dba;PWD=pwd';

// 2. SETUP // db2 "create table test.t1(ts timestamp default, rid varchar(16) for bit data)"

// 3. QUERY ROW // db2 "select * from test.t1 order by ts"

ibmdb.open(connStr, function(err,conn) { conn.beginTransaction(function (err) { if (err) { //could not begin a transaction for some reason. console.log(err); return conn.closeSync(); }

    var rid = '610001000000000000003DB337000000';
    console.log('str:', rid);

    var hex_rid = Buffer.from(rid, 'hex');
    console.log('put:', hex_rid);

    var param = { Data: hex_rid, SQLType: 'SQL_BINARY', CType: 'SQL_C_BINARY', ParamType: 'SQL_PARAM_INPUT', Length: hex_rid.length };

var result = conn.querySync("insert into test.t1 (rid) values (?)", [param]);

var got_rid = conn.querySync("select rid from test.t1 order by ts desc fetch first 1 rows only");
    console.log('got:', got_rid[0].RID);

conn.commitTransaction(function (err) {
  if (err) {
    //error during commit
    console.log(err);
    return conn.closeSync();
  }

 //Close the connection
 conn.closeSync();
});

}); });

@CoderOnFire51 You have neither shared the version of ibm_db driver nor shared the output of npm ls ibm_db command as requested in new issue template. Your problem is due to use of old version of ibm_db driver. Please upgrade to the latest version of ibm_db which has fix for this issue.

In shared trace file p84548t140337524250560.txt, we can see below entries for insert statement:

[2023-12-02 03.45.35.990655] SQLBindParameter( hStmt=1:1, iPar=1, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR,
 fSQLType=SQL_VARCHAR, cbColDef=19, ibScale=0, rgbValue=&00007fa2ddc07c90, cbValueMax=19, 
pcbValue=&00007fa2ddc0c2cc )
[2023-12-02 03.45.35.990728]     ---> Time elapsed - +1.600000E-005 seconds
[2023-12-02 03.45.35.990766] SQLExecute( hStmt=1:1 )
[2023-12-02 03.45.35.990783]     ---> Time elapsed - +1.300000E-005 seconds
[2023-12-02 03.45.35.990796] ( Package="SYSSH200          ", Section=4 )
[2023-12-02 03.45.35.990820] 
[2023-12-02 03.45.35.990866] ( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="a" - 
x'610001000000000000003DEFBFBD37000000', pcbValue=18, piIndicatorPtr=18 )
[2023-12-02 03.45.35.993716] ( Re-executing since original execute got SQLCODE=-302 )
[2023-12-02 03.45.35.993747] 
[2023-12-02 03.45.35.993763] ( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="a" - 
x'610001000000000000003DEFBFBD37000000', pcbValue=18, piIndicatorPtr=18 )
[2023-12-02 03.45.35.993817] 
[2023-12-02 03.45.35.994050] ( Unretrieved error message="[IBM][CLI Driver][DB2/LINUXX8664] SQL0302N  The value of a
 host variable in the EXECUTE or OPEN statement is out of range for its corresponding use.  SQLSTATE=22001
" )
[2023-12-02 03.45.35.994082] SQLExecute( )
[2023-12-02 03.45.35.994091]     <--- SQL_ERROR   Time elapsed - +3.325000E-003 seconds
[2023-12-02 03.45.35.994329] SQLGetDiagRec( pszSqlState="22001", pfNativeError=-99999, pszErrorMsg="[IBM][CLI Driver] 
CLI0109E  String data right truncation. SQLSTATE=22001", pcbErrorMsg=72 )
[2023-12-02 03.45.35.994367]     <--- SQL_SUCCESS   Time elapsed - +2.460000E-004 seconds
[2023-12-02 03.45.36.003536] SQLGetData( hStmt=1:1, iCol=1, fCType=SQL_C_CHAR, rgbValue=&00007fa2ddc07610, 
cbValueMax=1023, pcbValue=&00007ffcc925a61c )
[2023-12-02 03.45.36.003595]     ---> Time elapsed - +1.800000E-005 seconds
[2023-12-02 03.45.36.003640] SQLGetData( rgbValue="610001000000000000003DFD37000000" - 
x'3631303030313030303030303030303030303030334446443337303030303030', pcbValue=32 )
[2023-12-02 03.45.36.003671]     <--- SQL_SUCCESS   Time elapsed - +1.350000E-004 seconds

When I collected cli trace using latest ibm_db driver for same test program, saw below entries which seems correct:

SQLBindParameter( hStmt=1:1, iPar=1, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_CHAR, fSQLType=SQL_VARCHAR,
 cbColDef=16, ibScale=0, rgbValue=&00000268c1390350, cbValueMax=16, pcbValue=&00000268c14dc71c )
    ---> Time elapsed - +7.540000E-004 seconds
SQLBindParameter( )
    <--- SQL_SUCCESS   Time elapsed - +1.047000E-002 seconds
SQLExecute( hStmt=1:1 )
    ---> Time elapsed - +7.790000E-004 seconds
( Package="SYSSH200          ", Section=4 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue="a" - x'610001000000000000003DB337000000', pcbValue=16,
 piIndicatorPtr=16 )
( COMMIT REPLY RECEIVED=1 )
SQLExecute( )
    <--- SQL_SUCCESS   Time elapsed - +2.388740E-001 seconds
SQLGetData( hStmt=1:1, iCol=1, fCType=SQL_C_BINARY, rgbValue=&00000268c112e450, cbValueMax=1022,
 pcbValue=&000000a26d5fe2c0 )
    ---> Time elapsed - +1.002000E-003 seconds
SQLGetData( rgbValue=x'610001000000000000003DB337000000', pcbValue=16 )
    <--- SQL_SUCCESS   Time elapsed - +1.012900E-002 seconds

See the mismatch in values of cbValueMax in SQLBindParameter in both traces causing data truncation. Please reinstall ibm_db using npm install ibm_db@latest and then verify your test program. Thanks.

You have neither shared the version of ibm_db driver nor shared the output of npm ls ibm_db command as requested in new issue template. Sorry Bimal, I just noticed the template. Will do for future.

See the mismatch in values of cbValueMax in SQLBindParameter in both traces causing data truncation. Yes, they are different, 16 in your trace matches the byte array length, versus the 19 in my trace.

You are correct. The version mattered. I was on ibm_db@2.8.2, upgraded to ibm_db@3.2.3, reran and the issue is fixed.

Thank you for the prompt reply!

NB: I stumbled on SQL_BINARY and SQL_C_BINARY, honestly was guessing these were the types to use for "for bit data", please do consider adding a sample for "for bit data".

OLD VERSION

$ npm ls ibm_db webserver.7@1.0.0 /home/homer/Backend/WEBSERVER.7 └── ibm_db@2.8.2

AFTER UPGRADING

$ npm ls ibm_db webserver.7@1.0.0 /home/homer/Backend/WEBSERVER.7 └── ibm_db@3.2.3

RERAN

$ node repro.js str: 610001000000000000003DB337000000 put: <Buffer 61 00 01 00 00 00 00 00 00 00 3d b3 37 00 00 00> got: <Buffer 61 00 01 00 00 00 00 00 00 00 3d b3 37 00 00 00>

CLP CONFIRMS

$ db2 "select * from test.t1 order by ts"

TS RID

2023-12-03-22.15.32.100403 x'610001000000000000003DB337000000'

1 record(s) selected.

ibmdb / node-ibm_db