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
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.
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.
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
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
As with #859 the only workaround appears to be use a SQL PL procedure.
But again this is not really workable with any signifcant volume of data.