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 1mpQQs-0006oq-OX for pgsql-odbc@arkaria.postgresql.org; Tue, 23 Nov 2021 07:41:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mpQQr-0007fI-BA for pgsql-odbc@arkaria.postgresql.org; Tue, 23 Nov 2021 07:41:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mpJL2-0006nc-Kb for pgsql-odbc@lists.postgresql.org; Tue, 23 Nov 2021 00:07:08 +0000 Received: from mail-io1-xd2e.google.com ([2607:f8b0:4864:20::d2e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mpJKv-0005WU-3K for pgsql-odbc@postgresql.org; Tue, 23 Nov 2021 00:07:07 +0000 Received: by mail-io1-xd2e.google.com with SMTP id k21so25663968ioh.4 for ; Mon, 22 Nov 2021 16:07:00 -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=MR/Zy4wG/IK5JEfrm4+SSnqMbLQmTxn0P/wWg/gFnUI=; b=AHnobhz06Z3hsrMaW5SVZbXPf+MxgIli7x+QMDrj8KucQwcVSVFXqZOc9EmwQMKuuZ TyxdnAqKmFjvkLdYyF3W3080Cn/yb+syng1xtDljWPFq4P76wORbTdMIZTL8jgjMYLgI dzTT6UKRTITj9LiFAhabAFXUU2pAW7qYJYfwRjw+xcFjYp+Kf08TwY8+AqMi8uASX3ZB WuiBjwrNCnLEDhA1zhP0Oltl3RVIUNHqCPg7mgkFpWY/iRZFdgcM3MNpnEh0StO4nN2S P2PWWDq0zp1EfqifHgCeIkrWTpxsw7OjimNYksyqomFhVnyiyzaHGQlgbxObUuv1wiiq zxig== 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=MR/Zy4wG/IK5JEfrm4+SSnqMbLQmTxn0P/wWg/gFnUI=; b=imnp0eTKSqchRwe7SAvwUi9Mvwvw/kc8x9phc0hNjQ22DPhovh/m+sjjrySITS0/ms KtWFcffRLuP1QOMMCp+pZqkIl7aDKs7UXmt36xkH/kUMbgqG9Dzsi5HDdMN/xAEyAMho xCStPYpsuJKXQq8O2qJeobtJnM2INiruPVDp/esYAE02ohZ5842kuDO0ZsIZY2cRhNBO /KbcFnPxC7bfN0X4MfhrABXVTnee9qXrG5s6CRPhSZdHDyHUmN4Culq3TR+LEsrWd7kG prZGwU/f7GQwqENDz6kkjciTVWV4VbUP2SLS7dZrC6tcCtFRcHeKKMEXbJOLcUt4OJNj PJUg== X-Gm-Message-State: AOAM531buQBuky0jCln4KfDwYZ8UCxFESsFB/V8+ijyJAJ5Yxt7wa5fN jP9Fw5JprU+wZSxMo6uwecoKuzbn1qIE8RLg X-Google-Smtp-Source: ABdhPJxYYe78hBJkvnNx52QdBAHlb7iAuTQrb8GGV23nbjt8kIzQqmMwr428TU6Udk46rE+7rKBpjw== X-Received: by 2002:a05:6602:3ca:: with SMTP id g10mr1065183iov.194.1637626019591; Mon, 22 Nov 2021 16:06:59 -0800 (PST) Received: from zid1 ([172.58.222.174]) by smtp.gmail.com with ESMTPSA id a12sm3846633iow.6.2021.11.22.16.06.58 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 22 Nov 2021 16:06:59 -0800 (PST) MIME-Version: 1.0 Date: Mon, 22 Nov 2021 19:06:54 -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: <0CDC5606-43A9-4896-807E-FEA1FD50DFFF@labware.com> 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

Clien= t ODBC apps are supposed to use the narrow or wide ODBC API depending on wh= ether 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 Windows app (uses narrow windows an= d 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.

 

The ANSI/wide det= ermines what version of the ODBC API is called (narrow or wide,

ie, SQLBindColA or SQLBindColW, etc) and has nothing to do wit= h what actual data types the app uses. Client ODBC apps can use any ODBC da= ta 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.

 

When an app (whether the app uses narrow or wide version of the ODBC A= PI) 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).

 

So it is very = confusing to make a narrow Windows client API app connect with W ODBC drive= r where there is also a narrow version of the driver. This would at a minim= um require the ODBC driver manager to convert perfectly valid narrow client= data to wide data to match the ODBC Driver Wide API interface.

 

I tested the Postgre= SQL Unicode version of the ODBC driver and it worked in this case. However,= the driver performance is significantly reduced by over 50% (ODBC Driver M= anager has to convert all the client narrow data buffers to wide buffers as= it relays the client data to the ODBC driver).

 

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.

 

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 u= se any valid ODBC data types and the ODBC driver 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 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=C2=A0 wchar_t data types (like MS SQL Server= , Oracle, etc, wide char data types).

 

I would recommend fixing the PostgreSQL ODBC A= NSI(narrow) driver to work with native narrow windows client apps without a= lways incurring unnecessary conversions by the ODBC driver manager of the n= arrow client ODBC apps all data to wide chars.

 

Farid

&n= bsp;

CompareData  Compare and synchronize = sql dbms data visually

St= robe  Strobe light for your phone

 

From: Jon Raiford
Sent: Monday, Novembe= r 22, 2021 3:31 PM
To: pgsql-odbc@postgresql.org
Cc: Farid z
Subject: Re: Bug report: odbc driver does no= t 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 Maria= DB ODBC drivers.  Is there a reason you are not using the Unicode Post= gres driver?  If you insist on using the ANSI driver, I would suggest = not trying to use the Unicode =E2=80=9CW=E2=80=9D (wide char) functions or = data types.  But really, I think your answer is to just use the Unicod= e driver.  I honestly don=E2=80=99t 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@zidsoft.com>
Date: Monday, November 22, 2021 at 2:45 PM<= br>To: Jon Raiford <raiford@labware.com>
Subject: RE= : Bug report: odbc driver does not convert wide chars to chars<= /o:p>

 

Right, I think that=E2=80=99s what=E2=80=99s happening.<= /o:p>

 

App= is UTF-8 app code-page on Windows.

App= is telling the driver the data buffer is SQL_WCHAR, driver needs to conver= t 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 da= ta buffers data type to dbms column data type as necessary. Converting from= wide-char to UTF-8 is not ambiguous.

&n= bsp;

This works as expected with other O= DBC drivers like MySQL and MariaDB.

&nbs= p;

Farid

 

CompareData  Compare and synchronize sql dbms d= ata visually

Strobe  Strobe light for your= phone

 

From: Jon Raiford
Sent: Monday, November 22, 2021 9:01 AM<= br>To: Farid z
Subjec= t: 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 fir= st null and assumes the end of the string has been reached.  I would s= uggest trying again with the Unicode driver.

 

Jon

 

From: Farid z <farid@zidsoft.com>
Date: S= unday, November 21, 2021 at 12:46 PM
To: "pgsql-odbc@postgre= sql.org" <pgsql-odbc@postgresql.org>
Subject: Bug repo= rt: odbc driver does not convert wide chars to chars

<= /div>

 

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:

&n= bsp;

1 create SQL Server table source da= ta table:

 

create table test_char(

col= 1 nchar(8),

col2 nvarchar(30));

 

2 crea= te PostgreSQL table target table:

 =

create table test_char(

<= p class=3DMsoNormal>col1 char(8),

col2 v= archar(30));

 

3 add test data in MS SQL Server table:

 

insert into test_c= har

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'ff= ffffff');

 

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

 <= o:p>

cmpdata     &nb= sp;   6be8-29f4 EXIT  SQLBindParameter  with return cod= e 0 (SQL_SUCCESS)

   &nbs= p;           HSTMT &= nbsp;           &nbs= p; 0x000000000591E5D0

   =             UWORD&nb= sp;             = ;          1 <= /p>

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

          &= nbsp;    SWORD       &nbs= p;            &= nbsp;  -8 <SQL_C_WCHAR>

 = ;            &n= bsp; SWORD           = ;             1= <SQL_CHAR>

   &nbs= p;           SQLULEN = ;            &n= bsp;      8

&nb= sp;            =   SWORD          &nb= sp;            = 0

      =          PTR    = ;            0x00000= 00000411178

    &nbs= p;          SQLLEN  =             &nb= sp;     18

 &nb= sp;            = SQLLEN *           = 0x0000000000411170 (-1)

 

5 drivers successfully executes the inserts stat= ements 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.<= /o:p>

 

=E2= =80=98b=E2=80=99 instead of =E2=80=98bb=E2=80=99, =E2=80=98c=E2=80=99 inste= ad of =E2=80=98ccc=E2=80=99, =E2=80=98f=E2=80=99 instead of =E2=80=98ffffff= ff=E2=80=99.

 

 

Please see attache= d log with insert statements and screen shots.

 

Farid

 

 <= /p>

CompareData  Compare and synchronize sql dbms data visually

Strobe &nbs= p;Strobe light for your phone

 

 

 

=