Fetch numbers via temporary strings

[kubo]

IMO, #744 and #638 would be almost solved if node-oracledb converts Oracle numbers to javascript numbers via strings.

As far as I checked, node-js does its best about conversion between numbers and strings. The following script printed 'All okay!' on ubuntu 16.04. (nodejs 4.2.6)

var error = 0;
console.log('Checking whether string -> number -> string returns same value.');
for (var i = 1; i <= 9999999; i++) {
    // create a string representation of a number from 1.0000001 to 1.9999999
    var str = '1.' + ('000000' + i).slice(-7);
    str = str.replace(/0+$/, ''); // '1.1230000' -> '1.123'

    // round trip to a string via number
    var str2 = String(Number(str));

    if (str != str2) {
        console.log(str + ' != ' + str2);
        error++;
    }
}
if (error == 0) {
    console.log('All Okay!');
} else {
    console.log('Mismatch count: ' + error);
}

This implies that if Oracle numbers are converted to javascript numbers via string representations of the Oracle numbers, the javascript numbers are printed correctly. I'm not sure it is true for all numbers. However it will drastically reduce "unexpected" representations such as '7.140000000000001'.

The demerit is performance. The string-to-number conversion must be exactly same with that used by node-js. Otherwise, strings are converted to slightly different numbers. Therefore temporary javascript strings must be created just to use conversion exactly same with node-js.

(Ruby-oci8 did it about 6 years ago. Fortunately ruby C API provides a C-string-to-double conversion function used by ruby itself.)

[cjbj]

Thanks @kubo. We have discussed various options, and the topic is still open. There's also binding to be considered.

[sosoba]

I'm looking into source code and there is a key line:

napi_create_double(env, data->value.asDouble, value)

Look at ODPI-C data structure. Driver does not give a better numeric type than double :(

The only way is to get the data from the driver as a string. Maybe executeOptions should have a factory callback to convert this to suitable value?

What would be very useful is to get the NUMBER columns as a string with 'TM' formatting, independent of the NLS session (comma and group separators).

[cjbj]

@kubo node-oracledb 6.0 has an output type handler feature. Is this satisfactory enough for you?

[sla100]

node-oracledb 6.0:

await conn.execute("ALTER SESSION SET NLS_TERRITORY = 'SPAIN'" ); // comma number separator
const {rows} = await conn.execute('select 38.73 AS N1, 38.73 AS N2, TO_CHAR(38.73) AS N3 from dual', [], {
  outFormat: oracledb.OUT_FORMAT_OBJECT,
  fetchInfo: {
    N2: {type: oracledb.STRING},
  },
});

thick:

{ N1: 38.730000000000004, N2: '38,73', N3: '38,73' }

thin:

{ N1: 38.73, N2: '38.73', N3: '38,73' }

As we can see:

• eliminating the double conversion between OCI and JS eliminates improves the float accuracy (N1)
• the driver performs the conversion to a dotted literal format (N2)

Both changes are positive but break compatibility. Can we assume that this new behavior will be maintained?

[anthony-tuininga]

Both changes are positive but break compatibility. Can we assume that this new behavior will be maintained?

Yes. Technically, we can make thick mode behave the same as thin mode for conversion to string -- not sure if @cjbj and @sharadraju are interested in doing that at this point, however. We will discuss and one of them will update this issue.

[kubo]

Thanks.

In thin mode, this issue disappeared. Probably Oracle numbers are converted to Javascript numbers by string-to-number conversion inside of node.js. In thick mode, the following fetchtypehandler resolves the issue.

fetchTypeHandler: function(metaData) {
  if (metaData.dbType == oracledb.DB_TYPE_NUMBER) {
    const converter = (v) => {
      if (v !== null)
        v = parseFloat(v)
      return v;
    };
    return {type: oracledb.STRING, converter: converter};
  }
}

---

Test code:

const result1 = await connection.execute(
  "select 0.94, 27.99, 0.35, 0.47 from dual", []
);
console.log(result1.rows[0]);

const result2 = await connection.execute(
  "select 0.94, 27.99, 0.35, 0.47 from dual", [], {
    fetchTypeHandler: function(metaData) {
      if (metaData.dbType == oracledb.DB_TYPE_NUMBER) {
        const converter = (v) => {
          if (v !== null)
            v = parseFloat(v)
          return v;
        };
        return {type: oracledb.STRING, converter: converter};
      }
    }
  }
);
console.log(result2.rows[0]);

Thin mode:

[ 0.94, 27.99, 0.35, 0.47 ]
[ 0.94, 27.99, 0.35, 0.47 ]

Numbers are printed correctly with and without fetchTypeHandler.

Thick mode:

[
  0.9400000000000001,
  27.990000000000002,
  0.35000000000000003,
  0.47000000000000003
]
[ 0.94, 27.99, 0.35, 0.47 ]

Numbers are printed incorrectly as #744 and #638 when fetchTypeHandler isn't set. They are printed correctly by fetchTypeHandler.

[cjbj]

@kubo thanks for the evaluation. I will add your snippet to examples/typehandlernum.js.

Regarding the default Thick mode behavior, I would prefer to keep it backward compatible - and keep our focus on making Thin mode the preferred choice. If you are OK with this plan shall we close this enhancement request?

[kubo]

@cjbj Ok. I'll close this.

The following code works as I prefer.

if (!oracledb.thin) {
  oracledb.fetchTypeHandler = function(metaData) {
    if (metaData.dbType == oracledb.DB_TYPE_NUMBER) {
      const converter = (v) => {
        if (v !== null)
          v = parseFloat(v)
        return v;
      };
      return {type: oracledb.STRING, converter: converter};
    }
  };
}

[cjbj]

@kubo thank you.