Array Insert , VARBINARY and Buffer

markddrake commented 2 years ago

Given the following table

TABLE - PURCHASEORDER
********************************************************************************

Name                Data Type     Type schema   Length   Scale   Nulls Hidden
------------------- ------------- ------------- -------- ------- ----- --------
SYS_NC_OID$         VARBINARY     SYSIBM              16       0 Y     Not     
SYS_NC_ROWINFO$     XML           SYSIBM               0       0 Y     Not     
ACLOID              VARBINARY     SYSIBM              16       0 Y     Not     
OWNERID             VARBINARY     SYSIBM              16       0 Y     Not     

********************************************************************************

What is the correct way to insert into a VARBINARY column, assuming I have a Buffer containing my content. I tried the following

{
  sql: 'insert into "OE1"."PURCHASEORDER" ("SYS_NC_OID$","SYS_NC_ROWINFO$","ACLOID","OWNERID") values (?,?,?,?)',
  params: [
    {
      ParamType: 'ARRAY',
      DataType: 'VARBINARY',
      Data: [
        Buffer(8) [Uint8Array] [
          224, 83, 247,   6,
          232, 10,   6, 183
        ]
      ]
    },
    {
      ParamType: 'ARRAY',
      DataType: 'XML',
      Data: [
        '<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"></PurchaseOrder>'
      ],
      Length: 16777216
    },
    {
      ParamType: 'ARRAY',
      DataType: 'VARBINARY',
      Data: [
        Buffer(8) [Uint8Array] [
          224, 83, 247,   6,
          232, 10,   6, 183
        ]
      ]
    },
    {
      ParamType: 'ARRAY',
      DataType: 'VARBINARY',
      Data: [ Buffer(4) [Uint8Array] [ 0, 0, 0, 107 ] ]
    }
  ]
}

and got

[IBM][CLI Driver][DB2/LINUXX8664] SQL0301N  The value of input variable, expression or parameter number "1" cannot be used because of its data type.  SQLSTATE=07006

DB2Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0301N  The value of input variable, expression or parameter number "1" cannot be used because of its data type.  SQLSTATE=07006

    at DB2DBI.batchInsert (file:///C:/Development/YADAMU/src/node/dbi/db2/db2DBI.js:177:11)
    ... 8 lines matching cause stack trace ...
    at processTicksAndRejections (node:internal/process/task_queues:96:5) {
  _DRIVER_ID: 312.76879999786615,
  cause: Error
      at DB2DBI.batchInsert (file:///C:/Development/YADAMU/src/node/dbi/db2/db2DBI.js:177:11)
      at DB2Writer._writeBatch (file:///C:/Development/YADAMU/src/node/dbi/db2/db2Writer.js:32:40)
      at DB2Writer.processBatch (file:///C:/Development/YADAMU/src/node/dbi/base/yadamuWriter.js:281:34)
      at DB2Writer.doWrite (file:///C:/Development/YADAMU/src/node/dbi/base/yadamuWriter.js:354:17)
      at DB2Writer._write (file:///C:/Development/YADAMU/src/node/dbi/base/yadamuWriter.js:374:10)
      at doWrite (node:internal/streams/writable:409:12)
      at clearBuffer (node:internal/streams/writable:570:7)
      at onwrite (node:internal/streams/writable:462:7)
      at file:///C:/Development/YADAMU/src/node/dbi/base/yadamuWriter.js:374:37
      at processTicksAndRejections (node:internal/process/task_queues:96:5) {
    error: '[ibm_db] SQL_ERROR',
    sqlcode: -301,
    message: '[IBM][CLI Driver][DB2/LINUXX8664] SQL0301N  The value of input variable, expression or parameter number "1" cannot be used because of its data type.  SQLSTATE=07006\r\n',
    state: '07006'
  },
  sql: 'insert into "OE1"."PURCHASEORDER" ("SYS_NC_OID$","SYS_NC_ROWINFO$","ACLOID","OWNERID") values (?,?,?,?)',
  tags: []
}

This worked

{
  sql: 'insert into "OE1"."PURCHASEORDER" ("SYS_NC_OID$","SYS_NC_ROWINFO$","ACLOID","OWNERID") values (HEXTORAW(?),?,HEXTORAW(?),HEXTORAW(?))',
  params: [
    {
      ParamType: 'ARRAY',
      DataType: 'VARCHAR',
      Data: [ 'e053f706e80a06b7' ]
    },
    {
      ParamType: 'ARRAY',
      DataType: 'XML',
      Data: [
        '<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"></PurchaseOrder>'
      ],
      Length: 16777216
    },
    {
      ParamType: 'ARRAY',
      DataType: 'VARCHAR',
      Data: [ 'e053f706e80a06b7' ]
    },
    { ParamType: 'ARRAY', DataType: 'VARCHAR', Data: [ '0000006b' ] }
  ]
}

But converting buffers to hex and the converting them back again on the server seems a little inefficient ?

markddrake commented 2 years ago

I was eventually able to avoid passing data as to HEX and the converting back on the server by binding all BUFFERS as BLOB, regardless of what the actual column data types is, however I cannot get it to work if I bind BUFFERS as VARBINAY

bimalkjha commented 2 years ago

@markddrake Currently in ibm_db code, support for VARBINARY is missing. Instead you can use BINARY. Thanks.

markddrake commented 2 years ago

Stilll cannot get this working without using HEXTORAW and RAWTOHEX..

See following example

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), B2 BINARY(12), VB1 VARBINARY(12), VB2 VARBINARY(12) )`);
    console.log(results)

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

    let idx=0

    try {
      const query = {
        sql:          `insert into BINARY_TAB (ID, B1, B2, VB1, VB2) values (?, ?, HEXTORAW(?), ?, HEXTORAW(?))`
      , params: [{
           ParamType: 'ARRAY',
           DataType: 1,
           Data: data.map(() => { return idx++ })
        },{
           ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')})
        },{
           ParamType: 'ARRAY',
           DataType: 'VARCHAR',
           Data: [...data],
           Length: 24
        },{
           ParamType: 'ARRAY',
           DataType: 'BINARY',
           Data: data.map((hex) => { return Buffer.from(hex,'hex')})
        },{
           ParamType: 'ARRAY',
           DataType: 'VARCHAR',
           Data: [...data],
           Length: 24
        }]
      , ArraySize: data.length
      }

      console.dir(query,{depth:null})

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

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

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

}

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

Which produces the following output

C:\Development\YADAMU>node src\scratch\db2\binary.js
[]
[]
{
  sql: 'insert into BINARY_TAB (ID, B1, B2, VB1, VB2) values (?, ?, HEXTORAW(?), ?, HEXTORAW(?))',
  params: [
    { ParamType: 'ARRAY', DataType: 1, Data: [ 0, 1, 2, 3, 4 ] },
    {
      ParamType: 'ARRAY',
      DataType: 'BINARY',
      Data: [
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 212
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 213
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 214
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 215
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 216
        ]
      ]
    },
    {
      ParamType: 'ARRAY',
      DataType: 'VARCHAR',
      Data: [
        '628bac1ce075696504f012d4',
        '628bac1ce075696504f012d5',
        '628bac1ce075696504f012d6',
        '628bac1ce075696504f012d7',
        '628bac1ce075696504f012d8'
      ],
      Length: 24
    },
    {
      ParamType: 'ARRAY',
      DataType: 'BINARY',
      Data: [
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 212
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 213
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 214
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 215
        ],
        Buffer(12) [Uint8Array] [
           98, 139, 172, 28, 224,
          117, 105, 101,  4, 240,
           18, 216
        ]
      ]
    },
    {
      ParamType: 'ARRAY',
      DataType: 'VARCHAR',
      Data: [
        '628bac1ce075696504f012d4',
        '628bac1ce075696504f012d5',
        '628bac1ce075696504f012d6',
        '628bac1ce075696504f012d7',
        '628bac1ce075696504f012d8'
      ],
      Length: 24
    }
  ],
  ArraySize: 5
}
[]
BINARY_TAB contents:
 [
  {
    ID: 0,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D4',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D4'
  },
  {
    ID: 1,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D5',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D5'
  },
  {
    ID: 2,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D6',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D6'
  },
  {
    ID: 3,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D7',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D7'
  },
  {
    ID: 4,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D8',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D8'
  }
]
BINARY_TAB contents:
 [
  {
    ID: 0,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D4',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D4'
  },
  {
    ID: 1,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D5',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D5'
  },
  {
    ID: 2,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D6',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D6'
  },
  {
    ID: 3,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D7',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D7'
  },
  {
    ID: 4,
    B1: '62FDFD1CFD75696504FD12FD',
    B2: '628BAC1CE075696504F012D8',
    VB1: '62FDFD1CFD75696504FD12FD',
    VB2: '628BAC1CE075696504F012D8'
  }
]
success

C:\Development\YADAMU>

If I pass a buffer columns B1 and VB1 do not round-trip. Based on the result of second select operation which coverts back to HEX inside the database it appears the values are not being written correctly.

Also, it appears that BINARY and VARBINARY is converted to HEX when it is retrieved.

Do I need to change the arguments to pass a Buffer as input without converting to HEX Can I get a Buffer back, rather than HEX ?

Can I pass HEX as input without needing to use HEXTORAW() ?

bimalkjha commented 2 years ago

@markddrake I would suggest to use datatype as BLOB(12) for BINARY FOR BIT DATA in create table and pass the data as Buffer as shown here: https://github.com/ibmdb/node-ibm_db/blob/master/test/test-all-data-types.js#L75 Thanks.

markddrake commented 2 years ago

Looking at the example it appears that the test passes column C18 as a BUFFER to the INSERT but the SELECT returns it as a string.

BTW this example also needs to be updated for the (highly questionable) behavior of returning BIGINT as number (Can we please have an OPTION to return bigint as STRING without having to add an explicit cast)

markddrake commented 2 years ago

Also, I think this technique fails for the values > 0x7F...

See the following

import ibmdb  from 'ibm_db'
import crypto from 'crypto';
import assert from 'assert';

async function main() { 
    const cn = "DATABASE=YADAMU;HOSTNAME=yadamu-db2;PORT=50000;PROTOCOL=TCPIP;UID=DB2INST1;PWD=oracle;"
    const BLOB_LENGTH = 256;

    const conn = await ibmdb.open(cn)
    let results 

    results = await conn.query(`BEGIN DECLARE V_STATEMENT VARCHAR(300) DEFAULT 'drop table BLOB_TAB'; DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN  END;  EXECUTE IMMEDIATE V_STATEMENT; END;`)
    console.log(results)

    results = await conn.querySync(`create table BLOB_TAB (id int, B1 BLOB(${BLOB_LENGTH}))`);
    console.log(results)

    const values = Array.from(Array(BLOB_LENGTH).keys())
    const buf = Buffer.from(values)
    console.log(buf)

    const blobParam = {DataType: "BLOB", Data:buf};

    try {
      const query = {
        sql      : `insert into BLOB_TAB (ID, B1) values (?, ?)`
      , params:  [1, blobParam]
      }

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

    results = await conn.query(`select ID, length(B1) L1, B1 from BLOB_TAB`)
    console.log(BLOB_LENGTH,results)

    results.forEach((row,idx) => { 
      console.log(row.B1.length,  Buffer.byteLength(row.B1), unescape(row.B1).length)
      console.log(JSON.stringify(Buffer.from(unescape(row.B1))))
      assert.equal(buf,Buffer.from(unescape(row.B1)))
    })    

}

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

results in

C:\Development\YADAMU>node src\scratch\db2\blob1.js
[]
[]
<Buffer 00 01 02 03 04 05 06 07 08 09 0a 0b 0c 0d 0e 0f 10 11 12 13 14 15 16 17 18 19 1a 1b 1c 1d 1e 1f 20 21 22 23 24 25 26 27 28 29 2a 2b 2c 2d 2e 2f 30 31 ... 206 more bytes>
[]
256 [
  {
    ID: 1,
    L1: 256,
    B1: '\x00\x01\x02\x03\x04\x05\x06\x07\b\t\n' +
      '\x0B\f\r\x0E\x0F\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1A\x1B\x1C\x1D\x1E\x1F !"#$%&\'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~\x7Fýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýý'
  }
]
256 384 256
"\u0000\u0001\u0002\u0003\u0004\u0005\u0006\u0007\b\t\n\u000b\f\r\u000e\u000f\u0010\u0011\u0012\u0013\u0014\u0015\u0016\u0017\u0018\u0019\u001a\u001b\u001c\u001d\u001e\u001f !\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~ýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýýý"
AssertionError [ERR_ASSERTION]: Buffer(256) [Uint8Array] [

There are two problems..

First the data is returned as a string which needs to converted back using the depricated 'unescape' function.

Second if the Buffer contains values > 0x7F, then the contents of the buffer are not written to the database correctly

db2 => select * from BLOB_TAB;
db2 (cont.) => /

ID          B1                                                                                                                                                                                                                                                                                                                                                                                                                            
----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7FFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFDFD'

  1 record(s) selected.

bimalkjha commented 2 years ago

@markddrake we'll push the BIGINT fix soon. Thanks.

markddrake commented 2 years ago

Opened #859 for Data Corruption issue

markddrake commented 2 years ago

Opened #860 for the issue with the return type for BLOB columns

markddrake commented 2 years ago

New bugs reports filed to address the two issues raised here.

ibmdb / node-ibm_db

Array Insert , VARBINARY and Buffer #850