Re: Bug report: odbc driver does not convert wide chars to chars

This does not sound like a bug. I’m sure that you will find that you are using a Unicode version of MySQL and MariaDB ODBC drivers. Is there a reason you are not using the Unicode Postgres driver? If you insist on using the ANSI driver, I would suggest not trying to use the Unicode “W” (wide char) functions or data types. But really, I think your answer is to just use the Unicode driver. I honestly don’t know why the ANSI driver is still being distributed, but I suppose there are still a few people out there who need it.

Jon

From: Farid z <farid(at)zidsoft(dot)com>
Date: Monday, November 22, 2021 at 2:45 PM
To: Jon Raiford <raiford(at)labware(dot)com>
Subject: RE: Bug report: odbc driver does not convert wide chars to chars

Right, I think that’s what’s happening.

App is UTF-8 app code-page on Windows.
App is telling the driver the data buffer is SQL_WCHAR, driver needs to convert from wide chars to the column database data type rather than treating the data buffer as UTF-8 chars.

Driver is supposed to convert from source data buffers data type to dbms column data type as necessary. Converting from wide-char to UTF-8 is not ambiguous.

This works as expected with other ODBC drivers like MySQL and MariaDB.

Farid

CompareData<https://urldefense.proofpoint.com/v2/url?u=https-3A__zidsoft.com_index.html&d=DwMFaQ&c=sPZ6DeHLiehUHQWKIrsNwWp3t7snrE-az24ztT0w7Jc&r=gyT4z3lWeJogBi5EE3kRdCBevAtSI6-HaWhKJMvs6Ts&m=hQ8oBR8cCqYCsJl-TVQFGLaXP9Nu7Qzxl__3l8tao3w&s=flxcUNyLSdEBYaJA25nYaCamqjHITHmczwdTQpVig14&e=> Compare and synchronize sql dbms data visually
Strobe<https://urldefense.proofpoint.com/v2/url?u=https-3A__zidsoft.com_strobe.html&d=DwMFaQ&c=sPZ6DeHLiehUHQWKIrsNwWp3t7snrE-az24ztT0w7Jc&r=gyT4z3lWeJogBi5EE3kRdCBevAtSI6-HaWhKJMvs6Ts&m=hQ8oBR8cCqYCsJl-TVQFGLaXP9Nu7Qzxl__3l8tao3w&s=aUEcUEkLaMoSDP4FEq0s4LdTMm8-kL2UN97mGgHakgM&e=> Strobe light for your phone

From: Jon Raiford<mailto:raiford(at)labware(dot)com>
Sent: Monday, November 22, 2021 9:01 AM
To: Farid z<mailto:farid(at)zidsoft(dot)com>
Subject: Re: Bug report: odbc driver does not convert wide chars to chars

It looks like you are using the ANSI driver (PSQLODBC30A) rather than the Unicode driver (PSQLODBC35W). The ANSI driver likely sees the first null and assumes the end of the string has been reached. I would suggest trying again with the Unicode driver.

Jon

From: Farid z <farid(at)zidsoft(dot)com>
Date: Sunday, November 21, 2021 at 12:46 PM
To: "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: Bug report: odbc driver does not convert wide chars to chars

PostgreSQL 14.0.0, PSQLODBC30A.DLL 13.02.0000
Windows x64 (Windows 10 or Windows 11).

Migrating data from SQL Server (or any other dbms that supports SQ_WCHAR/SQL_WVARCHAR) data types) to PostgreSQL.

Steps to reproduce:

1 create SQL Server table source data table:

create table test_char(
col1 nchar(8),
col2 nvarchar(30));

2 create PostgreSQL table target table:

create table test_char(
col1 char(8),
col2 varchar(30));

3 add test data in MS SQL Server table:

insert into test_char
values
(N'a', N'a');

insert into test_char
values
(N'bb', N'bb');

insert into test_char
values
(N'ccc', N'ccc');

insert into test_char
values
(N'ffffffff', N'ffffffff');

4 application binds the two columns as SQL_C_CHAR and SQL_C_WCHAR (excerpt from attached log).

cmpdata 6be8-29f4 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS)
HSTMT 0x000000000591E5D0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD 1 <SQL_CHAR>
SQLULEN 8
SWORD 0
PTR 0x0000000000411178
SQLLEN 18
SQLLEN * 0x0000000000411170 (-1)

5 drivers successfully executes the inserts statements into PostgreSQL but does not convert from SQL_WCHAR and SQL_WVARCHAR, looks like it driver just grabs the first bytes of each inserted value.

6 Actual inserted data is just the first letter of each bound value.

‘b’ instead of ‘bb’, ‘c’ instead of ‘ccc’, ‘f’ instead of ‘ffffffff’.

Please see attached log with insert statements and screen shots.

Farid

CompareData<https://urldefense.proofpoint.com/v2/url?u=https-3A__zidsoft.com_index.html&d=DwMGaQ&c=sPZ6DeHLiehUHQWKIrsNwWp3t7snrE-az24ztT0w7Jc&r=gyT4z3lWeJogBi5EE3kRdCBevAtSI6-HaWhKJMvs6Ts&m=CY10HY42cv_uh6yUfGQf0xCClQO_s8dordpEwSYAgNA&s=OaQeFhTGVfbBqWgl0AXbBUkIduRJjDDotyJKaEUwLFw&e=> Compare and synchronize sql dbms data visually
Strobe<https://urldefense.proofpoint.com/v2/url?u=https-3A__zidsoft.com_strobe.html&d=DwMGaQ&c=sPZ6DeHLiehUHQWKIrsNwWp3t7snrE-az24ztT0w7Jc&r=gyT4z3lWeJogBi5EE3kRdCBevAtSI6-HaWhKJMvs6Ts&m=CY10HY42cv_uh6yUfGQf0xCClQO_s8dordpEwSYAgNA&s=nfD8CZAUEL4AIRL43JUPrh6Swle8G6Ml3kB7-lC7GrE&e=> Strobe light for your phone