Can not insert Date with oracledb 4 using Jest

[darlenya]

With the update to oracledb 4.1 I can not insert Dates anymore.

The following code works with oracledb 3.1.2

import { oracleOpts } from './oracleOpts'
import oracledb from 'oracledb'

// Create Table for test
// CREATE TABLE dailytt_actual.demo_date
// (
//   my_time DATE
// );

test('Insert Date Test', async () => {
  const connection = await oracledb.getConnection(oracleOpts)
  const myDate = new Date()

  try {
    await connection.execute(
      'INSERT INTO demo_date (my_time) values (:1)',
      [myDate],
      {
        autoCommit: false,
        batchErrors: false,
        bindDefs: [{ type: oracledb.DATE }],
      }
    )
    console.log('Date Inserted')
  } catch (e) {
    console.log(e)
  }
  await connection.close()
})

Since version 4.1 I get the following Error

    Error: NJS-044: named JSON object is not expected in this context
        at Connection.execute (/Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/node_modules/oracledb/lib/connection.js:169:17)
        at /Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/node_modules/oracledb/lib/util.js:180:16
        at new Promise (<anonymous>)
        at Connection.execute (/Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/node_modules/oracledb/lib/util.js:168:14)
        at Object.execute (/Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/tests/Snippet2Test.js:9:22)

[cjbj]

Your code mixes arguments that belong with executeMany() with a call to execute().

As it currently is (only changing autoCommit to true), the insert using execute() works fine with 4.0.1:

cjones@cjones-mac2:~/n$ node issue1152.js 
Date Inserted

And the data is stored fine:

SQL> select * from demo_date;

MY_TIME
---------
12-SEP-19

[darlenya]

Hello cjbj,
first thank you for the fast answer. I have derived the example code from my code where I use execudeMany. Thats the reason for the mix. Sorry.

Now I have the executeMany Example which does not work.

import { oracleOpts } from './oracleOpts'
import oracledb from 'oracledb'

// Dieser Test dient dazu, zu checken ob oracledb4.x funktioniert

// Create Table for test
// CREATE TABLE dailytt_actual.demo_date
// (
//   my_time DATE
// );

test('Insert Date Test', async () => {
  const connection = await oracledb.getConnection(oracleOpts)
  const myDate1 = new Date()
  const myDate2 = new Date()
  const myDate3 = new Date()

  try {
    await connection.executeMany(
      'INSERT INTO demo_date (my_time) values (:1)',
      [[myDate1], [myDate2], [myDate3]],
      {
        autoCommit: true,
        batchErrors: false,
        bindDefs: [{ type: oracledb.DATE }],
      }
    )
    console.log('Date Inserted')
  } catch (e) {
    console.log(e)
  }
  await connection.close()
})

It create the following error:

Error: NJS-011: encountered bind value and type mismatch
    at Connection.executeMany (/Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/node_modules/oracledb/lib/connection.js:207:21)
    at /Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/node_modules/oracledb/lib/util.js:180:16
    at new Promise (<anonymous>)
    at Connection.executeMany (/Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/node_modules/oracledb/lib/util.js:168:14)
    at Object.executeMany (/Users/torstenlink/Documents/entwicklung/bahn/e2e/e2e-step/e2e-step-oracle/tests/Snippet2Test.js:19:22)

When I execute the example with the 3.1.2 Version it is working.

btw.
could you show me the execute example which works for you?

[dmcghan]

@darlenya Your bindDefs should be an array of objects. From the doc:

Each value in the bindDefs array or object should be an object containing the keys dir, maxSize, and type for each bind variable, similar to how execute() bind parameters are identified.

[cjbj]

@dmcghan the given bindDefs is OK because it an array for the single bind var; the 3 dates were multiple rows, not multiple bind vars.

@darlenya The new code also works fine for me.

cjones@cjones-mac2:~/n$ node issue1152.js 
Date Inserted

The 3 dates are inserted:

SQL> select * from demo_date;

MY_TIME
---------
12-SEP-19
12-SEP-19
12-SEP-19

The actual code I ran is:

const oracledb = require('oracledb');
const  oracleOpts = require('./dbconfig.js');

async function run() {

  const connection = await oracledb.getConnection(oracleOpts);
  const myDate1 = new Date();
  const myDate2 = new Date();
  const myDate3 = new Date();

  try {
    await connection.executeMany(
      'INSERT INTO demo_date (my_time) values (:1)',
      [[myDate1], [myDate2], [myDate3]],
      {
        autoCommit: true,
        batchErrors: false,
        bindDefs: [{ type: oracledb.DATE }],
      }
    );
    console.log('Date Inserted');
  } catch (e) {
    console.log(e);
  }
  await connection.close();
}

run();

[dmcghan]

@cjbj Should we update the doc then? :)

[cjbj]

@dmcghan the quoted doc is correct but may be a bit labored (I might change the 2nd "each" to "one"). The SQL has one bind variable and the bindDefs describes one bind variable.

[darlenya]

Hi, thanks for your help!

Your are right, the code is working. It seems to be a problem with the testing framework jest.
After copying the code into a separate file and execute it directly it is working. When calling the function from a jest test, I got the error. This behaviour has changed from oracledb version 3.1.2 to 4.0.1. With the old version also the test with jest is working.

[dmcghan]

@cjbj Ah yes, I was looking at the bind data, not the bindDefs value.

[cjbj]

@darlenya can you identify what has changed?

[darlenya]

@cjbj I have the same code. Only change the dependency of the oracledb version. When switching back to 3.1.2 it is woking again. When using 4.0.1 it fails. The versions of all the other dependencies is unchanged.