Unicode conversion fails

[ErikJansenIRefact]

Hi,

The test environment setup:

• MacOS Ventura
• unixODBC driver manager version 2.3.11
• Microsoft ODBC Driver 18 For SQL Server
• SQLServer running in Docker (or on a Windows machine)

Database has a single table:

create` table test (col1 `nvarchar(100));

Tested connection to the database with isql (command line tool) and issued the following statement:

insert into test (col1) values (N'Ω');

• result is OK

Tested connection with nodejs and issued the same statement as above on the connection:

const statement =  `insert into IR_TSTGIN.dbo.test (col1) values (N'Ω');`
const result = await connection.query(statement);

• Statement is executed but the result is not OK. The omega charactered is converted to another symbol.

Ran both statements above (via isql and with nodejs) with odbc debug enabled.

• running with isql the log contains a message:

[ODBC][47379][1679320146.155152][SQLAllocHandle.c][395]
Entry:
Handle Type = 2
Input Handle = 0x7f987c815200
UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'

• running through nodejs the log contains the following message:

[ODBC][46268][1679319075.820124][SQLAllocHandle.c][395]
Entry:
Handle Type = 2
Input Handle = 0x7f8a88012600
UNICODE Using encoding ASCII 'US-ASCII' and UNICODE 'UCS-2-INTERNAL'

It seems that the locale settings are not applied when connecting via NodeJS odbc to the driverManager.
The locale settings are:

LANG="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_CTYPE="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_ALL=

[stale]

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.

[stale]

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.

[ErikJansenIRefact]

Please re-open.

[LuisEdMB]

Hi, any solution?

[Ted-CAcert]

See also #379

[kadler]

It seems that the locale settings are not applied when connecting via NodeJS odbc to the driverManager.

Node.js does not call setlocale(), so due to POSIX/Unix rules it will start in the "C" locale which usually uses ASCII. You could try using the setlocale package to set the locale at the start of your script. I found this via this node.js issue otherwise you just find hits for i18n.setlocale() which I don't think actually calls the system setlocale function.