[BUG] Losing decimal places

[Relbot]

Describe your system
Running on Fedora Linux 38 (Workstation Edition)
Kernal Version: Linux 6.2.9-300.fc38.x86_64

• odbc Package Version: ^2.4.9
• ODBC Driver: Zen-Client-linux-15.21-005.000.x86_64
• Database Name: Pervasive SQL (Actian Zen)
• Database Version: 15.21-005.000
• Node.js Version: 18.18.2
• Node.js OS:

Describe the bug
When I query any database table where the rows contain decimal numbers I lose the decimal places
Example:
column X contains 3.5, when I query this column the result is only 3.

column result definition is

{
   name: "vk_1",
   dataType: 2,
   dataTypeName: 'SQL_NUMERIC',
   columnSize: 9,
   decimalDigits: 2,
   nullable: false
}

Expected behavior
Result should be 3.5 instead of 3

If I do the same query with isql I get the result 3.5

Best Regards
Tell

[Relbot]

@markdirish do you have any solution for this? Help appreciated

[Relbot]

I’d be willing to pay if someone can help me solve this problem.

[Relbot]

@kadler can you help?

[kadler]

Can you please provide a trace of the problem?

[Relbot]

Hi @kadler,
Thx for your fast answer. Here the trace
odbcTrace.log

The query starts at row 138.

[kadler]

Ok. From the trace, I see that for vk_1, this is described by the driver as a NUMERIC(9, 2). We bind this as SQL_C_CHAR with a buffer of length 11 (9 total digits + decimal separator + null terminator). The relevant trace info is:

[ODBC][897611][1731625276.699660][SQLDescribeCol.c][504]
		Exit:[SQL_SUCCESS]                
			Column Name = [vk_1]                
			Data Type = 0x7f617c005fd4 -> 2                
			Column Size = 0x7f617c005fd8 -> 9 (64 bits)                
			Decimal Digits = 0x7f617c005fe0 -> 2                
			Nullable = 0x7f617c005ff0 -> 0
[ODBC][897611][1731625276.699664][SQLBindCol.c][245]
		Entry:
			Statement = 0x7f617c000d50
			Column Number = 3
			Target Type = 1 SQL_CHAR
			Target Value = 0x7f617c006010
			Buffer Length = 11
			StrLen Or Ind = 0x7f617c006e10

This should be enough room to successfully retrieve the converted data. Unfortunately, ODBC trace does not show the data that was retrieved. I would say that perhaps this is a driver issue, but as isql shows the correct data and it also uses SQL_C_CHAR (albeit with SQLGetData, not SQLBindCol, but I don't think that would matter) it's probably not that.

Looks like our SQL_NUMERIC handling converts to a number via atof, which I know is notorious for causing problems with locale-sensitive parsing. Do you happen to run in a locale which uses a different decimal separator, eg. 3,5?

[kadler]

One potential fix would be to comment out these lines and rebuild:

node-odbc/src/odbc_connection.cpp

Lines 3382 to 3389 in 3694c4d

	{
	size_t character_count = column->ColumnSize + 2;
	column->buffer_size = character_count * sizeof(SQLCHAR);
	column->bind_type = SQL_C_CHAR;
	data->bound_columns[i].buffer =
	new SQLCHAR[character_count * data->fetch_size]();
	break;
	}

This would cause the data to be bound as a double, obviating the need to call atof to convert it to one later. TBH I'm not sure why we bother binding as char if we have to convert to double anyway. Looks like it's always been binding to SQL_C_CHAR since SQLBindCol support was introduced, which was based on idb-connector code. I suspect just poor code or having to deal with the limited way that SQLCLI supports conversions.

[Relbot]

Thank you for your help.
Regarding your question,

Do you happen to run in a locale which uses a different decimal separator, e.g., 3,5?

no, the number is stored with a “.” (dot) in the database.

Regarding your second response, I will give it a try and get back to you.

[Relbot]

@kadler

Over the past few days, I tested everything thoroughly. Your solution, with commenting out the conversation as you suggested, works perfectly—100%!

Thank you so much for your help; it’s been incredibly useful. Is there any other way I can thank you besides expressing my gratitude here?

Best Regards
Tell aka. Relbot

[kadler]

Thanks, glad it worked! I'm gonna re-open to track making the change here.

[0xfacade]

Here's a workaround that worked for us: multiply the column by 1.0.

Example: instead of

SELECT timestamp, temperature FROM weather_data;

do

SELECT timestamp, (temperature * 1.0) as temperature FROM weather_data;

[0xfacade]

Perhaps this is related: ColumnInfo.decimalDigits is reported as 0, when it is actually 1. This might also be an issue in our ODBC driver, though (no idea what is responsible for what).