FLOAT value being returned incorrectly

[jorgecolonconsulting]

Value in ACS for the same query shows 150.0 for FLOAT data type.

But what's returned by idb-pconnector is '1.500000000'. It should instead be '150.000000000'. Looks like INTEGER data types are returned correctly.

[kadler]

Does this also fail with nodejs-idb-connector directly?

[abmusse]

@2upmedia

Can you provide example that recreates the bug described here?

I tried to recreate the issue with:

From ACS

CREATE TABLE SCHEMA.PRICES(item VARCHAR(5), price FLOAT(2))

INSERT INTO SCHEMA.PRICES VALUES('TV', 150.98)

const { Connection } = require('idb-pconnector');

async function floatExample() {
  const connection = new Connection({ url: '*LOCAL' });
  const statement = connection.getStatement();

  const results = await statement.exec('SELECT * FROM SCHEMA.PRICES');

  console.log(`results:\n ${JSON.stringify(results)}`);
}

floatExample().catch((error) => {
  console.error(error);
});

[ { ITEM: 'TV', PRICE: '150.98' } ]

[jorgecolonconsulting]

Try changing that up and use

await statement.prepare(sqlStatement); await statement.execute();

instead

On Jan 14, 2020, at 4:14 PM, Abdirahim Musse notifications@github.com wrote:

@2upmedia https://github.com/2upmedia Can you provide example that recreates the bug described here?

I tried to recreate the issue with:

From ACS

CREATE TABLE SCHEMA.PRICES(item VARCHAR(5), price FLOAT(2))

INSERT INTO SCHEMA.PRICES VALUES('TV', 150.98) const { Connection } = require('idb-pconnector');

async function floatExample() { const connection = new Connection({ url: '*LOCAL' }); const statement = connection.getStatement();

const results = await statement.exec('SELECT * FROM SCHEMA.PRICES');

console.log(results:\n ${JSON.stringify(results)}); }

floatExample().catch((error) => { console.error(error); }); [ { ITEM: 'TV', PRICE: '150.98' } ] — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/IBM/nodejs-idb-pconnector/issues/57?email_source=notifications&email_token=AACX4COZUGWHAHDHEGLFGZ3Q5YTLPA5CNFSM4KFOPSW2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEI6EYDI#issuecomment-574376973, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACX4CNAXBW5LQRZBSI5TSLQ5YTLPANCNFSM4KFOPSWQ.

[jorgecolonconsulting]

Also change the data type to FLOAT(52)

On Jan 14, 2020, at 4:14 PM, Abdirahim Musse notifications@github.com wrote:

@2upmedia https://github.com/2upmedia Can you provide example that recreates the bug described here?

I tried to recreate the issue with:

From ACS

CREATE TABLE SCHEMA.PRICES(item VARCHAR(5), price FLOAT(2))

INSERT INTO SCHEMA.PRICES VALUES('TV', 150.98) const { Connection } = require('idb-pconnector');

async function floatExample() { const connection = new Connection({ url: '*LOCAL' }); const statement = connection.getStatement();

const results = await statement.exec('SELECT * FROM SCHEMA.PRICES');

console.log(results:\n ${JSON.stringify(results)}); }

floatExample().catch((error) => { console.error(error); }); [ { ITEM: 'TV', PRICE: '150.98' } ] — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/IBM/nodejs-idb-pconnector/issues/57?email_source=notifications&email_token=AACX4COZUGWHAHDHEGLFGZ3Q5YTLPA5CNFSM4KFOPSW2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEI6EYDI#issuecomment-574376973, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACX4CNAXBW5LQRZBSI5TSLQ5YTLPANCNFSM4KFOPSWQ.

[kadler]

idb-pconnector is just a wrapper around idb-connecter, which does all the real work. I'm going to transfer this issue there.

[kadler]

@2upmedia what version of idb-connector and what version of IBM i are you running?

[jordiwes]

Client is on V7R3.... "idb-pconnector": {
"version": "github:abmusse/nodejs-idb-pconnector#09affd25405d0546dc14d8 4ac84da966d4f11882",
"from": "github:abmusse/nodejs-idb-pconnector#add-asNumber",
"requires": {
"idb-connector": "^1.2.2"
}

[dmabupt]

Hello @2upmedia , I can not recreate the issue with idb-connector@1.2.2 --

• Create the table

  $ db2util "CREATE TABLE XUMENG.PRICE(item VARCHAR(5), price FLOAT(52))"
  $ db2util "INSERT INTO XUMENG.PRICE VALUES('TV', 150.98)"
  $ db2util "SELECT * FROM XUMENG.PRICE"
  "TV","150.98"

• JS code

  
  const {dbconn, dbstmt} = require('idb-connector');

const sSql = 'SELECT FROM XUMENG.PRICE'; const connection = new dbconn(); connection.conn('LOCAL'); connection.debug(true); const statement = new dbstmt(connection);

statement.prepare(sSql, (err) => { statement.execute((err) => { statement.fetchAll((rs, err) => { console.log(JSON.stringify(rs)); statement.close(); connection.disconn(); connection.close(); }) }) });

* Run idb-connector

$ npm ls idb-connector dbtest@1.0.0 /home/xumeng/test/dbtest `-- idb-connector@1.2.2

$ node test.js Prepare(). SQLPrepare(0): SELECT * FROM XUMENG.PRICE Execute(). SQLExecuteAsync(0): SQLNUMRESULTSCOLS(0) Column Count = 2 SQLDescribeCol(0) index[0] sqlType[12] colScale[0] colPrecise[5] SQLDescribeCol(0) index[1] sqlType[8] colScale[0] colPrecise[8] FetchAllAsync(). [{"ITEM":"TV","PRICE":"150.98"}] ......

Would you check the FLOAT(52) field's **sqlType** and **colPrecise** with `dbconn.debug(true)`? 
In my test the FLOAT(52) field have **sqlType[8]** and  **colPrecise[8]**

[jordiwes]

@dmabupt We were able to recreate with a stored procedure. Create this stored proc: CREATE OR REPLACE PROCEDURE SCHEMA.PRICESPROC () LANGUAGE SQL DYNAMIC RESULT SETS 1 BEGIN DECLARE C1 CURSOR FOR SELECT item, price from SCHEMA.PRICES; OPEN C1; RETURN; END

And test that with prepare and execute from Node.js. We have recreated it on multiple systems.

[dmabupt]

Thanks @jordiwes , I can recreate the issue now.

This is very interesting --

• If I directly call the SELECT query, the returned DOUBLE value keeps the original format 150.98 and that is no problem.
• If the query is wrapped in a procedure, the returned DOUBLE value become 1.509800000000E+02. The total length exceeds the limit, so the tailing part E+02 is cut off. That is the problem.

I can increase the buffer to resolve the problem.

[dmabupt]

resolved with commit b034342 idb-connector v1.2.5 is released to fix this issue. Thanks!

[dmabupt]

BTW, the returned value is now "1.509800000000E+02". And you can use dbstmt.asNumber(true) to convert it to 150.98.

[abmusse]

@2upmedia @jordiwes

Does the latest fix v1.2.5 resolve the issue?

[jorgecolonconsulting]

@abmusse as far as we have been told, yes. Thanks! We’ll re-open if there’s additional issues.