BLOB content should be returned as instance of Buffer or HEXDECIMAL not string

[markddrake]

As the following sample shows when a BLOB column is retrieved the column is returned as a 'string'. A BLOB contains binary data and should be returned as Buffer, or at worse string of HEXADECIMAL values that can be converted to a buffer using the Buffer.from() method.

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

const SP = `CREATE OR REPLACE FUNCTION 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;`

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)
    
	results = await conn.querySync(SP);
	console.log(results)
    
	const values = Array.from(Array(BLOB_LENGTH).keys())
	const buf = Buffer.from(values)
	const hex = buf.toString('hex')
	console.log(hex)
	
	const blobParam = {DataType: "CLOB", Data:hex};
	

	try {
      const query = {
        sql      : `insert into BLOB_TAB (ID, B1) values (?, HEXTOBLOB(?))`
	  ,	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)
  
    const row = results[0]
	console.log(2,'Buffer.isBuffer(buf) <--> Buffer.isBuffer(row.B1)',Buffer.isBuffer(buf),Buffer.isBuffer(row.B1));
	console.log(3,'typeof buf <--> typeof row.B1',typeof buf,typeof row.B1);
	console.log(4,'buf.length <--> row.B1.length',buf.length,'<-->',row.B1.length,Buffer.byteLength(row.B1));

    const buf1 = Buffer.from(row.B1)
	console.log(5,'buf.length <--> Buffer.from(row.B1).length',buf.length,'<-->',buf1.length)
		
    for (let i=0; i < buf.length; i++) {
	  if (buf[i] !== buf1[i]){
		console.log('!==',i,buf[i],buf1[i])
		break
	  }
	}
}

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

Note that the stored procedure is used to populate the BLOB as a result of issue #859

When the program is run the following output is generated

C:\Development\YADAMU>node src\scratch\db2\blob1.js
[]
[]
[]
000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f202122232425262728292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b5c5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff
[]
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\x80\x81\x82\x83\x84\x85\x86\x87\x88\x89\x8A\x8B\x8C\x8D\x8E\x8F\x90\x91\x92\x93\x94\x95\x96\x97\x98\x99\x9A\x9B\x9C\x9D\x9E\x9F ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ'
  }
]
2 Buffer.isBuffer(buf) <--> Buffer.isBuffer(row.B1) true false
3 typeof buf <--> typeof row.B1 object string
4 buf.length <--> row.B1.length 256 <--> 256 384
5 buf.length <--> Buffer.from(row.B1).length 256 <--> 384
!== 128 128 194
success

Although 256 bytes were inserted (and testing via SQL shows that the column contains the correct data), 384 bytes are returned when the column is retrieved. Also the column is returned as a string, not a Buffer. Note that this behavoir is also inconsistent with BLOB and VARBINARY. If you fetch a BINARY or VARBINARY column the content is returned as HEXDECIMAL.

SQL Results

db2 => select * from DB2INST1.BLOB_TAB

ID          B1                                                                                                                                                                                                                                                                                                                                                                                                                            
----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'

  1 record(s) selected.

db2 =>

As with #859 the only workaround appears to be use a SQL PL procedure.

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

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

  IF (RAW_VALUE is NULL) THEN
    return NULL;
  END If;
  
  
  SET RAW_LENGTH = LENGTH(RAW_VALUE);
  SET OFFSET = 1;
  
  SET HEX_VALUE = EMPTY_CLOB();
  
  WHILE (OFFSET <= RAW_LENGTH) DO
    SET RAW_CHUNK = SUBSTRB(RAW_VALUE,OFFSET,16336);
	SET HEX_CHUNK = TRIM(TRAILING FROM HEX_CHUNK);
    SET HEX_CHUNK  = RAWTOHEX(RAW_CHUNK);
	SET OFFSET = OFFSET + LENGTH(RAW_CHUNK);
  	SET HEX_VALUE = HEX_VALUE CONCAT HEX_CHUNK;
  END WHILE;
	
  RETURN HEX_VALUE;
END;
/

But again this is not really workable with any signifcant volume of data.