problems with charset

[andy.durrer]

hello community

I've installed sugar on a windows 2k3 server with a ms sql 2005 database. it works well. but I have a problem or a question about the charset. the data will stored in "strange" format. I'm from switzerland and our default charset is ISO-8859-1. we have some special characters like: äöüéèà..... on the sugar portal are these characters corect. but in the database looks these characters like: ü³õ

now my question, can I store the data in our format in the database? or where can I change the charset for the page.

thanks for any infromation

cheers andy

[clee]

Hi Andy,

There's a bunch of stuff on the web out there written about this issue, but it's not well summarized. I hope I can shed some light from what I have found in this post. The database data inconsistencies you're seeing are a known issue with PHP and the existing mssql.dll driver. The gist is this: the mssql.dll driver uses the Microsoft dblib libraries to communicate with SQL Server. These drivers will not store UTF-8 data (which PHP uses internally) as is. What happens is that SQL Server uses UCS-2 encoding and so there is an increase in byte size to store the data. To absolve this problem there are currently four known solutions:

1) Use FreeTDS driver http://www.freetds.org/. Check out Moodle's code. They did something similar to wrap adodb layer libraries with FreeTDS. You also have to convert the varchar, char, text columns to nvarchar, nchar, ntext respectively. Also, your SQL will have to change to use a prepended hint in statements for these columns. Example would be:

insert into accounts(id) values (N'xxxxxxxxx')

2) Use ODBCTP. This requires a sort of client/server program and is used to solve issues where the app server may be running on a Linux flavor OS and needs to communicate to SQL Server on a Windows OS through ODBC. This requires additional services running on the system.

3) Use the ADO layer libraries and make database/SQL changes as in the FreeTDS solution. Our investigation has shown that this is very slow howerver as there seems to be a double translation layer.

4) Wait for PHP6. PHP6 is supposed to change their encoding of characters from UTF-8 to have better handling for internationalization support.

Currently, we are investigating FreeTDS to address this. We have had some success with it, but need to rigourously test the performance. Also, data migration issues of existing data encoded with the existing mssql.dll libraries are a pain.

Other links that document this known PHP limitation with mssql.dll can be found here:

http://bugs.php.net/bug.php?id=18169

http://support.microsoft.com/kb/232580/EN-US/

Hope this helps.

-Collin

[andy.durrer]

Hi Collin

Thanks for your help.

At the weekend, I've installed a MySQL to MS SQL Converter. It works well. That tool imported and converted all my data with the correct charset. All my special characters are now correct in my MS SQL database. Now I have the problem, the data showing wrong on the webpage.
I will serch now more information about "moodle's code". And I hope, I find a solution with your information.

Greez Andy

[andy.durrer]

Hi again

Well, I found a solution for my problem.
On the page load event, I send a HTML Header to the client, with my charset defintion. It works for reading and writing.
All a did, is insert following code line in the index.php on the root directory of sugar:

header("Content-type: text/html; charset=ISO-8859-1");

I had insert this code on the 2nd last line of the index.php.

Collin, thanks again for your information.

-Andy

[clee]

Andy,

I'm glad to hear you got something working. If your application will be confined to use Swiss language only then you may be okay with this fix. Have you tried with other multibyte character sets (e.g. Japanese, Chinese)? I'm guessing there may be a problem then. It seems the right solution with SQL Server is to use the nvarchar, nchar, etc. columns for applications that will store multibyte (non-ascii) data.

-Collin

[andy.durrer]

Yes, we use (at the moment) the Sugar CRM only in the switzerland. But if I have more time to trying, then I will looking for a better solution.

-Andy