Return numeric properties when sql types are numeric

[jasonclake]

The json result for all sql comes back as strings -- I would like a way to return numeric types when the underlying sql column is numeric.

Given the following sql:

select
cast('CHAR' as CHAR(10)) as TYPE_1
    , cast(-100.10 as NUMERIC(5, 2)) as TYPE_2
    , cast(100.10 as DECIMAL(5, 2)) as TYPE_3
    , cast(100 as INTEGER) as TYPE_4
    , cast(100 as SMALLINT) as TYPE_5
    , cast(100.10 as FLOAT(5)) as TYPE_6
    , cast(100.10 as REAL) as TYPE_7
    , cast(100.10 as DOUBLE) as TYPE_8
    , cast('VARCHAR' as VARCHAR(10)) as TYPE_12
    , cast('BLOB' as BLOB(10)) as TYPE_13
    , cast('CLOB' as CLOB(10)) as TYPE_14
    , cast('DBCLOB' as DBCLOB(10)) as TYPE_15
    , cast(100 as BIGINT) as TYPE_19
    , cast('2020-02-20-15.15.15.000000' as DATE) as TYPE_91
    , cast('2020-02-20-15.15.15.000000' as TIME) as TYPE_92
    , cast('2020-02-20-15.15.15.000000' as TIMESTAMP) as TYPE_93
from sysibm.sysdummy1

Expected results:

{"results": [{ "TYPE_1": "CHAR      ", "TYPE_2": -100.1, "TYPE_3": 100.1, 
    "TYPE_4": 100, "TYPE_5": 100, "TYPE_6": 100.1, "TYPE_7": 100.1, 
    "TYPE_8": 100.1, "TYPE_12": "VARCHAR", 
    "TYPE_13": { "type": "Buffer", "data": [194, 211, 214, 194] }, 
    "TYPE_14": "CLOB", "TYPE_15": "DBCLOB", "TYPE_19": 100, 
    "TYPE_91": "2020-02-20", "TYPE_92": "15.15.15", 
    "TYPE_93": "2020-02-20-15.15.15.000000" }] }

Naive Implementation working in my local copy but I don't know much about c++ and NAPI So I am guessing there could be some issues with the following implementation in dbstmt.cc excerpt from fetchColData

        Napi::Value value;
        Napi::Value nvalue;
        if(result[i][j].rlength == SQL_NULL_DATA)
          value = env.Null();
        else {
          switch(dbColumn[j].sqlType) {
            case SQL_VARBINARY :
            case SQL_BINARY : 
            case SQL_BLOB :
              value = Napi::Buffer<char>::New(env, result[i][j].data, result[i][j].rlength);
              break;
            case SQL_SMALLINT :
            case SQL_INTEGER :
            case SQL_BIGINT :
            case SQL_DECIMAL :
            case SQL_NUMERIC :
            case SQL_FLOAT :
            case SQL_REAL :
            case SQL_DOUBLE :
              nvalue = Napi::String::New(env, result[i][j].data);
              value = Napi::Number::New(env, nvalue.ToNumber());            
              break;
            default : 
              value = Napi::String::New(env, result[i][j].data);
              break

[markdirish]

This is a great suggestion, and something I hadn't noticed before. It looks like all values all bound to SQL_C_CHAR, so casting to a String works (even if its a bit inefficient), or in your code casting to a string and then to a number. It would probably be better to bind the values to the right C Type from the outset, and then just cast to the correct pointer type...

I'm going to take a look at this and see if I can't get it working a little smoother.

Quick question: Do the values you receive seem correct, even if they are in String format? Getting the right value is the hard part, getting it to the right type should be fairly easy to fix.

[jasonclake]

Thanks!

Sorry, but I'm not sure what you are asking "seem correct, even if they are in String format". We have several queries dealing with numbers and so far the text version are the correct value -- but as far as I know they are dealing with numeric(x,y) and integers.

I plan on testing the bounds of each data type according to https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzlimtabs.htm

Something like:

sql:
select
cast(-32768 as SMALLINT) MIN_SMALLINT
    , cast(32767 as SMALLINT) MAX_SMALLINT
--out of range returns empty set with no error thrown(not sure what I should expect)
--but I half expected an empty value for these columns but still return a set since the other columns 
-- were in bounds 
--, cast(-32769 as SMALLINT) OMIN_SMALLINT
--, cast(32768 as SMALLINT) OMAX_SMALLINT
    , cast(-2147483648 as INT) MIN_INT
        , cast(2147483647 as INT) MAX_INT
from sysibm.sysdummy1

results: { "success": true, "data": [
        { "MIN_SMALLINT": -32768, 
        "MAX_SMALLINT": 32767, 
        "MIN_INT": -2147483648, 
        "MAX_INT": 2147483647 }] }

[jasonclake]

"dealing with numeric(x,y) and integers."
Meaning we haven't dealt with any other numeric data types in our queries so far.

[jasonclake]

Larger precision numbers are a problem with my first attempt.

SELECT 
      cast(-9223372036854775808 as BIGINT) MIN_BIGINT,
      cast(+9223372036854775807 as BIGINT) MAX_BIGINT
FROM SYSIBM.SYSDUMMY1

Not the desired output

[
  {
    "MIN_BIGINT":-9223372036854776000,
    "MAX_BIGINT":9223372036854776000
  }
]

[jasonclake]

So I am trying this out:

 switch(dbColumn[j].sqlType) {
            case SQL_VARBINARY :
            case SQL_BINARY : 
            case SQL_BLOB :
              value = Napi::Buffer<char>::New(env, result[i][j].data, result[i][j].rlength);
              break;
            case SQL_SMALLINT :
            case SQL_INTEGER :
              nvalue = Napi::String::New(env, result[i][j].data);
              value = Napi::Number::New(env, nvalue.ToNumber());            
              break;
            case SQL_DECIMAL :
            case SQL_NUMERIC :
              if(dbColumn[j].colPrecise <= 15){
                nvalue = Napi::String::New(env, result[i][j].data);
                value = Napi::Number::New(env, nvalue.ToNumber());            
                break;
              }
            //dec > 15 precision and the following
            // have plenty of values that are not safe for javascript number
            case SQL_BIGINT :
            case SQL_DECFLOAT :
            case SQL_FLOAT :
            case SQL_REAL :
            case SQL_DOUBLE :
            default : 
              value = Napi::String::New(env, result[i][j].data);
              break;
          }

[jasonclake]

Results appear to be what I expected (exceptions noted) and work for what I need -- however I am conflicted about having some db2 numbers come across as Numbers and others as Strings in the javascript result -- although I believe a simple note could explain why. Also a change like this would be a breaking change-depending on the implementation. @markdirish I am interested in your thoughts.

 SELECT
 --due to column definition should be safe?
 cast(-32768 as SMALLINT) MIN_SMALLINT,
 cast(+32767 as SMALLINT) MAX_SMALLINT,
 cast(-2147483648 as INT) MIN_INT,
 cast(+2147483647 as INT) MAX_INT,
 cast(999999999999999 as DECIMAL(15,0)) as DEC_SAFE_15_0,
 cast(.999999999999999 as DECIMAL(15,15)) as DEC_SAFE_15_15,
 cast(9999999.99999999 as DECIMAL(15,8)) as DEC_SAFE_15_8,
 --some values could be unsafe?
 cast(-9223372036854775808 as BIGINT) MIN_BIGINT,
 cast(+9223372036854775807 as BIGINT) MAX_BIGINT,
 cast(9999999999999999 as DECIMAL(16,0)) as DEC_NOT_SAFE_16_0,
 cast(-3.4e38 as REAL) MIN_REAL,
 cast(+3.4e38 as REAL) MAX_REAL,
 cast(-1.18e-38 as REAL) MAX_NEG_REAL,
 cast(+1.18e-38 as REAL) MIN_POS_REAL,
 cast(-1.79e308 as DOUBLE) MIN_DOUBLE,
 cast(+1.79e308 as DOUBLE) MAX_DOUBLE,
 cast(-2.23e-308 as DOUBLE) MAX_NEG_DOUBLE,
 cast(+2.23e-308 as DOUBLE) MIN_POS_DOUBLE,
 cast(-9.999999999999999e384 as DECFLOAT(16)) MIN_DECFLOAT16,
 cast(+9.999999999999999e384 as DECFLOAT(16)) MAX_DECFLOAT16,
 cast(-1e383 as DECFLOAT(16)) MAX_NEG_DECFLOAT16,
 cast(+1e383 as DECFLOAT(16)) MIN_POS_DECFLOAT16,
 cast(-9.999999999999999999999999999999999e6144 as DECFLOAT(34)) MIN_DECFLOAT34,
 cast(+9.999999999999999999999999999999999e6144 as DECFLOAT(34)) MAX_DECFLOAT34,
 cast(-1e-6143 as DECFLOAT(34)) MAX_NEG_DECFLOAT34,
 cast(+1e-6143 as DECFLOAT(34)) MIN_POS_DECFLOAT34,
 cast( -999999999999999999999999999999999999999999999999999999999999999 AS DECIMAL(63)) AS MIN_DEC63,
 cast( +999999999999999999999999999999999999999999999999999999999999999 AS DECIMAL(63)) AS MAX_DEC63,
 cast(-.000000000000000000000000000000000000000000000000000000000000001 AS DECIMAL(63,63)) AS MAX_NEG_DEC63P63,
 cast(+.000000000000000000000000000000000000000000000000000000000000001 AS DECIMAL(63,63)) AS MIN_POS_DEC63P63,
 cast( -999999999999999999999999999999999999999999999999999999999999999 AS NUMERIC(63)) AS MIN_NUM63P0,
 cast( +999999999999999999999999999999999999999999999999999999999999999 AS NUMERIC(63)) AS MAX_NUM63P0,
 cast(-.000000000000000000000000000000000000000000000000000000000000001 AS NUMERIC(63,63)) AS MAX_NEG_NUM63P63,
 cast(+.000000000000000000000000000000000000000000000000000000000000001 AS NUMERIC(63,63)) AS MIN_POS_NUM63P63
 FROM SYSIBM.SYSDUMMY1

[
 {
  "MIN_SMALLINT": -32768,
  "MAX_SMALLINT": 32767,
  "MIN_INT": -2147483648,
  "MAX_INT": 2147483647,
  "DEC_SAFE_15_0": 999999999999999,
  "DEC_SAFE_15_15": 0.999999999999999,
  "DEC_SAFE_15_8": 9999999.99999999,

  "MIN_BIGINT": "-9223372036854775808",
  "MAX_BIGINT": "9223372036854775807",
  "DEC_NOT_SAFE_16_0": "9999999999999999",
TRUNCATION--The_original_code_does_the_same
 x "MIN_REAL": "-3.4E+3",
  "MAX_REAL": "3.4E+38",
 x "MAX_NEG_REAL": "-1.18E-`",
 x "MIN_POS_REAL": "1.18E-3`",
  "MIN_DOUBLE": "-1.79E+308",
  "MAX_DOUBLE": "1.79E+308",
  "MAX_NEG_DOUBLE": "-2.23E-308",
  "MIN_POS_DOUBLE": "2.23E-308",
  "MIN_DECFLOAT16": "-9.999999999999999E+384",
  "MAX_DECFLOAT16": "9.999999999999999E+384",
The_VALUE_is_ok_but_why_doesn't_MAX_NEG_DECFLOAT34_do_the_same?_eg_Add_the_extra_zeros?
 ? "MAX_NEG_DECFLOAT16": "-1.00000000000000E+383",
 ? "MIN_POS_DECFLOAT16": "1.00000000000000E+383",
  "MIN_DECFLOAT34": "-9.999999999999999999999999999999999E+6144",
  "MAX_DECFLOAT34": "9.999999999999999999999999999999999E+6144",
  "MAX_NEG_DECFLOAT34": "-1E-6143",
  "MIN_POS_DECFLOAT34": "1E-6143",
  "MIN_DEC63":         "-999999999999999999999999999999999999999999999999999999999999999",
  "MAX_DEC63":          "999999999999999999999999999999999999999999999999999999999999999",
  "MAX_NEG_DEC63P63": "-.000000000000000000000000000000000000000000000000000000000000001",
  "MIN_POS_DEC63P63":  ".000000000000000000000000000000000000000000000000000000000000001",
  "MIN_NUM63P0":       "-999999999999999999999999999999999999999999999999999999999999999",
  "MAX_NUM63P0":        "999999999999999999999999999999999999999999999999999999999999999",
  "MAX_NEG_NUM63P63": "-.000000000000000000000000000000000000000000000000000000000000001",
  "MIN_POS_NUM63P63":  ".000000000000000000000000000000000000000000000000000000000000001"
 }
]```

[jasonclake]

I would like to propose adding a flag to the connector similar to the debug flag. Call it something like "returnAsNumberSafe" with the default to false for backwards compatibility ie: return strings for all numeric sql types.

If the flag is true then javascript numbers are returned for each column with the following data types: SQL_SMALLINT -32768 to 32767 SQL_INTEGER -2147483648 to 2147483647 SQL_DECIMAL when colPrecise <= 15
SQL_NUMERIC when colPrecise <= 15 All other SQL numeric types are returned as strings due to possible significant digit truncation/rounding when converting to a javascript number.

I feel this would be a good compromise and would return all rows with the same javascript type -- that is I would not want it to be based on the value of the number on a per row basis.

[kadler]

@jasonclake both SMALLINT and INTEGER can always be safely converted to a Javascript number. Due to using some of the bits to store the mantissa along with special values like NaN, a 64-bit double cannot hold all the values of a 64-bit BIGINT, however.

Also, DECIMAL and NUMERIC values with scale < 15 (somehow ODBC got precision/scale opposite from SQL) are never guaranteed to be safely converted because a double is a binary number while the SQL types are decimal numbers.

[jasonclake]

@kadler I agree on SMALLINT, INTEGER as well as BIGINT -- but for backwards compatibility they should all return strings by default as is now and only add a convenience flag that will convert sql numbers to javascript numbers in a predictable way to save the step of pulling the metadata and converting after the fact.

When the dbstmt.cc source refers to the var name colPrecise I interpreted that to mean total count of digits in the number ie: 123456789012345 as well as 1234567890.12345 have a precision of 15 with different scale.

I could be mistaken but it appears the javascript number (IEEE754 double (2^53)) can represent any 15 digit number accurately no matter where the decimal is placed.

Were you saying that there is a 15 digit number that cannot be safely converted? Can you give an example that I could test? Here are all the values I tested along with the program -- using a modified version of dbstmt.cc attached.

test.js

//const { dbconn, dbstmt, SQL_ATTR_AUTOCOMMIT, SQL_FALSE } = require("idb-connector");
const { dbconn, dbstmt } = require('./nodejs-idb-connector/lib/db2a');

function runsqlp(sql, conn) {
    return new Promise((resolve) => {
        let statement = new dbstmt(conn);
        statement.exec(sql, (result, error) => {
            if (error) {
                statement.close();
                return resolve(err);
            }
            statement.close();
            resolve(result);
        });
    });
}

async function test() {
    console.log("```debug output");
    const connection = new dbconn();
    connection.debug(true);
    connection.conn("*LOCAL");
    console.log("---------------------------------------------");
    result = await runsqlp(
        `SELECT  
        cast(-999999999999999 as DECIMAL(15,0)) as MAXNEGDEC_15_0,
        cast(999999999999999 as DECIMAL(15,0)) as MAXPOSDEC_15_0,
        cast(-.999999999999999 as DECIMAL(15,15)) as MAXDEGEXP_15_15,
        cast(.000000000000001 as DECIMAL(15,15)) as MINPOSEXP_15_15,
        cast(.999999999999999 as DECIMAL(15,15)) as MAXPOSEXP_15_15,
        cast(9999999.99999999 as DECIMAL(15,8)) as MIDDLE_15_8
    from sysibm.sysdummy1`,
        connection
    );
    console.log("```debug output \n");
    console.log(`\`\`\` result output \n ${JSON.stringify(result,null," ")} \n\`\`\``);
}

test();

debug output from test

SQLConnect(0): conn obj [1803454f0] handler [2]
---------------------------------------------
SQLExecDirect(0): SELECT
        cast(-999999999999999 as DECIMAL(15,0)) as MAXNEGDEC_15_0,
        cast(999999999999999 as DECIMAL(15,0)) as MAXPOSDEC_15_0,
        cast(-.999999999999999 as DECIMAL(15,15)) as MAXDEGEXP_15_15,
        cast(.000000000000001 as DECIMAL(15,15)) as MINPOSEXP_15_15,
        cast(.999999999999999 as DECIMAL(15,15)) as MAXPOSEXP_15_15,
        cast(9999999.99999999 as DECIMAL(15,8)) as MIDDLE_15_8
    from sysibm.sysdummy1
SQLNUMRESULTSCOLS(0) Column Count = 6
SQLDescribeCol(0)       index[0]        sqlType[3]      colScale[0]     colPrecise[15]
SQLDescribeCol(0)       index[1]        sqlType[3]      colScale[0]     colPrecise[15]
SQLDescribeCol(0)       index[2]        sqlType[3]      colScale[15]    colPrecise[15]
SQLDescribeCol(0)       index[3]        sqlType[3]      colScale[15]    colPrecise[15]
SQLDescribeCol(0)       index[4]        sqlType[3]      colScale[15]    colPrecise[15]
SQLDescribeCol(0)       index[5]        sqlType[3]      colScale[8]     colPrecise[15]
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLFreeStmt(0)

results from test are as expected

 [
 {
  "MAXNEGDEC_15_0": -999999999999999,
  "MAXPOSDEC_15_0": 999999999999999,
  "MAXDEGEXP_15_15": -0.999999999999999,
  "MINPOSEXP_15_15": 1e-15,
  "MAXPOSEXP_15_15": 0.999999999999999,
  "MIDDLE_15_8": 9999999.99999999
 }
]

dbstmt.cc.txt

[dmabupt]

@jasonclake I created a PR #77 to track and review the code change.

[dmabupt]

For the REAL type truncated issue, commit 24ee94823254a7a550d626443f1d4097c6f5abe6 will fix it. (The root cause is that data type REAL has a fixed precision 24)

[dmabupt]

idb-connector v1.2.1 has been released now. Close this issue.