Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mpod2-0002My-FG for pgsql-odbc@arkaria.postgresql.org; Wed, 24 Nov 2021 09:31:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mpocz-0002nz-Q1 for pgsql-odbc@arkaria.postgresql.org; Wed, 24 Nov 2021 09:31:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mpXle-0000Jx-W9 for pgsql-odbc@lists.postgresql.org; Tue, 23 Nov 2021 15:31:35 +0000 Received: from mail-qt1-x834.google.com ([2607:f8b0:4864:20::834]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mpXlb-0003np-Mq for pgsql-odbc@postgresql.org; Tue, 23 Nov 2021 15:31:34 +0000 Received: by mail-qt1-x834.google.com with SMTP id n15so20243398qta.0 for ; Tue, 23 Nov 2021 07:31:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zidsoft.com; s=google; h=mime-version:date:from:subject:thread-topic:in-reply-to:message-id :references:to:content-transfer-encoding; bh=vEZiW7oZsGmzFQi6FiAzxDJFXpzRFzQ0iyVYYMv7Ygo=; b=QrYCp+HqVFuQ0t7pLgvwR6d1s2qXfbqgmX3xI86nLGAtNI65Clq9kjTbBR+CQhh3G8 47vwEV0igAESi6bwC/HWClToAJGrpnNRFBsMD9WHfFFVviGF3P1kK+8Q7te5TMcAcdZ0 J+bVva/zcfPSreB71SeRDqA/BS0+NNcu3LvwGcEnVu7etjbGfZzP4R8+EjZW+5uPAWI9 YzbEotqxOJC4T3CRjSU284+jZ65Kp8Y7iGEhqakz07BWf1rtykYd+PFtXUjGMMqGCQ0I sVzIkLrkjXGQ07nZky/DceYH0r8ewB3ljUhpotLvcy8MQ1P18CCugfTEV1xuD+iJ61JV cWnw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:date:from:subject:thread-topic :in-reply-to:message-id:references:to:content-transfer-encoding; bh=vEZiW7oZsGmzFQi6FiAzxDJFXpzRFzQ0iyVYYMv7Ygo=; b=OvPFLtulcaPietE4OMvPurhN4K/ZaPNL1h2S+TaqbusoOzKbBFAPLqapAZ5lMG+aVT iAwJoXq8jSjfIg3h1aOBO/U5ypfEjLpoi7fBI065csBwaOFrZk9gfrHDVbrFU5YhXl+F D6oKyDEmfhvkVB2hrG+e+sokdO6ocz/r1DP5sNXC6TxZ2CKvXS5nzWmXGwx4DC2rZEwR tgyvLckXZZdwv2ls0E9tOQSpHDzd1/QT1q/2MsXH6FS1twR6G7Y6AEnayTpmjlh38USP mOw/BjFrvqr/W9uNtnXjIwHxYTY80IAeKD9HzwPJVsWSLVTLdHqS2Us1rK/YVXnAMXYk rpZQ== X-Gm-Message-State: AOAM533Z1nFOjbc+/JJZskavC/uoYk5mTiNuTKseuwWKFTL8IIhk/PN6 Nyex9Kqr67wiQdFevhEIVltmLA== X-Google-Smtp-Source: ABdhPJyfDM0uQWoq35DIb0VBjKj5L4S/H9+avfJXSU7jIzX2mw6CdFlPaDNW2hoWhZusVvsNPozcLw== X-Received: by 2002:ac8:7e88:: with SMTP id w8mr7242727qtj.357.1637681489273; Tue, 23 Nov 2021 07:31:29 -0800 (PST) Received: from zid1 ([172.58.222.174]) by smtp.gmail.com with ESMTPSA id y18sm6561196qtx.19.2021.11.23.07.31.28 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 23 Nov 2021 07:31:28 -0800 (PST) MIME-Version: 1.0 Date: Tue, 23 Nov 2021 10:31:21 -0500 From: Farid z Subject: RE: Bug report: odbc driver does not convert wide chars to chars Thread-Topic: RE: Bug report: odbc driver does not convert wide chars to chars In-Reply-To: Message-ID: References: <735E4FFF-14AD-430D-AE0A-273DA36BA534@hxcore.ol> <8C357B68-DCE5-43F7-B31D-0EC1552C176C@labware.com> <42B6DD1E-EDBE-4641-8910-E6966B9C2EC8@hxcore.ol> <0CDC5606-43A9-4896-807E-FEA1FD50DFFF@labware.com> , To: Jon Raiford , "pgsql-odbc@postgresql.org" Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk

Below= is some of the relevant ODBC spec documentation regarding clients app usin= g all available ODBC data types regardless of whether the ODBC driver being= used is narrow or wide and topic of ODBC drivers supporting all reasonable= data conversions.

 

No worry, I am reporting this bug because I encountered it and th= at=E2=80=99s the first step in resolving the issue.

 

Farid

 

SQLBindParameter= Function

Summary
SQLBindParamet= er binds a buffer to = a parameter marker in an SQL statement. SQLBindParameter supports = binding to a Unicode C data type, even if the underlying driver does not su= pport Unicode data.

https:= //docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindparameter-funct= ion?view=3Dsql-server-ver15

 

Data Type Conversions

https://docs.microsoft.com/en-us/sql/o= dbc/reference/develop-app/data-type-conversions?view=3Dsql-server-ver15

 

=  

 

 

From: Jon Raiford
Sent: Tuesday, November 23, 20= 21 9:00 AM
To: Farid z; = pgsql-odbc@postgresql.org<= br>Subject: Re: Bug report: odbc driver does not convert wide chars = to chars

 

First of all, I should say that I am not one of the pgsql-odbc driver= developers so I do not speak for them. I only speak for myself.=

 

I sugges= t you reference the documentation that supports your case, specifically in = regards to using WCHAR types with the A / narrow API calls.  Clearly t= he value has or has been converted to 16-bit characters and is using the fi= rst null as an end of string marker.  It isn=E2=80=99t clear from your= description if the issue is in your code or understanding of the spec, the= driver manager, or the ODBC driver.  I would suggest that saying simp= ly that the MySQL driver works for your scenario may not be enough evidence= .

 

Alternatively, the driver code is available and you may decide to find = and fix the issue and submit a patch. Many of us have done that but is defi= nitely not required.

 

Best of luck to you and maybe someone will be able t= o help you.

 

Jon

 

<= div style=3D'border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0= in 0in'>

From: Farid z <= ;farid@zidsoft.com>
Date: Monday, November 22, 2021 at 7:07 PM=
To: Jon Raiford <raiford@labware.com>, "pgsql-odbc@po= stgresql.org" <pgsql-odbc@postgresql.org>
Subject: RE:= Bug report: odbc driver does not convert wide chars to chars

 

Client ODBC apps are supposed to use the narrow or wide ODBC A= PI depending on whether the client app is a narrow or wide app on Windows (= A or W versions of the Windows/ODBC API).

 

Since my app is a narrow Window= s 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/n= arrow and Wide versions of a driver to match the API narrow/wide ODBC API c= alls of the drivers.

 

The ANSI/wide determines what version of the ODBC AP= I is called (narrow or wide,

ie, SQLBind= ColA 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 a= nd wchar_t data types and ODBC Drivers are required to convert from bound p= arameter data type to target dbms column data type as necessary.=

 

When an = app (whether the app uses narrow or wide version of the ODBC API) binds a p= arameter/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 inse= rting the data into the dbms. This conversion of data types as necessary ha= s nothing to do with whether the ODBC driver is A or W).

 

So it is very co= nfusing 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 d= ata to wide data to match the ODBC Driver Wide API interface.

 

I tested th= e 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 b= uffers as it relays the client data to the ODBC driver).

 

So, this is a bu= g in the PostgreSQL ANSI ODBC driver. But I guess, it has a workaround with= a severely degraded ODBC driver performance.

 

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 O= DBC spec says client apps can use any valid ODBC data types and the ODBC dr= iver is supposed to convert the data types as necessary.

 

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 p= erformance by forcing native Windows UTF-8/narrow client apps to have to us= e the wide version of the PostgreSQL ODBC driver to work with other DBMS&nb= sp; wchar_t data types (like MS SQL Server, Oracle, etc, wide char data typ= es).

 

I would recommend fixing the PostgreSQL ODBC ANSI(narrow) driver to = work with native narrow windows client apps without always incurring unnece= ssary conversions by the ODBC driver manager of the narrow client ODBC apps= all data to wide chars.

 

Farid

 <= o:p>

CompareData  = Compare and synchronize sql dbms data visuall= y

<= span style=3D'font-size:8.0pt'>Strobe  Strobe light for your phone

 

<= p class=3DMsoNormal>From: Jon= Raiford
Sent: Monday, November 22, 2021 3:31 PM
To: pgsql-odbc@postgresql.org
Cc: Farid z
Subj= ect: Re: Bug report: odbc driver does not convert wide chars to chars

 

This does not sound like a bug. I=E2=80=99m sure that you will find = that you are using a Unicode version of MySQL and MariaDB ODBC drivers.&nbs= p; Is there a reason you are not using the Unicode Postgres driver?  I= f you insist on using the ANSI driver, I would suggest not trying to use th= e Unicode =E2=80=9CW=E2=80=9D (wide char) functions or data types.  Bu= t really, I think your answer is to just use the Unicode driver.  I ho= nestly don=E2=80=99t know why the ANSI driver is still being distributed, b= ut I suppose there are still a few people out there who need it.=

 

Jon=

 

From: Farid z <farid@zidsoft.com>=
Date: Monday, November 22, 2021 at 2:45 PM
To: Jon Rai= ford <raiford@labware.com>
Subject: RE: Bug report: odbc dr= iver does not convert wide chars to chars

<= p class=3DMsoNormal> 

Right, = I think that=E2=80=99s what=E2=80=99s happening.

 

App is UTF-8 app code-pa= ge on Windows.

App is telling the drive= r 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 i= s not ambiguous.

 

This works as expected with other ODBC drivers like MySQ= L and MariaDB.

 

Farid

 =

CompareData  Compare and synchronize sql dbms data visually

Strobe &nbs= p;Strobe light for your phone

 

From: Jon Raiford
Sent: Monday, November 22, 2021 9:01 AM
To: Farid z
Subject: Re: Bug report= : odbc driver does not convert wide chars to chars

 

It looks like yo= u are using the ANSI driver (PSQLODBC30A) rather than the Unicode driver (P= SQLODBC35W).  The ANSI driver likely sees the first null and assumes t= he end of the string has been reached.  I would suggest trying again w= ith the Unicode driver.

 

Jon

 

From: Farid z <farid@zidsoft.com>
Date: Sunday, November 21, 202= 1 at 12:46 PM
To: "pgsql-odbc@postgresql.org" <pgsql= -odbc@postgresql.org>
Subject: Bug report: odbc driver does no= t 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 d= bms that supports SQ_WCHAR/SQL_WVARCHAR) data types) to PostgreSQL.

 

Steps= to reproduce:

 

1 create SQL Server table source data table:

 

create tabl= e test_char(

col1 nchar(8),

col2 nvarchar(30));

 

2 create PostgreSQL table targ= et table:

 

create table test_char(

col= 1 char(8),

col2 varchar(30));=

 

3 add te= st data in MS SQL Server table:

 

insert into test_char

values

(N'a', N'a');

 

i= nsert into test_char

values

(N'bb', N'bb');

&= nbsp;

insert into test_char

values

(N'ccc', N= 'ccc');

 

insert into test_char

values

(N'ffffffff', N'ffffffff');

 

4 applicati= on binds the two columns as SQL_C_CHAR and SQL_C_WCHAR (excerpt from attach= ed log).

 

cmpdata         6be8-2= 9f4 EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)=

       &n= bsp;       HSTMT     = ;          0x000000000591E5D0<= o:p>

      &nbs= p;        UWORD     =             &nb= sp;      1

            &= nbsp;  SWORD         &nbs= p;            &= nbsp; 1 <SQL_PARAM_INPUT>

 &n= bsp;            = ; SWORD           &n= bsp;           -8 <SQL= _C_WCHAR>

    &nb= sp;          SWORD  =             &nb= sp;         1 <SQL_CHAR>=

       &n= bsp;       SQLULEN    &nb= sp;            =    8

    &= nbsp;          SWORD &nbs= p;            &= nbsp;         0

         &nbs= p;     PTR       &nb= sp;        0x0000000000411178=

        &n= bsp;      SQLLEN     &nbs= p;            &= nbsp; 18

     &= nbsp;         SQLLEN *  &= nbsp;         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 da= ta is just the first letter of each bound value.

 

=E2=80=98b=E2=80=99 inst= ead of =E2=80=98bb=E2=80=99, =E2=80=98c=E2=80=99 instead of =E2=80=98ccc=E2= =80=99, =E2=80=98f=E2=80=99 instead of =E2=80=98ffffffff=E2=80=99.

 

 =

Please see attached log with insert sta= tements and screen shots.

 

Farid

 =

 

CompareData=  Co= mpare and synchronize sql dbms data visually

S= trobe  Strobe light for your phone

 

 

 

 =

=