Quick Links

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

From:	Farid z <farid(at)zidsoft(dot)com>
To:	Jon Raiford <raiford(at)labware(dot)com>, "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject:	RE: Bug report: odbc driver does not convert wide chars to chars
Date:	2021-11-23 00:06:54
Message-ID:	FDA13E12-E395-46CA-9C0B-1CA017BA782D@hxcore.ol
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-odbc

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style></style></head><body lang=EN-US link=blue vlink="#954F72" style='word-wrap:break-word'><div class=WordSection1>Client ODBC apps are supposed to use the narrow or wide ODBC API depending on whether the client app is a narrow or wide app on Windows (A or W versions of the Windows/ODBC API).<o:p> </o:p>Since my app is a narrow Windows app (uses narrow windows and ODBC API), it uses the ANSI/narrow versions of MySQL and MariaDB drivers and all ODBC drivers that have separate ANSI/narrow and Wide versions of a driver to match the API narrow/wide ODBC API calls of the drivers.<o:p> </o:p>The ANSI/wide determines what version of the ODBC API is called (narrow or wide,ie, SQLBindColA or SQLBindColW, etc) and has nothing to do with what actual data types the app uses. Client ODBC apps can use any ODBC data type including char and wchar_t data types and ODBC Drivers are required to convert from bound parameter data type to target dbms column data type as necessary.<o:p> </o:p>When an app (whether the app uses narrow or wide version of the ODBC API) binds a parameter/column data buffer as wchar_t and the database column data type is UTF-8, the driver has to converts the wchar_t buffer data to UTF-8 on inserting the data into the dbms. This conversion of data types as necessary has nothing to do with whether the ODBC driver is A or W).<o:p> </o:p>So it is very confusing to make a narrow Windows client API app connect with W ODBC driver where there is also a narrow version of the driver. This would at a minimum require the ODBC driver manager to convert perfectly valid narrow client data to wide data to match the ODBC Driver Wide API interface.<o:p> </o:p>I tested the PostgreSQL Unicode version of the ODBC driver and it worked in this case. However, the driver performance is significantly reduced by over 50% (ODBC Driver Manager has to convert all the client narrow data buffers to wide buffers as it relays the client data to the ODBC driver).<o:p> </o:p>So, this is a bug in the PostgreSQL ANSI ODBC driver. But I guess, it has a workaround with a severely degraded ODBC driver performance.<o:p> </o:p>Not sure why the PostgreSQL narrow driver does not do what it is supposed to do rather than requiring apps to use the wide version of the driver to work with wchar_t data. The ODBC spec says client apps can use any valid ODBC data types and the ODBC driver is supposed to convert the data types as necessary.<o:p> </o:p>With Windows now supporting UTF-8 narrow apps natively via UTF-8 code page, it is not very helpful, nor necessary, to incur severely degraded PostgreSQL ODBC driver performance by forcing native Windows UTF-8/narrow client apps to have to use the wide version of the PostgreSQL ODBC driver to work with other DBMS wchar_t data types (like MS SQL Server, Oracle, etc, wide char data types).<o:p> </o:p>I would recommend fixing the PostgreSQL ODBC ANSI(narrow) driver to work with native narrow windows client apps without always incurring unnecessary conversions by the ODBC driver manager of the narrow client ODBC apps all data to wide chars.<o:p> </o:p>Farid<o:p> </o:p><a href="https://zidsoft.com/index.html">CompareData</a>  Compare and synchronize sql dbms data visually<o:p></o:p><a href="https://zidsoft.com/strobe.html">Strobe</a>  Strobe light for your phone<o:p></o:p><o:p> </o:p><div style='mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'>From: <a href="mailto:raiford(at)labware(dot)com">Jon Raiford</a> Sent: Monday, November 22, 2021 3:31 PM To: <a href="mailto:pgsql-odbc(at)postgresql(dot)org">pgsql-odbc(at)postgresql(dot)org</a> Cc: <a href="mailto:farid(at)zidsoft(dot)com">Farid z</a> Subject: Re: Bug report: odbc driver does not convert wide chars to chars</div><o:p> </o:p>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.<o:p></o:p> <o:p></o:p>Jon<o:p></o:p> <o:p></o:p><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>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<o:p></o:p></div><div> <o:p></o:p></div>Right, I think that’s what’s happening.<o:p></o:p> <o:p></o:p>App is UTF-8 app code-page on Windows. <o:p></o:p>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.<o:p></o:p> <o:p></o:p>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.<o:p></o:p> <o:p></o:p>This works as expected with other ODBC drivers like MySQL and MariaDB.<o:p></o:p> <o:p></o:p>Farid<o:p></o:p> <o:p></o:p><a href="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=">CompareData</a>  Compare and synchronize sql dbms data visually<o:p></o:p><a href="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</a>  Strobe light for your phone<o:p></o:p> <o:p></o:p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'>From: <a href="mailto:raiford(at)labware(dot)com">Jon Raiford</a> Sent: Monday, November 22, 2021 9:01 AM To: <a href="mailto:farid(at)zidsoft(dot)com">Farid z</a> Subject: Re: Bug report: odbc driver does not convert wide chars to chars<o:p></o:p></div> <o:p></o:p>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.<o:p></o:p> <o:p></o:p>Jon<o:p></o:p> <o:p></o:p><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>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<o:p></o:p></div><div> <o:p></o:p></div>PostgreSQL 14.0.0, PSQLODBC30A.DLL 13.02.0000<o:p></o:p>Windows x64 (Windows 10 or Windows 11).<o:p></o:p> <o:p></o:p>Migrating data from SQL Server (or any other dbms that supports SQ_WCHAR/SQL_WVARCHAR) data types) to PostgreSQL.<o:p></o:p> <o:p></o:p>Steps to reproduce:<o:p></o:p> <o:p></o:p>1 create SQL Server table source data table:<o:p></o:p> <o:p></o:p>create table test_char(<o:p></o:p>col1 nchar(8),<o:p></o:p>col2 nvarchar(30));<o:p></o:p> <o:p></o:p>2 create PostgreSQL table target table:<o:p></o:p> <o:p></o:p>create table test_char(<o:p></o:p>col1 char(8),<o:p></o:p>col2 varchar(30));<o:p></o:p> <o:p></o:p>3 add test data in MS SQL Server table:<o:p></o:p> <o:p></o:p>insert into test_char<o:p></o:p>values<o:p></o:p>(N'a', N'a');<o:p></o:p> <o:p></o:p>insert into test_char<o:p></o:p>values<o:p></o:p>(N'bb', N'bb');<o:p></o:p> <o:p></o:p>insert into test_char<o:p></o:p>values<o:p></o:p>(N'ccc', N'ccc');<o:p></o:p> <o:p></o:p>insert into test_char<o:p></o:p>values<o:p></o:p>(N'ffffffff', N'ffffffff');<o:p></o:p> <o:p></o:p>4 application binds the two columns as SQL_C_CHAR and SQL_C_WCHAR (excerpt from attached log).<o:p></o:p> <o:p></o:p>cmpdata         6be8-29f4 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)<o:p></o:p>               HSTMT               0x000000000591E5D0<o:p></o:p>               UWORD                        1 <o:p></o:p>               SWORD                        1 <SQL_PARAM_INPUT><o:p></o:p>               SWORD                       -8 <SQL_C_WCHAR><o:p></o:p>               SWORD                        1 <SQL_CHAR><o:p></o:p>               SQLULEN                    8<o:p></o:p>               SWORD                        0 <o:p></o:p>               PTR                0x0000000000411178<o:p></o:p>               SQLLEN                    18<o:p></o:p>               SQLLEN *            0x0000000000411170 (-1)<o:p></o:p> <o:p></o:p>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.<o:p></o:p> <o:p></o:p>6 Actual inserted data is just the first letter of each bound value.<o:p></o:p> <o:p></o:p>‘b’ instead of ‘bb’, ‘c’ instead of ‘ccc’, ‘f’ instead of ‘ffffffff’.<o:p></o:p> <o:p></o:p> <o:p></o:p>Please see attached log with insert statements and screen shots.<o:p></o:p> <o:p></o:p>Farid<o:p></o:p> <o:p></o:p> <o:p></o:p><a href="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=">CompareData</a>  Compare and synchronize sql dbms data visually<o:p></o:p><a href="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</a>  Strobe light for your phone<o:p></o:p> <o:p></o:p> <o:p></o:p><o:p> </o:p></div></body></html>

Attachment	Content-Type	Size
unknown_filename	text/html	16.7 KB

In response to

Re: Bug report: odbc driver does not convert wide chars to chars at 2021-11-22 20:31:36 from Jon Raiford

Responses

Re: Bug report: odbc driver does not convert wide chars to chars at 2021-11-23 14:00:08 from Jon Raiford

Browse pgsql-odbc by date

	From	Date	Subject
Next Message	Jon Raiford	2021-11-23 14:00:08	Re: Bug report: odbc driver does not convert wide chars to chars
Previous Message	Jon Raiford	2021-11-22 20:31:36	Re: Bug report: odbc driver does not convert wide chars to chars