SQLServer: Critical bug with unicode characters

General discussion forum about all databases other than SqlBase.
Nils Jänicke
Germany
Posts: 413
Joined: 20 Sep 2017, 11:56
Location: Villingen-Schwenningen, Germany

SQLServer: Critical bug with unicode characters

Post by Nils Jänicke » 06 Feb 2017, 18:34

Hi

On SQLServer (testet on multiple actual versions 2012 - 2016) the INSERT command looses unicode characters on bind variables if any datafield is filled using the CONVERT function.
See the test case. Log into a SQLServer database via ODBC, create the test table by clicking the button and then test the INSERT only with bind variables and the other one with CONVERT function. You can see that another bind variable looses unicode characters if any CONVERT function is used even if it is used on another bind variable.

Best regards
Nils


My output:
Connected
CREATE TABLE TestUnicode (n1 int, s1 nvarchar(254), m1 nvarchar(max))
INSERT INTO TestUnicode (n1, s1, m1) VALUES (:n1, :s1, :m1)
SELECT s1, m1 FROM TestUnicode INTO :s1, :m1 WHERE n1 = :n1
n1 = 1
s1 = Unicode: ...
m1 = Empty
INSERT INTO TestUnicode (n1, s1, m1) VALUES (:n1, :s1, CONVERT(nvarchar(max),:m1))
SELECT s1, m1 FROM TestUnicode INTO :s1, :m1 WHERE n1 = :n1
n1 = 2
s1 = Unicode: ??
m1 = Empty
DROP TABLE TestUnicode
Disconnected
You do not have the required permissions to view the files attached to this post.

Nils Jänicke
Germany
Posts: 413
Joined: 20 Sep 2017, 11:56
Location: Villingen-Schwenningen, Germany

Re: SQLServer: Critical bug with unicode characters

Post by Nils Jänicke » 14 Feb 2017, 12:48

Is this bug fixed with Update 1?

Mike Vandine

Re: SQLServer: Critical bug with unicode characters

Post by Mike Vandine » 15 Feb 2017, 07:00

Checking on this now, Nils.

Best regards,

Mike Vandine

Re: SQLServer: Critical bug with unicode characters

Post by Mike Vandine » 16 Feb 2017, 01:08

Hi Nils,

Some replies from Development:

------------------
I think this is application issue.
TD is passing Unicode string as bind variable to SQL Server and SQL Server converts its encoding to ASCII if data type of target column is VARCHAR. But in this case bind variable is used as argument of CONVERT function so that SQL Server client does not know about expected string encoding so that Unicode string is used for CONVERT function, I believe. This means that CONVERT function tries to convert Unicode string to Unicode string and generates corrupted string.

Using the CONVERT function to change string encoding works for TD 4.2 and previous version but should not be used for Unicode version of TD.
------------------
If the bind m1 were declared as a binary type would we not transcode? Might be worth a try.
------------------
Have them try using OLEDB to see if they get the same issue as ODBC
------------------

Please try these suggestions and let me know how you go.

Best regards,

Mike Vandine

Re: SQLServer: Critical bug with unicode characters

Post by Mike Vandine » 17 Feb 2017, 08:20

Hi Nils,

OK, some new findings:

1)using SQLNATIVE client v11 shows all is fine
2)using SQLSERVER v 10.0 shows the issue as customer reported it
3)OLEDB SQLNCLI11.1 is OK

Lastly, the latest version of ODBC Driver 12 for SQLSERVER 2015.130.811.168 IS OK

Conclusion most likely a third party bug, ask customer to using native or latest MS SQLSERVER ODBC driver or the native one ( I also tested using TD 7.0 RTM)

Hope this helps!

Best regards,

Mike

Nils Jänicke
Germany
Posts: 413
Joined: 20 Sep 2017, 11:56
Location: Villingen-Schwenningen, Germany

Re: SQLServer: Critical bug with unicode characters

Post by Nils Jänicke » 20 Feb 2017, 10:04

Hi Mike,

Thank you for the answer.
How can I use native client for SQL Server? I didn't find anything about thsi in the books. What settings are needed in sql.ini to use SQLNATIVE?

Best regards
Nils

Return to “General Discussion”

Who is online

Users browsing this forum: No registered users and 0 guests