Sql-server – VARCHAR memory buffer appears to be breaking in ChineseTaiwan Collation

collationsql server

It is difficult for me to write something in English. I hope you understand.
I used a translator and reviewed it.

I created two temporary databases. The collations are 'Korea_Wansung_CI_AS' and 'Chinese_Taiwan_Stroke_CI_AS' respectively.

I created a temporary table with a column of type VARCHAR(2048) and I just created a procedure using INSERT for this table data here.

When invoking the stored procedure from a server that uses c++ oledb, the VARCHAR value is encrypted and sent as UTF-8.

We sent

  • '한국어' -> 'Korean_Wansung'¨
  • '韓國人學台' -> 'Chinese_Taiwan'.

I have verified through debugging that the value is encrypted with normal UTF-8.

  • '한국어' -> UTF-8 -> '\xed\x95\x9c\xea\xb5\xad\xec\x96\xb4'

  • '韓國人學台' -> UTF-8 -> '\xe9\x9f\x93\xe5\x9c\x8b\xe4\xba\xba\xe5\xad\xb8\xe5\x8f\xb0'

Here I have a problem.

When the table was read back via SELECT statement in the database, we found that for Korean_Wansung, the string 'matched by the value of the Code Page 949' appears normally.

But not for Chinese_Taiwan. The value stored in memory itself appears to be broken.

If you take the value from the server and decode it, the original character will appear normal for Korean_Wansung.

But in the Chinese case, it doesn't come out normally.

I need your help.

If you need additional information, we will quickly get back to you and upload it.

Thank you.

Best Answer

You need to ensure the strings (stored procedure parameters) are sent NVARCHAR

DECLARE @CollationTest table (
     ID int NOT NULL,
     KoreanV varchar(2048) COLLATE Korean_Wansung_CI_AS NULL ,
     KoreanNV nvarchar(2048) COLLATE Korean_Wansung_CI_AS NULL ,
     ChineseTaiwanV varchar(2048) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
     ChineseTaiwanNV nvarchar(2048) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL 
    )

INSERT @CollationTest VALUES (1,
    '한국어',
    N'한국어', 
    '韓國人學台', 
    N'韓國人學台'
)

INSERT @CollationTest VALUES (2,
    N'한국어',
    N'한국어', 
    N'韓國人學台', 
    N'韓國人學台'
)
SELECT * FROM @CollationTest
Related Question