Hex values begining with `00` return a blank text string

[codypace68]

With db data that contains umlaut characters, etc. I'm getting a blank string value as the return value. Example characters (ä°C)

[dmabupt]

Hello @codypace68 , I can not reproduce the problem on my system. Would you show me the test code for recreate it? And the version of idb-connector?

My table --

CREATE OR REPLACE TABLE xumeng.test(column1 INTEGER NOT NULL, column2 CLOB(100));

My test code --

const {dbconn, dbstmt} = require('idb-connector');

const sSql = 'SELECT * FROM XUMENG.TEST';
const connection = new dbconn();
connection.conn('*LOCAL');
const statement = new dbstmt(connection);

statement.exec(sSql, (x) => {
  console.log(JSON.stringify(x));
  statement.close();
  connection.disconn();
  connection.close();
});

My test output --

{"COLUMN1":"1","COLUMN2":"ä°C"}

[codypace68]

So i've got some more data on the issue. The field actually contains non-char data. Maybe that helps. Also here's the request code. I'm using the latest version from npm.

const sSql = SELECT * FROM TABLENAME let connection = new dbconn(); connection.conn("*LOCAL"); let statement = new dbstmt(connection); statement.exec(sSql, function(rows, err) { if (err) { throw err; } for (let i = 0; i < rows.length; i += 1) { console.log(rows[i].NONCHARFIELD); } statement.close(); connection.disconn(); connection.close(); })

[dmabupt]

Hello @codypace68 , Would you enable the debug mode and collect the output? ( including column data types, etc) --

let connection = new dbconn();
connection.debug(true);
connection.conn("*LOCAL");

I can recreate the issue with data type clob, but varchar works well.

[codypace68]

Below is the debug output i received.

SQLNUMRESULTSCOLS(0) Column Count = 29
SQLDescribeCol(0) index[0] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[1] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[2] sqlType[1] colScale[0] colPr ecise[3]
SQLDescribeCol(0) index[3] sqlType[1] colScale[0] colPr ecise[5]
SQLDescribeCol(0) index[4] sqlType[3] colScale[5] colPr ecise[15]
SQLDescribeCol(0) index[5] sqlType[3] colScale[5] colPr ecise[15]
SQLDescribeCol(0) index[6] sqlType[3] colScale[0] colPr ecise[9]
SQLDescribeCol(0) index[7] sqlType[1] colScale[0] colPr ecise[3]
SQLDescribeCol(0) index[8] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[9] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[10] sqlType[1] colScale[0] colPr ecise[5]
SQLDescribeCol(0) index[11] sqlType[93] colScale[6] colPr ecise[26]
SQLDescribeCol(0) index[12] sqlType[93] colScale[6] colPr ecise[26]
SQLDescribeCol(0) index[13] sqlType[3] colScale[0] colPr ecise[3]
SQLDescribeCol(0) index[14] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[15] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[16] sqlType[1] colScale[0] colPr ecise[7]
SQLDescribeCol(0) index[17] sqlType[1] colScale[0] colPr ecise[512]
SQLDescribeCol(0) index[18] sqlType[1] colScale[0] colPr ecise[1024]
SQLDescribeCol(0) index[19] sqlType[1] colScale[0] colPr ecise[1024]
SQLDescribeCol(0) index[20] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[21] sqlType[1] colScale[0] colPr ecise[3]
SQLDescribeCol(0) index[22] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[23] sqlType[1] colScale[0] colPr ecise[10]
SQLDescribeCol(0) index[24] sqlType[1] colScale[0] colPr ecise[20]
SQLDescribeCol(0) index[25] sqlType[93] colScale[6] colPr ecise[26]
SQLDescribeCol(0) index[26] sqlType[93] colScale[6] colPr ecise[26]
SQLDescribeCol(0) index[27] sqlType[93] colScale[6] colPr ecise[26]
SQLDescribeCol(0) index[28] sqlType[93] colScale[6] colPr ecise[26]
SQLFreeStmt: stmth 7 [SQL_DROP]
SQLFreeStmt(0)
SQLDisconnect: conn obj [1803b9c90] handler [6]
SQLFreeConnect: conn obj [1803b9c90] handler [6]
SQLFreeConnect[0]

[dmabupt]

sqlType[93] is SQL_TIMESTAMP sqlType[3] is SQL_DECIMAL most of the columns are sqlType[1] which is SQL_CHAR

In my test, only SQL_CLOB has some issues with these characters. Maybe idb-connector has returned the UTF-8 encoded bytes of ä°C but these bytes can not be displayed correctly? For example, my ssh terminal (putty) can display the characters corretly but the 5250 green screen can not.

[codypace68]

Hmmmm. That is a possibility. I'll ssh into the box and let you know what happens. Thank you for all the help by the way!!

[codypace68]

Unfortunately it doesn't appear to make a difference. One more clue to the mystery i found out last night though is that the field contains binary data. So possibly this could explain it? Also, i've tried buffering the return data and the buffers are also empty.

[dmabupt]

If the field contains binary data, the SQL type should be SQL_VARBINARY/SQL_BINARY/SQL_BLOB and the result will be returned in a Node.js Buffer object by default. If the binary data contains byte 0x00 and its SQL type is SQL_CHAR, this byte will terminate the string.

[dmabupt]

To check the raw bytes of the output, you can use the Buffer.from method.

console.log(x[0].COLUMN2);
console.log(Buffer.from(x[0].COLUMN2));

My test result is --

ä°C
<Buffer c3 a4 c2 b0 43>

[codypace68]

Ok. I'll take this data to my team and do some additional testing. It's possible that those fields start with 0x00 which would cause an empty string to be returned. Correct?

[codypace68]

Issue has been resolved. The fields in question began with a 00 hex value. In the end we returned the value as hex SELECT hex(NONCHARFIELD) FROM TABLENME.