search

ibmdb / node-ibm_db

IBM DB2 and IBM Informix bindings for node
MIT License
188 stars 151 forks source link

Binary Array Inserts fail when values to be inserted are of different lengths #896

Closed markddrake closed 1 year ago

markddrake commented 1 year ago

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(14), VB1 VARBINARY(14), C4B1 CHAR(14) FOR BIT DATA, BL1 BLOB(1024) )`);
    console.log(results)

    const data = [
      '628bac1ce075696504f012d6',
      '6200008bac1ce075696504f012d4',
      '62008bac1ce075696504f012d5',
      '62008bac1ce075696504f012d7',
      '6200008bac1ce075696504f012d8'
    ]

    let idx=0

    try {
      const query = {
        sql:          `insert into BINARY_TAB (ID, B1, VB1, C4B1, BL1) 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 : 14
        },{
           ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')}),
           Length : 14
        },{
           ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')}),
           Length : 14
        },{
           ParamType: 'ARRAY',
           DataType: 'BLOB',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')}),
           Length : 14
        }]
      , ArraySize: data.length
      }

      results = await conn.query(query);
      console.log(results)
    } catch (e) {
      console.log(1,e)
    }

    try {
      const query = {
        sql:          `insert into BINARY_TAB (ID, B1, VB1, C4B1, BL1) values (?, HEXTORAW(?), HEXTORAW(?), HEXTORAW(?), YADAMU.HEXTOBLOB(?))`
      , params: [{
           ParamType: 'ARRAY',
           DataType: 'INT',
           Data: data.map(() => { return data.length + idx++ }),
        },{
           ParamType: 'ARRAY',
           DataType: 'VARCHAR',
           Data: data.map((hex) => { return hex}),
           Length : 12
        },{
           ParamType: 'ARRAY',
           DataType: 'VARCHAR',
           Data: data.map((hex) => { return hex}),
           Length : 12
        },{
           ParamType: 'ARRAY',
           DataType: 'VARCHAR',
           Data: data.map((hex) => { return hex}),
           Length : 12
        },{
           ParamType: 'ARRAY',
           DataType: 'CLOB',
           Data: data.map((hex) => { return 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, BL1 from BINARY_TAB`)
    results.forEach( (row,idx) => { console.log(idx, row.ID, data[idx % data.length], Buffer.from(data[idx % data.length],'hex') , row.B1, (row.B1.toString('hex') === data[idx % data.length]), row.VB1, (row.VB1.toString('hex') === data[idx % data.length]), row.C4B1, (row.C4B1.toString('hex') === data[idx % data.length]), row.BL1, (row.BL1.toString('hex') === data[idx % data.length]))})

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

}

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

Results in

C:\Development\YADAMU\src\scratch\db2>
C:\Development\YADAMU\src\scratch\db2>node testBinary.js
[]
[]
[]
[]
0 0 628bac1ce075696504f012d6 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6 00 00> false <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> true <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6 20 20> false <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> true
1 1 6200008bac1ce075696504f012d4 <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 12 d4> <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 00 00> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 20 20> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false
2 2 62008bac1ce075696504f012d5 <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 d5> <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 00 00> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 20 20> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false
3 3 62008bac1ce075696504f012d7 <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 d7> <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 00 00> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 20 20> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false
4 4 6200008bac1ce075696504f012d8 <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 12 d8> <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 00 00> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 20 20> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false
5 10 628bac1ce075696504f012d6 <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6 00 00> false <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> true <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6 20 20> false <Buffer 62 8b ac 1c e0 75 69 65 04 f0 12 d6> true
6 11 6200008bac1ce075696504f012d4 <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 12 d4> <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 00 00> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 20 20> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false
7 12 62008bac1ce075696504f012d5 <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 d5> <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 00 00> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 20 20> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false
8 13 62008bac1ce075696504f012d7 <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 d7> <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 00 00> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12 20 20> false <Buffer 62 00 8b ac 1c e0 75 69 65 04 f0 12> false
9 14 6200008bac1ce075696504f012d8 <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 12 d8> <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 00 00> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0 20 20> false <Buffer 62 00 00 8b ac 1c e0 75 69 65 04 f0> false
BINARY_TAB contents:
 [
  {
    ID: 0,
    B1: '628BAC1CE075696504F012D60000',
    VB1: '628BAC1CE075696504F012D6',
    C4B1: '628BAC1CE075696504F012D62020',
    BL1: '628BAC1CE075696504F012D6'
  },
  {
    ID: 1,
    B1: '6200008BAC1CE075696504F00000',
    VB1: '6200008BAC1CE075696504F0',
    C4B1: '6200008BAC1CE075696504F02020',
    BL1: '6200008BAC1CE075696504F0'
  },
  {
    ID: 2,
    B1: '62008BAC1CE075696504F0120000',
    VB1: '62008BAC1CE075696504F012',
    C4B1: '62008BAC1CE075696504F0122020',
    BL1: '62008BAC1CE075696504F012'
  },
  {
    ID: 3,
    B1: '62008BAC1CE075696504F0120000',
    VB1: '62008BAC1CE075696504F012',
    C4B1: '62008BAC1CE075696504F0122020',
    BL1: '62008BAC1CE075696504F012'
  },
  {
    ID: 4,
    B1: '6200008BAC1CE075696504F00000',
    VB1: '6200008BAC1CE075696504F0',
    C4B1: '6200008BAC1CE075696504F02020',
    BL1: '6200008BAC1CE075696504F0'
  },
  {
    ID: 10,
    B1: '628BAC1CE075696504F012D60000',
    VB1: '628BAC1CE075696504F012D6',
    C4B1: '628BAC1CE075696504F012D62020',
    BL1: '628BAC1CE075696504F012D6'
  },
  {
    ID: 11,
    B1: '6200008BAC1CE075696504F00000',
    VB1: '6200008BAC1CE075696504F0',
    C4B1: '6200008BAC1CE075696504F02020',
    BL1: '6200008BAC1CE075696504F0'
  },
  {
    ID: 12,
    B1: '62008BAC1CE075696504F0120000',
    VB1: '62008BAC1CE075696504F012',
    C4B1: '62008BAC1CE075696504F0122020',
    BL1: '62008BAC1CE075696504F012'
  },
  {
    ID: 13,
    B1: '62008BAC1CE075696504F0120000',
    VB1: '62008BAC1CE075696504F012',
    C4B1: '62008BAC1CE075696504F0122020',
    BL1: '62008BAC1CE075696504F012'
  },
  {
    ID: 14,
    B1: '6200008BAC1CE075696504F00000',
    VB1: '6200008BAC1CE075696504F0',
    C4B1: '6200008BAC1CE075696504F02020',
    BL1: '6200008BAC1CE075696504F0'
  }
]
success

C:\Development\YADAMU\src\scratch\db2>

It appears that the length of the first member of the 'Data' array has been used as the length for all members of the 'Data' array, despite the fact that the maximum length has been specified using the Length attribute on the parameter block.

Eg in Row with ID1 the last four digits of the supplied value were truncated

bimalkjha commented 1 year ago

Fix of this issue has been delivered and it will be part of next release of ibm_db. Thanks.