SQLServer: Critical bug with unicode characters

[Nils Jänicke]

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

[Nils Jänicke]

Is this bug fixed with Update 1?

[Mike Vandine]

Checking on this now, Nils.

Best regards,

[Mike Vandine]

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]

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]

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