bound param of value null causes error

[commi]

Describe The Bug

If the parameter of a prepared statement has the value null an error occurs int he library code

To Reproduce

• query SELECT :a (does not have to be executed)
• bind :a to null

Error Stack

batchUpdate error: TypeError: Cannot read property 'getDate' of null
    at Connection.generateBindDefinitionEntry (/home/till/IdeaProjects/bas-syncserver/server/node_modules/simple-oracledb/lib/connection.js:1691:25)
    at Connection.generateBindDefinitions (/home/till/IdeaProjects/bas-syncserver/server/node_modules/simple-oracledb/lib/connection.js:1740:18)
    at Connection.insertOrUpdate (/home/till/IdeaProjects/bas-syncserver/server/node_modules/simple-oracledb/lib/connection.js:581:14)

Code Sample

oracleconnection.batchUpdate(`SELECT :a`,[{a: null}]);

The reason is here:

Connection.prototype.generateBindDefinitionEntry = function (bindDefinitions, key, item) {
 if (typeof item === 'object') {
        if (typeof item.getDate === 'function') {

as null is considered type object

[sagiegurari]

great catch. i can fix that or if you want to submit a PR, that would be great as well

[commi]

Hi, i'd love to, but wont, because i actually am not sure what to do with an item of type null here. I mean what to do with bindDefinitions if anything.

[sagiegurari]

based on the docs: https://oracle.github.io/node-oracledb/doc/api.html#executebindparamtype null is not supported, so i would throw an error in this case.

[cjbj]

In node-oracledb:

    const sql = `select :a from dual`;
    const binds = [null];
    const options = { outFormat: oracledb.OUT_FORMAT_OBJECT };
    const result = await connection.execute(sql, binds, options);
    console.dir(result, { depth: null }); 

gives

 { metaData: [ { name: ':A' } ], rows: [ { ':A': null } ] }

[commi]

Yae i works on 'normal' execute, just not in batchUpdate.

[sagiegurari]

@commi i pushed a code change to branch 2.0.1 any chance you can pull it and try it out? i no longer have access to an oracle DB so testing (for me) requires mocks

[cjbj]

@sagiegurari If it helps, you can create a free DB using Oracle Cloud https://www.oracle.com/cloud/free/ The top level wording is confusing IMHO but the bottom two boxes have the details. You start with a free trial to all services. At the end of the trial you get to keep using free DB and free compute and some other free bits and pieces forever.

[sagiegurari]

thanks and i usually don't like to give payment means but i decided to go for it. it refuses to accept my credit card :) there has got to be a better way. since i develop on arm, i can't try it locally.

[cjbj]

@sagiegurari sorry to hear that

[sagiegurari]

i was able to use gitpod to install oracle xe and test few changes. seems ok. @commi can you verify on your end? or provide me some test case to check?

[cjbj]

@sagiegurari check your gmail for mail from me.

[sagiegurari]

@commi i think this is now resolved. i released a new simple-oracledb version with the fix. basically, you can provide the bind type on your own (actually you could always do that) and i prevent null issues (thats the fix). thanks @cjbj for the help as well :)