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

[markddrake]

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.

[markddrake]

Same issue is seen for BLOB columns

[bimalkjha]

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]

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

[markddrake]

Any update ?

[bimalkjha]

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

[bimalkjha]

@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]

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