what is the correct way to convert a Buffer to a string?

[jpollard-cs]

for all return types where the value.constructor.name === 'Buffer', should conversion to a string be done as:

value.toString('hex').toUpperCase()

or are there further complexities here?

[dmcghan]

If you're fetching buffers out then toString should be fine as they will be fully materialized - you just need to know the encoding. In certain circumstances, you might make use of the StringDecoder class.

[jpollard-cs]

@dmcghan thanks for the quick response - so this question mostly has to do with the RAW data type.. I know this type is stored as hex, but in Oracle it seems to store it as uppercase and it also appears to be case sensitive ... is there any reason doing value.toString('hex').toUpperCase() would not work for all RAW data types?

[dmcghan]

I know this type is stored as hex

RAW is stored as unencoded binary data, like BLOB (but smaller).

Given this table:

create table t (
  c_raw  raw(255)
);

The following should work:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

oracledb.fetchAsBuffer = [oracledb.BLOB];

async function runTest() {
  let conn;

  try {
    let buffer = Buffer.from('Hello World', 'utf8');

    conn = await oracledb.getConnection(config);

    let result = await conn.execute(
      'insert into t (c_raw, c_blob) values (:buffer, :buffer)',
      {
        buffer: buffer
      },
      {
        autoCommit: true
      }
    );

    result = await conn.execute(
      'select c_raw, c_blob from t',
      [],
      {
        outFormat: oracledb.OBJECT
      }
    );

    let row = result.rows[0];

    console.log(row.C_RAW.toString('hex')); // 48656c6c6f20576f726c64
    console.log(row.C_RAW.toString('utf8')); // Hello World

    console.log(row.C_BLOB.toString('hex')); // 48656c6c6f20576f726c64
    console.log(row.C_BLOB.toString('utf8')); // Hello World
  } catch (err) {
    console.error(err);
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

runTest();

in Oracle it seems to store it as uppercase and it also appears to be case sensitive

I think perhaps you're seeing a RAW value displayed as HEX (SQL Developer seems to do this by default). Do you have a test case that could help us understand better?

is there any reason doing value.toString('hex').toUpperCase() would not work for all RAW data types

Perhaps the example above will help explain. Can you tell us what you're trying to do?

[jpollard-cs]

Okay actually the source of my confusion seems to be two-fold - I thought I had read somewhere that RAW was stored as a "Hex number as a string literal" but that was actually suggesting that's the insert syntax (it did seem rather peculiar). The other is that this also appears to be how DataGrip displays the data.

The issue we seem to be running into is that when we pull IDs that are stored as RAW types (not sure why we decided to use RAW types for IDs) we are converting them using value.toString('hex') and then when we try to use that same ID to retrieve or delete the row it doesn't find any rows to retrieve or delete unless we first convert it to uppercase.

[cjbj]

Maybe don't convert them but just use them directly in the WHERE clause? Try this patch on examples/raw1.js:

diff --git a/examples/raw1.js b/examples/raw1.js
index 6d6817fb..2c4c9c80 100644
--- a/examples/raw1.js
+++ b/examples/raw1.js
@@ -38,6 +38,8 @@ if (Number(process.version.match(/^v(\d+\.\d+)/)[1]) < 4)
 else
   buf = Buffer.from(data);
 
+let myRaw;
+
 var insertRaw = function (conn, cb) {
   conn.execute(
     "INSERT INTO myraw VALUES (:r)",
@@ -54,7 +56,8 @@ var insertRaw = function (conn, cb) {
 
 var queryRaw = function (conn, cb) {
   conn.execute(
-    "SELECT r FROM myraw",
+    "SELECT r FROM myraw where r = :r",
+    [myRaw],
     function (err, result) {
       if (err) {
         return cb(err, conn);
@@ -68,6 +71,19 @@ var queryRaw = function (conn, cb) {
     });
 };
 
+var getRaw = function (conn, cb) {
+  conn.execute(
+    "SELECT r FROM myraw",
+    function (err, result) {
+      if (err) {
+        return cb(err, conn);
+      } else {
+        myRaw = result.rows[0][0];
+        return cb(null, conn);
+      }
+    });
+};
+
 // Insert and query the RAW data
 async.waterfall(
   [
@@ -75,6 +91,7 @@ async.waterfall(
       oracledb.getConnection(dbConfig, cb);
     },
     insertRaw,
+    getRaw,
     queryRaw
   ],
   function (err, conn) {

My output is:

$ node raw1.js 
1 row(s) inserted.
Buffer queried:
[ <Buffer 48 65 6c 6c 6f 2c 20 4f 72 61 63 6c 65 21> ]
Hello, Oracle!

[dmcghan]

@jpollard-cs Chris beat me to the punch here. I'm trying to learn more about what you're doing so we can figure out a way to do it in the database. This will likely yield much better performance.

[jpollard-cs]

Thanks for the replies @cjbj and @dmcghan - I will follow-up on this so don't close it out please, but might not be until early next week

[jpollard-cs]

So the db I'm working with is using a RAW Id, for many tables, which is generated by SYS_GUID - it seems converting this to a regular string does not result in human readable characters which is problematic for the current use cases. The GUIDs are being used for reference on a UI so hex seems to be the best format given the circumstances. I guess I just don't understand why this is valid:

SELECT r FROM myraw where r = '48656C6C6F2C204F7261636C6521'

but it won't work with lowercase letters:

SELECT r FROM myraw where r = '48656c6c6f2c204f7261636c6521'

I'm just surprised this is case-sensitive. It would seem intuitive, to me at least, that this should work for upper and lower-case. I can enforce how we format our RAW data types on the way out from the database, but I have very little control over queries like in the above example where we're completely bypassing our ORM.

Using buffers is a little bit too complex as we have a few junior devs and I don't want to complicate things more than necessary right now. Right now my main focus/battle is to get everyone to use transactions.

Any other suggestions how I might simplify this beyond ensuring I convert the buffer to an uppercase hex string?

[cjbj]

The specific question about the query behavior sounds like a question for my best friends like @ChrisSaxonOra at https://asktom.oracle.com/ I guess the optimizer is converting the raw to a string (with uppercase) and then doing the comparison. You can check with EXPLAIN PLAN