Inserting Buffers for Oracle CLOBs silently fails

[code-ape]

Environment

Knex version: 0.95.11
Database + version: Oracle 18c
OS: Ubuntu Linux

If issue is about oracledb support, tag @atiertant.

Bug

Behavior

Explain what kind of behavior you are getting and how you think it should do

The oracle-node driver clearly documents that the best way to insert a large string for a CLOB is to simply hand a buffer of that string to the driver for the insert parameter (reference: https://blogs.oracle.com/opal/post/node-oracledb-112-working-with-lobs-as-string-and-buffer-connection-pinging). However, today when you do this Knex silently replaces the Buffer reference with the query string EMPTY_BLOB(). Effectively causing a silent failure which inserts no data! 😢

Error message

None, silent failure.

Reduced test code

// Generate insert SQL
const b = Buffer.from('hello', 'utf-8')
const query = knex('table1').insert({ value: b })
const queryText = query.toSQL().toNative()
console.log(query)

Suggested fixes

Initial tests seem to indicate that this can easily be resolved by changing this code:

knex/lib/dialects/oracledb/index.js

Lines 106 to 118 in bfdece3

	parameter(value, builder, formatter) {
	if (typeof value === 'function') {
	return outputQuery(
	compileCallback(value, undefined, this, formatter),
	true,
	builder,
	this
	);
	} else if (value instanceof BlobHelper) {
	return 'EMPTY_BLOB()';
	}
	return unwrapRaw(value, true, builder, this, formatter) || '?';
	}

It appears that some work has been done to support this but never completed. A quick test by myself found that the following code worked:

  parameter(value, builder, formatter) {
    if (typeof value === 'function') {
      ...
    } else if (value instanceof BlobHelper) {
      formatter.bindings.push(value.value);
      return '?';
    }
    ...
  }

It's unclear to me whether or not this is an elegant solution. I suspect you may want to tinker with the Buffer object slightly so that it "pretty prints" better when the query is logged like I did for the example code to reproduce above. Currently it just prints the u8 array which should probably just be replaced by something like [ Buffer of length ${X}].

Additional information

Of important note is that the fallback for using Buffer is to_clob([4000 chars])||to_clob([4000 chars])||... which is around 2,000% slower that using Buffer for inserting a 2MB payload.

[OlivierCavadenti]

@code-ape could be nice if you could provide a PR with your solution + unit/integration tests.

It's unclear to me whether or not this is an elegant solution. I suspect you may want to tinker with the Buffer object slightly so that it "pretty prints" better when the query is logged like I did for the example code to reproduce above. Currently it just prints the u8 array which should probably just be replaced by something like [ Buffer of length ${X}].

I dont see anything wrong expect what you say about pretty print.

[kibertoad]

@OlivierCavadenti I'm 95% done with pr for the issue. @code-ape promised to fix CI for oracle, waiting on that.

[code-ape]

I have a working system for standing up Oracle on GitHub actions. A bit swamped today and tomorrow but should have it in my this weekend if that's alright?

[code-ape]

PR is open for adding CI for Oracle! #4889

[kibertoad]

Released in 2.1.0