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

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  Compare and synchronize sql dbms data visually

Strobe  Strobe light for your phone

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

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  Compare and synchronize sql dbms data visually

Strobe  Strobe light for your phone