Can not insert Date with oracledb 4 using Jest #1152

darlenya · 2019-09-11T09:02:15Z

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)

The text was updated successfully, but these errors were encountered:

cjbj · 2019-09-11T16:15:52Z

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 · 2019-09-11T20:20:02Z

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 · 2019-09-11T20:32:26Z

@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 · 2019-09-11T20:48:22Z

@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 · 2019-09-11T20:51:48Z

@cjbj Should we update the doc then? :)

cjbj · 2019-09-11T21:05:19Z

@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 · 2019-09-12T07:13:15Z

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 · 2019-09-12T13:57:02Z

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

cjbj · 2019-09-12T14:22:20Z

@darlenya can you identify what has changed?

darlenya · 2019-09-12T15:22:41Z

@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.

darlenya · 2019-09-12T15:43:24Z

oracletest.zip

A small demo Project which shows the problem.

node demoRun.js

or
yarn test

anthony-tuininga · 2019-09-24T20:38:08Z

I just tried your example....and the dates were inserted without any problems!

darlenya · 2019-09-25T10:09:54Z

@anthony-tuininga yes, currently the dependency is oracledb 3.1.2 if you change this to 4.0.1 it is not working.

anthony-tuininga · 2019-09-25T14:27:53Z

Yes, I did it with oracledb 4 -- no problems. :-)

darlenya · 2019-09-25T14:43:37Z

Hi Anthony, first thanks for the time you invest. I have tried it again with node 10.16.0 and with node 12.10.0. I always got the same Error. I am using a Mac with macOS mojave. With yarn 1.15.2 and 1.17.3. Always got the same error. But the error is only with 'yarn test'. The code itself (outside yarn test) is working

anthony-tuininga · 2019-09-25T15:01:59Z

Ah. I am not using yarn. In that case, it probably isn't an issue with node-oracledb itself. If you figure out what the issue is, though, let us know!

darlenya · 2019-09-25T15:06:14Z

Yes, when I find the issue I will report it. I the pas when I had problems with yarn it was enought to set the "testEnvironment": "node", in the jest.config.json. But this time not.

dmcghan · 2019-09-25T15:39:33Z

@darlenya Try using console.log() to log out the values of the variables you're passing to conn.execute just before you call it. Then check the result both with and without yarn.

darlenya · 2019-09-25T19:34:17Z

Hi @dmcghan, did what you mentioned. Here is the result of the different tries:

add:
console.log([[myDate1], [myDate2], [myDate3]])

oracle 4.0.1

node version: 10.16.0
yarn version: 1.17.3
yarn test

console.log demo.js:24
[ [ 2019-09-25T19:26:07.987Z ],
[ 2019-09-25T19:26:07.987Z ],
[ 2019-09-25T19:26:07.987Z ] ]

console.log demo.js:38
Error: NJS-011: encountered bind value and type mismatch
at Connection.executeMany

node version: 10.16.0
npm version: 6.9.0
npm run test

console.log demo.js:24
[ [ 2019-09-25T19:26:19.643Z ],
[ 2019-09-25T19:26:19.643Z ],
[ 2019-09-25T19:26:19.643Z ] ]

console.log demo.js:38
Error: NJS-011: encountered bind value and type mismatch
at Connection.executeMany

node version: 10.16.0
node demoRun.js

[ [ 2019-09-25T19:30:29.908Z ],
[ 2019-09-25T19:30:29.908Z ],
[ 2019-09-25T19:30:29.908Z ] ]
Date Inserted
Done

dmcghan · 2019-09-25T20:23:56Z

Just pointing out a couple of things here:

The NJS-011: encountered bind value and type mismatch error is very different than the NJS-044: named JSON object is not expected in this context error that you first logged. Not sure what's going on there.
Both npm test and yarn test have issues, which makes it less likely this is a yarn issue.

It looks like you've logged the binds parameters passed to execute, but not the sql or options parameters. The binds parameters don't seem to have any diffs, so try logging ALL of the parameters and then show us the output.

darlenya · 2019-09-26T08:12:32Z

@dmcghan Hi Dan, yes in the first post it was a different error, but in the second post it was this error. I have now change the code in the following way:

const insertDate = async function insertDate() {
  const connection = await oracledb.getConnection(oracleOpts)
  const myDate1 = new Date()
  const myDate2 = new Date()
  const myDate3 = new Date()
  const sql = 'INSERT INTO demo_date (my_time) values (:1)'
  const options = {
    autoCommit: false,
    batchErrors: false,
    bindDefs: [{ type: oracledb.DATE }],
  }
  const bindParameter = [[myDate1], [myDate2], [myDate3]]
  try {
    console.log(myDate1)
    console.log(`Sql: ${sql}`)
    console.log(`bindParamter: ${bindParameter}`)
    console.log(`options: ${options}`)
    await connection.executeMany(
      sql,
      bindParameter,
      options
    )
    // eslint-disable-next-line no-console
    console.log('Date Inserted')
  } catch (e) {
    // eslint-disable-next-line no-console
    console.log(e)
  }
  await connection.close()
}

Here is the Result with npm Test:

`ip-192-168-188-76:oracletest torstenlink$ npm test

[email protected] test /Users/torstenlink/Downloads/oracletest
jest -c jest.config.json

PASS ./demoTest.js
✓ Insert Date Test (891ms)

console.log demo.js:30
2019-09-26T08:07:56.792Z

console.log demo.js:31
Sql: INSERT INTO demo_date (my_time) values (:1)

console.log demo.js:32
bindParamter: Thu Sep 26 2019 10:07:56 GMT+0200 (Central European Summer Time),Thu Sep 26 2019 10:07:56 GMT+0200 (Central European Summer Time),Thu Sep 26 2019 10:07:56 GMT+0200 (Central European Summer Time)

console.log demo.js:33
options: [object Object]

console.log demo.js:43
Error: NJS-011: encountered bind value and type mismatch
at Connection.executeMany (/Users/torstenlink/Downloads/oracletest/node_modules/oracledb/lib/connection.js:207:21)
at /Users/torstenlink/Downloads/oracletest/node_modules/oracledb/lib/util.js:180:16
at new Promise ()
at Connection.executeMany (/Users/torstenlink/Downloads/oracletest/node_modules/oracledb/lib/util.js:168:14)
at executeMany (/Users/torstenlink/Downloads/oracletest/demo.js:34:22)
`

Here the Result with node direct:

ip-192-168-188-76:oracletest torstenlink$ node demoRun.js 
2019-09-26T08:15:10.794Z
Sql: INSERT INTO demo_date (my_time) values (:1)
bindParamter: Thu Sep 26 2019 10:15:10 GMT+0200 (Central European Summer Time),Thu Sep 26 2019 10:15:10 GMT+0200 (Central European Summer Time),Thu Sep 26 2019 10:15:10 GMT+0200 (Central European Summer Time)
options: [object Object]
Date Inserted
Done

dmcghan · 2019-09-26T16:15:36Z

Please change these lines:

    console.log(`Sql: ${sql}`)
    console.log(`bindParamter: ${bindParameter}`)
    console.log(`options: ${options}`)

to this:

    console.log('Sql:', sql)
    console.log('bindParamter:', bindParameter)
    console.log('options:', options)

Then test again.

darlenya · 2019-09-26T19:38:02Z

Ok changed the logs as you suggested.
Here are the results:

node demoRun.js 
2019-09-26T19:35:27.341Z
Sql: INSERT INTO demo_date (my_time) values (:1)
bindParamter: [ [ 2019-09-26T19:35:27.341Z ],
  [ 2019-09-26T19:35:27.341Z ],
  [ 2019-09-26T19:35:27.341Z ] ]
options: { autoCommit: false,
  batchErrors: false,
  bindDefs: [ { type: 2014 } ] }
Date Inserted
Done

And

npm run test

  console.log demo.js:30
    2019-09-26T19:35:38.247Z

  console.log demo.js:31
    Sql: INSERT INTO demo_date (my_time) values (:1)

  console.log demo.js:32
    bindParamter: [ [ 2019-09-26T19:35:38.247Z ],
      [ 2019-09-26T19:35:38.247Z ],
      [ 2019-09-26T19:35:38.247Z ] ]

  console.log demo.js:33
    options: { autoCommit: false,
      batchErrors: false,
      bindDefs: [ { type: 2014 } ] }

  console.log demo.js:43
    Error: NJS-011: encountered bind value and type mismatch
        at Connection.executeMany (/Users/torstenlink/Downloads/oracletest/node_modules/oracledb/lib/connection.js:207:21)

dmcghan · 2019-09-26T20:44:13Z

Thanks. Looks like they are exactly the same...

How is your npm test configured? Which library are you using to run the tests?

I need more info to reproduce...

dmcghan · 2019-09-26T21:08:13Z

Sorry, I just found your download link... Look into it.

anthony-tuininga · 2019-09-27T16:20:37Z

I have diagnosed the issue and determined the reason for the difference -- namely, that the global Date is different when using the jest framework. node-oracledb in version 3 used the V8 C++ API directly which has an IsDate() function available. node-oracleb in version 4 uses N-API but it is only recently that similar support for checking if an object is a date was added. So far I have been using the N-API function for checking to see if the object is an instance of the global Date -- but in your situation that doesn't work. I will instead need to use util.isDate(), and next year, once support for the N-API check for a date is in place in all LTS releases we can use that instead.

And if all of that was too technical, I know the problem and have a solution which will be available to you reasonably soon, I hope!

darlenya · 2019-09-28T08:01:32Z

@ALL of you, thank you for the time invest in this problem.

Should I close it or will you do it?

Torsten Link

cjbj · 2019-09-28T08:12:27Z

We'll close it when it is fixed. Thanks for reporting it and your patience.

cjbj · 2019-11-25T21:58:18Z

@darlenya node-oracledb 4.1 is out with a workaround / fix for using Dates in Jest.

darlenya added the bug label Sep 11, 2019

cjbj removed the bug label Sep 11, 2019

cjbj added the question label Sep 11, 2019

darlenya closed this as completed Sep 12, 2019

cjbj changed the title ~~Can not insert Date with oracledb 4.1~~ Can not insert Date with oracledb 4 Sep 12, 2019

cjbj reopened this Sep 26, 2019

anthony-tuininga added bug and removed question labels Sep 27, 2019

cjbj changed the title ~~Can not insert Date with oracledb 4~~ Can not insert Date with oracledb 4 using Jest Sep 27, 2019

cjbj closed this as completed Nov 25, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Can not insert Date with oracledb 4 using Jest #1152

Can not insert Date with oracledb 4 using Jest #1152

darlenya commented Sep 11, 2019

cjbj commented Sep 11, 2019 •

edited

Loading

darlenya commented Sep 11, 2019

dmcghan commented Sep 11, 2019

cjbj commented Sep 11, 2019

dmcghan commented Sep 11, 2019

cjbj commented Sep 11, 2019 •

edited

Loading

darlenya commented Sep 12, 2019

dmcghan commented Sep 12, 2019

cjbj commented Sep 12, 2019

darlenya commented Sep 12, 2019

darlenya commented Sep 12, 2019

anthony-tuininga commented Sep 24, 2019

darlenya commented Sep 25, 2019

anthony-tuininga commented Sep 25, 2019

darlenya commented Sep 25, 2019

anthony-tuininga commented Sep 25, 2019

darlenya commented Sep 25, 2019

dmcghan commented Sep 25, 2019

darlenya commented Sep 25, 2019

dmcghan commented Sep 25, 2019

darlenya commented Sep 26, 2019 •

edited

Loading

dmcghan commented Sep 26, 2019

darlenya commented Sep 26, 2019 •

edited

Loading

dmcghan commented Sep 26, 2019

dmcghan commented Sep 26, 2019

anthony-tuininga commented Sep 27, 2019

darlenya commented Sep 28, 2019

cjbj commented Sep 28, 2019

cjbj commented Nov 25, 2019

Can not insert Date with oracledb 4 using Jest #1152

Can not insert Date with oracledb 4 using Jest #1152

Comments

darlenya commented Sep 11, 2019

cjbj commented Sep 11, 2019 • edited Loading

darlenya commented Sep 11, 2019

dmcghan commented Sep 11, 2019

cjbj commented Sep 11, 2019

dmcghan commented Sep 11, 2019

cjbj commented Sep 11, 2019 • edited Loading

darlenya commented Sep 12, 2019

dmcghan commented Sep 12, 2019

cjbj commented Sep 12, 2019

darlenya commented Sep 12, 2019

darlenya commented Sep 12, 2019

anthony-tuininga commented Sep 24, 2019

darlenya commented Sep 25, 2019

anthony-tuininga commented Sep 25, 2019

darlenya commented Sep 25, 2019

anthony-tuininga commented Sep 25, 2019

darlenya commented Sep 25, 2019

dmcghan commented Sep 25, 2019

darlenya commented Sep 25, 2019

oracle 4.0.1

node version: 10.16.0 yarn version: 1.17.3 yarn test

node version: 10.16.0 npm version: 6.9.0 npm run test

node version: 10.16.0 node demoRun.js

dmcghan commented Sep 25, 2019

darlenya commented Sep 26, 2019 • edited Loading

Here is the Result with npm Test:

Here the Result with node direct:

dmcghan commented Sep 26, 2019

darlenya commented Sep 26, 2019 • edited Loading

dmcghan commented Sep 26, 2019

dmcghan commented Sep 26, 2019

anthony-tuininga commented Sep 27, 2019

darlenya commented Sep 28, 2019

cjbj commented Sep 28, 2019

cjbj commented Nov 25, 2019

cjbj commented Sep 11, 2019 •

edited

Loading

cjbj commented Sep 11, 2019 •

edited

Loading

node version: 10.16.0
yarn version: 1.17.3
yarn test

node version: 10.16.0
npm version: 6.9.0
npm run test

node version: 10.16.0
node demoRun.js

darlenya commented Sep 26, 2019 •

edited

Loading

darlenya commented Sep 26, 2019 •

edited

Loading