Incorrect results when array inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns #883

markddrake · 2022-09-16T07:07:01Z

Using latest versions of node and ibm_db.

import ibmdb  from 'ibm_db'
	
async function main() {	
    const cn = "DATABASE=SAMPLE;HOSTNAME=yadamu-db2;PORT=50000;PROTOCOL=TCPIP;UID=DB2INST1;PWD=oracle;"
    
    const conn = await ibmdb.open(cn)
	let results 
	
	results = await conn.query(`BEGIN DECLARE V_STATEMENT VARCHAR(300) DEFAULT 'drop table BINARY_TAB'; DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN  END;  EXECUTE IMMEDIATE V_STATEMENT; END;`)
	console.log(results)

    results = await conn.querySync(`create table BINARY_TAB (id int, B1 BINARY(12), VB1 VARBINARY(12), C4B1 CHAR(12) FOR BIT DATA )`);
	console.log(results)
	
	const data = [
      '628bac1ce075696504f012d4',
      '628bac1ce075696504f012d5',
      '628bac1ce075696504f012d6',
      '628bac1ce075696504f012d7',
      '628bac1ce075696504f012d8'
    ]
	
	let idx=0
	
	try {
      const query = {
        sql: 		  `insert into BINARY_TAB (ID, B1, VB1, C4B1) values (?, ?, ?, ?)`
	  ,	params: [{
 		   ParamType: 'ARRAY',
           DataType: 'INT',
           Data: data.map(() => { return idx++ }),
		},{
 		   ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')}),
		   Length : 12
		},{
 		   ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')}),
		   Length : 12
	    },{
 		   ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')}),
		   Length : 12
	    }]
	  , ArraySize: data.length
	  }
	  
      results = await conn.query(query);
  	  console.log(results)
	} catch (e) {
	  console.log(1,e)
	}
	
	results = await conn.query(`select ID, B1, VB1, C4B1 from BINARY_TAB`)
    console.log("BINARY_TAB contents:\n", results);
     
    results.forEach( (row) => { console.log(row.ID, data[row.ID], Buffer.from(data[row.ID],'hex'), row.B1, (row.B1.toString('hex') === data[row.ID]), row.VB1, (row.VB1.toString('hex') === data[row.ID]), row.C4B1, (row.C4B1.toString('hex') === data[row.ID]))})


	results = await conn.query(`select ID, RAWTOHEX(B1) "B1", RAWTOHEX(VB1) "VB1", RAWTOHEX(C4B1) "C4B1" from BINARY_TAB`)
    console.log("BINARY_TAB contents:\n", results);


}

main().then(() => { console.log('success')}).catch((e) => { console.log(e) })

Generates the following output

C:\Development\YADAMU>node src\scratch\db2\testBinary.js
[]
[]
[]
BINARY_TAB contents:
 [
  {
    ID: 0,
    B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    VB1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    C4B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>
  },
  {
    ID: 1,
    B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    VB1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    C4B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>
  },
  {
    ID: 2,
    B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    VB1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    C4B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>
  },
  {
    ID: 3,
    B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    VB1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    C4B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>
  },
  {
    ID: 4,
    B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    VB1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>,
    C4B1: <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd>
  }
]
0 628bac1ce075696504f012d4 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d4> <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false
1 628bac1ce075696504f012d5 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d5> <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false
2 628bac1ce075696504f012d6 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false
3 628bac1ce075696504f012d7 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d7> <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false
4 628bac1ce075696504f012d8 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d8> <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> false
BINARY_TAB contents:
 [
  {
    ID: 0,
    B1: '62FDFD1CFD75696504FD12FD',
    VB1: '62FDFD1CFD75696504FD12FD',
    C4B1: '62FDFD1CFD75696504FD12FD'
  },
  {
    ID: 1,
    B1: '62FDFD1CFD75696504FD12FD',
    VB1: '62FDFD1CFD75696504FD12FD',
    C4B1: '62FDFD1CFD75696504FD12FD'
  },
  {
    ID: 2,
    B1: '62FDFD1CFD75696504FD12FD',
    VB1: '62FDFD1CFD75696504FD12FD',
    C4B1: '62FDFD1CFD75696504FD12FD'
  },
  {
    ID: 3,
    B1: '62FDFD1CFD75696504FD12FD',
    VB1: '62FDFD1CFD75696504FD12FD',
    C4B1: '62FDFD1CFD75696504FD12FD'
  },
  {
    ID: 4,
    B1: '62FDFD1CFD75696504FD12FD',
    VB1: '62FDFD1CFD75696504FD12FD',
    C4B1: '62FDFD1CFD75696504FD12FD'
  }
]
success

C:\Development\YADAMU>

Looking at the content in the database It appears that the values were not inserted correctly.

b2 => select * from BINARY_TAB;

ID          B1                          VB1                         C4B1
----------- --------------------------- --------------------------- ---------------------------
          0 x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD'
          1 x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD'
          2 x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD'
          3 x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD'
          4 x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD' x'62FDFD1CFD75696504FD12FD'

  5 record(s) selected.

Not sure if my code is not correct, or if there is a bug.

The text was updated successfully, but these errors were encountered:

markddrake · 2022-09-16T19:38:52Z

Same issue is seen for BLOB columns

bimalkjha · 2022-09-26T20:14:43Z

Ok. So, you are inserting <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d8> but it get inserted as <Buffer 62 fd fd 1c fd 75 69 65 04 fd 12 fd> into the database. Let me check in detail. Thanks.

markddrake · 2022-09-26T21:35:57Z

Row one should end in d4, row2 in d5 etc...

markddrake · 2022-11-19T17:09:53Z

Any update ?

bimalkjha · 2022-11-19T18:15:24Z

@markddrake Started working on it, will provide update soon. Thanks.

* update Readme file (Bimal Jha) * update: windows binary for nodejs v19.x (Bimal Jha) * fix: for binary data array insert issue #883 (Bimal Jha) * fix: update nan version to 2.17.0 (Bimal Jha) * fea: Add support for getFunctions() APIs (Bimal Jha) * build(deps): bump minimatch from 3.0.4 to 3.1.2 (#893) (dependabot[bot]) * fea: add support for getTypeInfo() APIs (Bimal Jha) * Fix error in driverInstaller on z/OS (#884) (jolai)

bimalkjha · 2022-12-05T00:33:15Z

@markddrake This issue is fixed in latest release. Please check https://github.com/ibmdb/node-ibm_db/blob/master/test/test-binary-array-insert.js for example. Thanks.

markddrake · 2022-12-05T01:35:13Z

Simple test case works. Removing workarounds and running full regression now.

markddrake changed the title ~~Incorrect results when inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns~~ Incorrect results when array inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns Sep 16, 2022

markddrake mentioned this issue Sep 26, 2022

Trying to run an automated cron job to a bulk insert #886

Closed

bimalkjha added a commit that referenced this issue Dec 4, 2022

fix: for binary data array insert issue #883

b8a2be3

bimalkjha closed this as completed Dec 5, 2022

markddrake mentioned this issue Dec 5, 2022

Upgrade to IBM_DB@3.1.0 - Remove remaining workarounds for binary data markddrake/YADAMU---Yet-Another-DAta-Migration-Utility#223

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Incorrect results when array inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns #883

Incorrect results when array inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns #883

markddrake commented Sep 16, 2022

markddrake commented Sep 16, 2022

bimalkjha commented Sep 26, 2022

markddrake commented Sep 26, 2022

markddrake commented Nov 19, 2022

bimalkjha commented Nov 19, 2022

bimalkjha commented Dec 5, 2022

markddrake commented Dec 5, 2022

Incorrect results when array inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns #883

Incorrect results when array inserting content from Buffer into BINARY, VARBINARY and CHAR FOR BIT columns #883

Comments

markddrake commented Sep 16, 2022

markddrake commented Sep 16, 2022

bimalkjha commented Sep 26, 2022

markddrake commented Sep 26, 2022

markddrake commented Nov 19, 2022

bimalkjha commented Nov 19, 2022

bimalkjha commented Dec 5, 2022

markddrake commented Dec 5, 2022