6

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.

migrated from stackoverflow.com Oct 16 '18 at 15:28

This question came from our site for professional and enthusiast programmers.

  • 1
    @minchul What version of SQL Server are you using? Also, what are the exact Collations that you are using? Are you sure that the "Chinese" DB has the correct default Collation? As in Chinese_Taiwan_Stroke_* or Chinese_Taiwan_Bopomofo_*? – Solomon Rutzky Oct 16 '18 at 15:57
  • Also, what is the exact hex value being returned by the SELECT from the Chinese_Taiwan DB that "appears to be broken"? Is the stored procedure that does the insert in both databases, inserting into the table in that same DB? What database are you connecting to for each stored procedure? Are you connecting to the same DB each time, executing the stored procedure using DBName.SchemaName.ProcName? Or are you connecting to the specific DB executing the procedure that exists in that DB? And what is the exact datatype used for the input parameter of the stored procedure? – Solomon Rutzky Oct 16 '18 at 16:14
9

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
  • Thanks! Thank you for correcting the typos. I understand If I wanna expression 'All Character', need using Unicode. but I wanna know why memory buffer broke? – minchul.kang Oct 15 '18 at 9:55
  • What did you see in the memory buffer? – gbn Oct 15 '18 at 10:02
  • please give me a time. I need a time for detail content uploaded. I will upload when tomorrow. – minchul.kang Oct 15 '18 at 10:37
  • @minchul.kang: Internally SQL Server uses UCS-2 (UTF-16) not UTF-8 stackoverflow.com/questions/9008043/ucs-2-and-sql-server – gbn Oct 16 '18 at 10:08
  • I couldn't renew it because I was busy at work. C++ delivers the memory buffer (oledb), which does not appear to apply exactly to the value of the VARCHAR in the DB. I am writing an example code and will update as soon as it is complete. I'm sorry I couldn't keep my promise. – minchul.kang Oct 16 '18 at 14:20
6

No, you do not need to ensure that stored procedure parameters are NVARCHAR. While there are advantages to using NVARCHAR for both parameter and column datatypes, the fact that your Korean text is stored properly in a VARCHAR column proves that NVARCHAR isn't necessary.

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

Until SQL Server 2019 (which was only released to the public as a beta a few weeks ago), SQL Server didn't support UTF-8 internally at all. So you might be supplying a UTF-8 value to the OLEDB driver, but that driver will translate the UTF-8 bytes into the equivalent bytes for the destination Code Page, which should be the Code Page used by the current database's default Collation. The "current" database is either the database you connect to (if no USE statement has been executed), or the database set by the most recent USE statement.

This means that even if the database's default Collation uses Code Page 950, and the destination column also has a Collation that uses Code Page 950, you might not get the correct data inserted if the "current" database at the time of the INSERT (or EXEC of a stored procedure containing the INSERT) has a default Collation that uses a Code Page that does not support all of the characters you are inserting.

I posted several questions in comments on the question, and answering those will greatly assist me in being able to provide a more specific answer / explanation (especially updating the question to include the exact bytes returned from the value that is not being inserted correctly). However, until then I can illustrate how this all works with the following query:

DECLARE @Test TABLE
(
  [Source] VARCHAR(50),
  [Value] VARCHAR(20) COLLATE Chinese_Taiwan_Bopomofo_90_CI_AS,
  [HexOfCP950] AS (CONVERT(VARBINARY(20), [Value])),
  [ValueAsUTF16] AS (CONVERT(NVARCHAR(20), [Value])),
  [HexOfUTF16] AS (CONVERT(VARBINARY(20), CONVERT(NVARCHAR(50), [Value]))),
  [NumBytes] AS (DATALENGTH([Value]))
);

-- The following 2 INSERTs work no matter what the current DB Collation is:
INSERT INTO @Test VALUES ('UTF-16 characters', N'韓國人學台');
INSERT INTO @Test VALUES ('CodePage 950 bytes', 0xC1FAB0EAA448BEC7A578);

-- The following 3 INSERTs simulate the effect of passing in 8-bit characters,
--  which depend on the current DB's default Collation:
INSERT INTO @Test VALUES ('CodePage 950 characters', CONVERT(VARCHAR(20), N'韓國人學台'
                                   COLLATE Chinese_Taiwan_Bopomofo_90_CI_AS));

INSERT INTO @Test VALUES ('CodePage 949 characters', CONVERT(VARCHAR(20), N'韓國人學台'
                                   COLLATE Korean_Wansung_CI_AS));

INSERT INTO @Test VALUES ('CodePage 936 characters', CONVERT(VARCHAR(20), N'韓國人學台'
                                   COLLATE Chinese_Simplified_Stroke_Order_100_CI_AS));

INSERT INTO @Test VALUES ('CodePage 932 characters', CONVERT(VARCHAR(20), N'韓國人學台'
                                   COLLATE Japanese_XJIS_100_CI_AS));

INSERT INTO @Test VALUES ('CodePage 1252 characters', CONVERT(VARCHAR(20), N'韓國人學台'
                                   COLLATE Latin1_General_100_CI_AS));


SELECT * FROM @Test;

That returns the following:

Source              Value      HexOfCP950              ValUTF16   HexOfUTF16           Bytes
UTF-16 characters   韓國人學台  0xC1FAB0EAA448BEC7A578  韓國人學台  0xD3970B57BA4E785BF053  10
CodePage 950 bytes  韓國人學台  0xC1FAB0EAA448BEC7A578  韓國人學台  0xD3970B57BA4E785BF053  10
CodePage 950 chars  韓國人學台  0xC1FAB0EAA448BEC7A578  韓國人學台  0xD3970B57BA4E785BF053  10
CodePage 949 chars  韓國人學台  0xC1FAB0EAA448BEC7A578  韓國人學台  0xD3970B57BA4E785BF053  10
CodePage 936 chars  韓國人學台  0xC1FAB0EAA448BEC7A578  韓國人學台  0xD3970B57BA4E785BF053  10
CodePage 932 chars  韓國人學台  0xC1FAB0EAA448BEC7A578  韓國人學台  0xD3970B57BA4E785BF053  10
CodePage 1252 chars     ?????  0x3F3F3F3F3F                ?????  0x3F003F003F003F003F00   5

So it seems that those Chinese characters are found in all 4 of the Double-Byte Character Sets supported by SQL Server. So this should be working for you. Since it is not working, you need to please answer the questions I posted.

If you are executing the stored procedure from a database that has a default Collation using a Code Page that does not support the Chinese characters, that could explain why they did not get stored correctly. But:

  1. we did see that all four Double-Byte Character Sets supported the Chinese characters, so it would have to be a non-Chinese, non-Korean, non-Japanese Collation,
  2. if the Korean characters are being inserted correctly, then that is being called from a different database than where the Chinese INSERT proc is being called from.

If the stored procedure to INSERT the Chinese text is in the Chinese database, then either:

  1. it is possible that the default Collation for the Chinese database is not actually a Chinese Collation, and/or
  2. it is possible that the Collation of the column in the table in the Chinese database is not actually a Chinese Collation.

It is also possible that the input parameter for the stored procedure is VARCHAR(6) when it needs to be VARCHAR(10) (at least) to hold the Chinese characters.

Also, are you encoding (not encrypting) non-UTF-8 data into UTF-8 just for the purpose of sending into SQL Server? If yes, then that is unnecessary.

Finally, if you are getting an error, you need to please provide the exact error message. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy