search

IBM / node-odbc

ODBC bindings for node
MIT License
146 stars 77 forks source link

[BUG] Numeric data (double) is converted to integer (losing decimal digits) #308

Closed Destinyless closed 1 year ago

Destinyless commented 1 year ago

odbc Package Version: 2.4.6 ODBC Driver: AcuXDBC Database OS: RHE 8 Node.js Version: 18.12.1 Node.js OS: AlmaLinux 9

Selecting columns with decimal digits returns only the interger part.

Attaching log and trace.

Let me know if you need any other info.

executed query and output of result :

 [
  { SOCI_LIMITE_ORDINE: 24091 }, //value should be 24091.29
  statement: 'select soci_limite_ordine from public.soci where soci_codice = 247',
  parameters: [],
  return: undefined,
  count: 8192,
  columns: [
    {
      name: 'SOCI_LIMITE_ORDINE',
      dataType: 3,
      columnSize: 10,
      decimalDigits: 2,
      nullable: true
    }
  ]
]

ODBC trace log :

[ODBC][605123][1674466610.004006][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x5582a7b33050
[ODBC][605123][1674466610.004417][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x5582a7b33050
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = -5
[ODBC][605123][1674466610.004691][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.116003][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x5582a7b33050
        UNICODE Using encoding ASCII 'ANSI_X3.4-1968' and UNICODE 'UCS-2LE'

[ODBC][605123][1674466610.116504][SQLAllocHandle.c][513]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7f0f0c000f70
[ODBC][605123][1674466610.116678][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x7f0f0c000f70
            Window Hdl = (nil)
            Str In = [DSN=vision_sys;UID=acu;PWD=********][length = 35 (SQL_NTS)]
            Str Out = 0x7f0f13f104d0
            Str Out Max = 2048
            Str Out Ptr = (nil)
            Completion = 0
[ODBC][605123][1674466610.179067][SQLDriverConnect.c][1725]
        Exit:[SQL_SUCCESS_WITH_INFO]
            Connection Out [[DSN=vision_sys;UID=acu;PWD=********][length = 35 (SQL_NTS)]]
[ODBC][605123][1674466610.179307][SQLGetInfo.c][236]
        Entry:
            Connection = 0x7f0f0c000f70
            Info Type = SQL_MAX_COLUMN_NAME_LEN (30)
            Info Value = 0x5582a7bd93c0
            Buffer Length = 2
            StrLen = (nil)
[ODBC][605123][1674466610.179522][SQLGetInfo.c][236]
        Entry:
            Connection = 0x7f0f0c000f70
            Info Type = SQL_TXN_ISOLATION_OPTION (72)
            Info Value = 0x5582a7bd93c8
            Buffer Length = 4
            StrLen = (nil)
[ODBC][605123][1674466610.179707][SQLGetInfo.c][236]
        Entry:
            Connection = 0x7f0f0c000f70
            Info Type = SQL_GETDATA_EXTENSIONS (81)
            Info Value = 0x7f0f13f11d38
            Buffer Length = 0
            StrLen = (nil)
[ODBC][605123][1674466610.180424][SQLAllocHandle.c][560]
        Entry:
            Handle Type = 3
            Input Handle = 0x7f0f0c000f70
[ODBC][605123][1674466610.180551][SQLAllocHandle.c][1105]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7f0efc000d40
[ODBC][605123][1674466610.180718][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x7f0efc000d40
            Attribute = SQL_ATTR_CURSOR_TYPE
            Value = 0x3
            StrLen = 0
[ODBC][605123][1674466610.180862][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS_WITH_INFO]
[ODBC][605123][1674466610.181013][SQLExecDirect.c][240]
        Entry:
            Statement = 0x7f0efc000d40
            SQL = [select soci_limite_ordine from public.soci where soci_codice = 247][length = 66 (SQL_NTS)]
[ODBC][605123][1674466610.183282][SQLExecDirect.c][521]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.183484][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x7f0efc000d40
            Attribute = SQL_ATTR_ROW_ARRAY_SIZE
            Value = 0x1
            StrLen = 0
[ODBC][605123][1674466610.183603][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.183722][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x7f0efc000d40
            Attribute = SQL_ATTR_ROW_STATUS_PTR
            Value = 0x7f0efc004120
            StrLen = 0
[ODBC][605123][1674466610.183834][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.183953][SQLRowCount.c][173]
        Entry:
            Statement = 0x7f0efc000d40
            Row Count = 0x5582a79c5690
[ODBC][605123][1674466610.184066][SQLRowCount.c][247]
        Exit:[SQL_SUCCESS]
            Row Count = 0x5582a79c5690 -> 8192 (64 bits)
[ODBC][605123][1674466610.184258][SQLNumResultCols.c][156]
        Entry:
            Statement = 0x7f0efc000d40
            Column Count = 0x5582a79c5668
[ODBC][605123][1674466610.184419][SQLNumResultCols.c][251]
        Exit:[SQL_SUCCESS]
            Count = 0x5582a79c5668 -> 1
[ODBC][605123][1674466610.184654][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x7f0efc000d40
            Attribute = SQL_ATTR_ROW_BIND_TYPE
            Value = (nil)
            StrLen = 0
[ODBC][605123][1674466610.184801][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.184962][SQLSetStmtAttr.c][265]
        Entry:
            Statement = 0x7f0efc000d40
            Attribute = SQL_ATTR_ROWS_FETCHED_PTR
            Value = 0x5582a79c56b0
            StrLen = 0
[ODBC][605123][1674466610.185082][SQLSetStmtAttr.c][928]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.185206][SQLDescribeCol.c][247]
        Entry:
            Statement = 0x7f0efc000d40
            Column Number = 1
            Column Name = 0x7f0efc0041d0
            Buffer Length = 31
            Name Length = 0x7f0efc004192
            Data Type = 0x7f0efc004194
            Column Size = 0x7f0efc004198
            Decimal Digits = 0x7f0efc0041a0
            Nullable = 0x7f0efc0041b0
[ODBC][605123][1674466610.185376][SQLDescribeCol.c][504]
        Exit:[SQL_SUCCESS]                
            Column Name = [SOCI_LIMITE_ORDINE]                
            Data Type = 0x7f0efc004194 -> 3                
            Column Size = 0x7f0efc004198 -> 10 (64 bits)                
            Decimal Digits = 0x7f0efc0041a0 -> 2                
            Nullable = 0x7f0efc0041b0 -> 1
[ODBC][605123][1674466610.185524][SQLBindCol.c][245]
        Entry:
            Statement = 0x7f0efc000d40
            Column Number = 1
            Target Type = 1 SQL_CHAR
            Target Value = 0x7f0efc004220
            Buffer Length = 12
            StrLen Or Ind = 0x7f0efc004200
[ODBC][605123][1674466610.185659][SQLBindCol.c][353]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.185804][SQLFetch.c][162]
        Entry:
            Statement = 0x7f0efc000d40
[ODBC][605123][1674466610.186492][SQLFetch.c][352]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.186619][SQLFetch.c][162]
        Entry:
            Statement = 0x7f0efc000d40
[ODBC][605123][1674466610.186740][SQLFetch.c][352]
        Exit:[SQL_NO_DATA]
[ODBC][605123][1674466610.186864][SQLCloseCursor.c][139]
        Entry:
            Statement = 0x7f0efc000d40
[ODBC][605123][1674466610.187413][SQLCloseCursor.c][234]
        Exit:[SQL_SUCCESS]
[ODBC][605123][1674466610.191018][SQLFreeHandle.c][387]
        Entry:
            Handle Type = 3
            Input Handle = 0x7f0efc000d40
[ODBC][605123][1674466610.191177][SQLFreeHandle.c][490]
        Exit:[SQL_SUCCESS]
stale[bot] commented 1 year ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

Destinyless commented 1 year ago

any chance this bug may be fixed?

Destinyless commented 1 year ago

any update?

markdirish commented 1 year ago

Hi @Destinyless ,

I see the ODBC definitions of the column here:

  name: 'SOCI_LIMITE_ORDINE',
  dataType: 3,
  columnSize: 10,
  decimalDigits: 2,
  nullable: true

What DBMS are you defining this data in, and what is the native definition of the column that is defined? That might help me determine what's going on. A quick look at the code and it seems like SQL_DECIMAL should be binding to SQL_CHAR, and therefore returning the correct data (not converting to an int or sometihng like that...)

markdirish commented 1 year ago

In fact, this line here proves its binding as a char:

[ODBC][605123][1674466610.185524][SQLBindCol.c][245] Entry: Statement = 0x7f0efc000d40 Column Number = 1 T [ODBC][605123][1674466610.185524][SQLBindCol.c][245] Entry: Statement = 0x7f0efc000d40 Column Number = 1 Target Type = 1 SQL_CHAR Target Value = 0x7f0efc004220 Buffer Length = 12

I wonder... the buffer length is adding +2, I wonder if it should be adding +3 (sign, decimal, and null-terminator)...

Destinyless commented 1 year ago

@markdirish The data are stored on Vision 4 files (AcuCobol) and https://www.microfocus.com/documentation/xdbc/unix20/GUID-EACB78E2-70D2-4EBF-B3F4-A96AA2206838.html you can find the docs about their XDBC driver that allows queries on the non-db files. It works fine with Java Vortex Driver and Windows's ODBC

stale[bot] commented 1 year ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

Destinyless commented 1 year ago

I guess this issue ain't going to be fixed? @markdirish it's indeed returning SQL_CHAR but without the decimal part, that's why I said integer (by mistake)

stale[bot] commented 1 year ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

markdirish commented 1 year ago

Not stale

mucasliranda commented 1 year ago

is there any update ?

Destinyless commented 1 year ago

is there any update ?

not that I know of, and sadly it's still not working