BLOB column contains incorrect data following insert from Buffer containing values > 0x7F

[markddrake]

• Target Db2 Server Version: 11.5.7 (Official IBM DB2 Docker container)
• node.js Version: 17.7.2
• ibm_db version: 2.8.1

Running the following code

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)
    }

}

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

Generates the following output

C:\Development\YADAMU>node src\scratch\db2\blob2.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>
[]
success

C:\Development\YADAMU>

But when the content of the table is checked from SQL it appears to be incorrect Any characters > 0x7F appear as DF

C:\Program Files\IBM\SQLLIB\BIN>db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.5.4.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => select * from DB2INST1.BLOB_TAB

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

  1 record(s) selected.

db2 =>

The only workaround I can find is to pass the data in a CLOB as HEX and use a custom SQL PL procedure to convert back to a BLOB. This leads to poor performance with any significant volume of data.

CREATE OR REPLACE FUNCTION YADAMU.HEXTOBLOB (HEX_VALUE CLOB(16M))
RETURNS BLOB(16M)
DETERMINISTIC 
NO EXTERNAL ACTION 
CONTAINS SQL 
BEGIN
  DECLARE RAW_VALUE BLOB(16M);

  DECLARE HEX_LENGTH BIGINT;
  DECLARE OFFSET BIGINT;

  DECLARE HEX_CHUNK VARCHAR(32672);
  DECLARE RAW_CHUNK BLOB(16336);

  IF (HEX_VALUE is NULL) THEN
    return NULL;
  END If;

  SET HEX_LENGTH = LENGTH(HEX_VALUE);
  SET OFFSET = 1;

  SET RAW_VALUE = EMPTY_BLOB();

  WHILE (OFFSET <= HEX_LENGTH) DO
    SET HEX_CHUNK = SUBSTR(HEX_VALUE,OFFSET,32672);
    SET HEX_CHUNK = TRIM(TRAILING FROM HEX_CHUNK);
    SET RAW_CHUNK  = HEXTORAW(HEX_CHUNK);
    SET OFFSET = OFFSET + LENGTH(HEX_CHUNK);
    SET RAW_VALUE = RAW_VALUE CONCAT RAW_CHUNK;
  END WHILE;

  RETURN RAW_VALUE;
END;
/