Page 1 of 1
Unicode Translation in SQL SERVER
Posted: Thu 19 Sep 2013 18:14
by dadeishk
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?
Re: Unicode Translation in SQL SERVER
Posted: Fri 20 Sep 2013 10:21
by AndreyZ
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;
Re: Unicode Translation in SQL SERVER
Posted: Fri 20 Sep 2013 18:38
by dadeishk
Thank you. I see now I was approaching this backwards.
Re: Unicode Translation in SQL SERVER
Posted: Mon 23 Sep 2013 08:57
by AndreyZ
Feel free to contact us if you have any further questions about SDAC.
Posted: Mon 23 Sep 2013 21:46
by dadeishk
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.
Re: Unicode Translation in SQL SERVER
Posted: Wed 25 Sep 2013 09:15
by AndreyZ
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;