Node-oracledb: Issue with integer overflow in version 2

[diegomansua]

1. What is your Node.js version? Is it 64-bit or 32-bit? Run version.js from https://github.com/oracle/node-oracledb/blob/master/examples/version.js

Run at: Sun Jan 21 2018 08:59:14 GMT+0100 (CET)
Node.js version: v8.6.0 (darwin x64)
Node-oracledb text format: 1.13.1
Oracle Client library text format: 12.1.0.2.0
Oracle Database text format: 12.1.0.2.0

2. What is your node-oracledb version?

2.0.15

$ npm install oracledb@latest

> [email protected] install /Users/diegomansua/development/projects/test/overflow/node_modules/oracledb
> node package/oracledbinstall.js

oracledb Beginning installation
oracledb Verifying installation
oracledb Binary not found
oracledb Continuing installation
oracledb Oracledb installed
oracledb Verifying installation
oracledb Binary SHA matches SHA in SHASUMS256.txt
oracledb 
oracledb ********************************************************************************
oracledb ** Node-oracledb 2.0.15 installation complete for Node.js 8.6.0 (darwin, x64)
oracledb **
oracledb ** To use the installed node-oracledb:
oracledb ** - You need to have the Oracle Instant Client Basic or Basic Light package in ~/lib or /usr/local/lib
oracledb **   Download from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
oracledb **
oracledb ** Node-oracledb installation instructions: https://github.com/oracle/node-oracledb/blob/master/INSTALL.md
oracledb ********************************************************************************

$ npm list
[email protected] /Users/diegomansua/development/projects/test/overflow
└── [email protected]

3. What is your Oracle client (e.g. Instant Client) version? Is it 64-bit or 32-bit? How was it installed? Where is it installed?

Installed instantclient-basic-macos.x64-12.2.0.1.0-2.zip under /opt/oracle following instructions on https://github.com/oracle/node-oracledb/blob/master/INSTALL.md#instosx

4. What is your Oracle Database version?

SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

5. What is your OS and version?

MacOS Sierra 10.12.6 (can reproduce in Ubuntu as well)

6. What is your compiler version? For example, with GCC, run gcc --version

$ gcc --version
Configured with: --prefix=/Library/Developer/CommandLineTools/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 9.0.0 (clang-900.0.39.2)
Target: x86_64-apple-darwin16.7.0
Thread model: posix
InstalledDir: /Library/Developer/CommandLineTools/usr/bin

7. What Oracle environment variables did you set? How exactly did you set them?

I didn't set any env variables, at least when upgrading to v. 2. LD_LIBRARY_PATH, OCI_LIB_DIR and OCI_INC_DIR don't seem to be set.

8. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib?

Created a symbolic link under /usr/local/lib, as per installation instructions:

$ ls -la /usr/local/lib | grep libclntsh.dylib
lrwxr-xr-x    1 root          admin    51 19 Jan 15:30 libclntsh.dylib.12.1 -> /opt/oracle/instantclient_12_2/libclntsh.dylib.12.1

9. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?

N/A, didn't have installation issues.

10. What error(s) you are seeing?

I create a table with a numeric column:

CREATE TABLE TEST_NODE (ID NUMBER(12,0));

Insert a record with the highest possible value for that numeric column:

INSERT INTO TEST_NODE (ID) VALUES (999999999999);
COMMIT;

If I select the value in SQL Developer:

SELECT ID FROM TEST_NODE;

999999999999

Now in Node using oracledb 2.0.15:

const oracledb = require('oracledb');

const conf = {
  connectString: 'REDACTED',
  password: 'REDACTED',
  user: 'REDACTED'
};

(async () => {
  const connection = await oracledb.getConnection(conf);

  try {
    const {rows} = await connection.execute('SELECT ID FROM TEST_NODE');

    console.log(rows);
  } finally {
    connection.close();
  }
})();

The output is:

$ node index.js
[ [ -727379969 ] ]

Same code in oracledb 1.13.1:

$ node index.js 
[ [ 999999999999 ] ]

So it looks like the numeric column is read as a 32 bit integer. I've reviewed my installation and everything seems to be 64 bit, though.

[anthony-tuininga]

This is a bug in the library. Node.js doesn't support 64-bit integers but the underlying ODPI-C and OCI libraries do support 64-bit integers. We'll add a similar test to this one to the test suite so that this issue doesn't turn up again! Thanks for reporting it.

[LokeshBoran]

Hello @anthony-tuininga and @diegomansua,
Is there any quick solution to the same issue. If there is any please let me know.

Thanks In advance.
Hoping for best :)

[anthony-tuininga]

The solution is fairly simple, assuming you are able to build the node-oracledb binary yourself! Internal testing is still being done but this appears to resolve the issue. Here is the change you need to make:

diff --git a/src/njsConnection.cpp b/src/njsConnection.cpp
index 621ec0de..2e3913cd 100644
--- a/src/njsConnection.cpp
+++ b/src/njsConnection.cpp
@@ -1250,7 +1250,7 @@ bool njsConnection::GetScalarValueFromVar(njsBaton *baton, njsVariable *var,
     }
     switch (var->nativeTypeNum) {
         case DPI_NATIVE_TYPE_INT64:
-            temp = Nan::New<v8::Integer>( (int) data->value.asInt64);
+            temp = Nan::New<Number>(data->value.asInt64);
             break;
         case DPI_NATIVE_TYPE_FLOAT:
             temp = Nan::New<Number>(data->value.asFloat);

@cjbj can let you know when this will make it into the source on GitHub.

[diegomansua]

A workaround I tried that doesn’t require rebuilding the library is to use fetchAsString with the type NUMBER and then cast to int manually...

[anthony-tuininga]

Yes, that would work, too.

[LokeshBoran]

Thanks @anthony-tuininga and @diegomansua, You guys have the awesome team. keep it up :)

[cjbj]

This is fixed in node-oracledb 2.1.

Don't forget that numbers exceeding 2 ^ 53 can't be represented properly in Node.js numbers so fetchAsString still will be required in some cases.