Unicode Translation in SQL SERVER

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dadeishk
Posts: 5
Joined: Wed 06 Jun 2012 19:36

Unicode Translation in SQL SERVER

Post by dadeishk » Thu 19 Sep 2013 18:14

I'm using SQL SERVER 2005 and I've stored “hello World” (with special quote characters) in a table as nvarchar. I'm using UniConnection/UniQuery to access the record and display it on a webpage (charset=UTF-8). The text appears as ?hello world?.
Is this because SQL Server encodes to UTF-16?
I had a similar issue accessing and displaying text from a MYSQL database which was easily remedied by using

Code: Select all

SpecificOptions.Values['MySQL.CharSet']:='UTF8' 
However, this is not one of the specific options available for the SQL SERVER provider. Is there an option that I'm missing?

AndreyZ

Re: Unicode Translation in SQL SERVER

Post by AndreyZ » Fri 20 Sep 2013 10:21

SQL Server uses the UTF-16 (UNICODE UCS-2) character set for the nchar and nvarchar data types. You can find more detailed information about this here:
http://msdn.microsoft.com/en-us/library/ms143726.aspx
http://msdn.microsoft.com/en-us/library/ms186939.aspx
http://support.microsoft.com/kb/232580
As you can see in the article in the last link, you can avoid this problem by translating from UCS-2 to UTF-8 within the application. Therefore, you should use the UTF8Encode method, which uses the WideCharToMultiByte Win32 function. Here is a code example:

Code: Select all

var
  rbs: RawByteString;
begin
  rbs := UTF8Encode(UniQuery.FieldByName('fieldname').AsString);
  // now rbs contains the string in the UTF-8 character set, which you can use on your webpage
end;

dadeishk
Posts: 5
Joined: Wed 06 Jun 2012 19:36

Re: Unicode Translation in SQL SERVER

Post by dadeishk » Fri 20 Sep 2013 18:38

Thank you. I see now I was approaching this backwards.

AndreyZ

Re: Unicode Translation in SQL SERVER

Post by AndreyZ » Mon 23 Sep 2013 08:57

Feel free to contact us if you have any further questions about SDAC.

dadeishk
Posts: 5
Joined: Wed 06 Jun 2012 19:36

Post by dadeishk » Mon 23 Sep 2013 21:46

It appears that the IDE and basic String type in Lazarus are suited to UTF-8. I was successful in getting unicode data from SQL Server 2005 with:

Code: Select all

var AWideString:WideString;FieldValue:String;
AWideString:=DataRS.fieldbyName('data').AsWideString;
FieldValue:=UTF8Encode(WideFieldValue);
Hovering over AWideString or FieldValue shows the 漢 character stored in the variable. So retrieving the data in UTF-8 is not a problem.

However, I'm still having problems setting SQL statement of a UniQuery object which contains international characters.
I tried this without success.

Code: Select all

//DataRS is of type TUniQuery
WideSQLStatement:=UTF8Decode(SQLStatement);
DataRS.sql.text:=WideSQLStatement;

All international characters are replaced with ?'s. The problem might be that TUniQuery.SQL is of type TStrings, which I think only stores ANSI text.
Last edited by dadeishk on Fri 27 Sep 2013 15:12, edited 1 time in total.

AndreyZ

Re: Unicode Translation in SQL SERVER

Post by AndreyZ » Wed 25 Sep 2013 09:15

This is a known bug of FPC. To avoid the problem, you should add the paswstring unit to the USES clause of your unit. After this, you can use Unicode characters in the following way:

Code: Select all

UniQuery.SQL.Text:='SQL statement with Unicode characters';
UniQuery.Open;

Post Reply