Node-oracledb: Allow to fetch BLOBS as Buffer

[paulish]

With 0.12 update we've got an ability to query CLOBS as strings in rows. This really simplifies work with CLOBS. It would be nice to get similar simplification for BLOBS by output them as Buffers.

I suggest to allow Oracledb.BUFFER type in fetchInfo for BLOBS and CLOBS. And perhaps introduce Oracledb.fetchAsBuffer global setting.

[sagiegurari]

you can use simple-oracledb for that.
it supports all LOBs

https://github.com/sagiegurari/simple-oracledb#Connection+query

````js
//do this one time
const oracledb = require('oracledb');
const SimpleOracleDB = require('simple-oracledb');
SimpleOracleDB.extend(oracledb);

//get connection via oracledb or via pool
var connection = .....

//read all rows and get an array of objects with all data
connection.query('SELECT my_blob, my_clob FROM sometable WHERE id < :id', [110], function onResults(error, results) {
if (error) {
//handle error...
} else {
console.log(results[3].MY_BLOB);
}
});
````

[cjbj]

@paulish allowing BLOBs to be fetched as Buffer is coming. Do you want a new oracledb.fetchAsBuffer setting or should we just overload oracledb.fetchAsString?

Until then, use simple-oracledb or write your own wrapper!

[paulish]

@sagiegurari, you've made a great addition to the original driver. It indeed almost suites my needs. Nevertheless, it adds a big overhead to the lob reading which can be eliminated inside the driver.

@cjbj, thanks for the information. I don't see how you can overload oracledb.fetchAsString to return Buffers. If you want to have more global type map options in the future then perhaps better to call the setting as oracledb.fetchMap.

oracledb.fetchMap = { oracledb.CLOB: oracledb.STRING, oracledb.BLOB: oracledb.BUFFER }

[sagiegurari]

Personally I'm against globals.
Don't think just about your app code, think about libraries you are using that might also use oracledb and those globals may impact them.
For example, if you are using sails and you are using the sails oracledb adapter (there are 2 of them I think).

[dmcghan]

I think the idea that's been talked about before is that properties at the Oracledb scope, where they make sense, would be duplicated at the Connection scope. Is it worth having them at these 3 scopes?:

Oracledb > Pool > Connection

Pool would inherit from Oracledb. Connections would inherit from Pool or Oracledb (depending on how they are created). But all properties could be overridden as needed?

[sagiegurari]

makes sense

[cjbj]

The option hierarchy has indeed been discussed before and is on my list; I can't immediately find which issue it was. But that's off topic :)

Back to BLOBs: the fetchMap is a good idea. I forget why we decided against a mapping like this when we introduced fetchAsString. Probably for pure readability. The fetchAsString attribute is just for the most common cases, with fetchInfo for fine-grained control. While typing this I've decided to retract my weak argument that BLOBs are binary strings and that fetchAsString could be overloaded. I'm now backing fetchAsBuffer.

[cjbj]

Node-oracledb 1.13 has a fetchAsBuffer option for BLOBs.