Problem with regional characters (polish) in php application and ms sql server

Question

I have a problem with regional string characters inserted to MS SQL Server database.

There is a PHP application that connects with mssql server and inserts some data. But instead of inserting characters such as: ą, Ą, ć, Ł, ź (and so on - btw. these are polish regional characters), when inserted into mssql table they appear as a, A, c, L, z.

Here is some background:

• I use freetds drivers for mssql connectivity
• column type is nvarchar(MAX)
• I looked at data sent "trough wire" (with WireShark) and the UTF-8 encoded data looks ok, for example Ą is sent as U+0104.
• When inserting same string into local database instance (Microsoft SQL Server Express Edition) it works fine, but on remote host (in customer location - it is Microsoft SQL Server Standard Edition 64-bit) this "de-regionalization" occurs.

It seems like this remote mssql server doesn't handle this input data sent by php application right. Can anyone see/know what can be wrong here?

Answer 1

You can use this workaround, use htmlentities function before insert to database and html_entity_decode function after retrieve data from database. You can use ISO-8859-2 or Windows-1250 charset.

Answer 2

I found the soruce of this problem:

"When SQL Server converts a Unicode string without the N prefix from Unicode to the SQL Server database's code page, any characters in the Unicode string that do not exist in the SQL Server code page will be lost."

http://support.microsoft.com/kb/239530

I suppose the local instance of sqlserver in some way treats all incoming string literals as Unicode strings and that remote server doesn't, thus requiring N prefix.

Maybe it was a rocky mistake (not knowing about N prefix necessity), but I work with mysql on daily basis not mssql.