PHP/PDO and SQL Server connection and i18n issues

Question

In our web-app we use PHP5.2.6 + PDO to connect to a SQL Server 2005 database and store Russian texts.

Database collation is Cyrillic_General_CI_AS, table collation is Cyrillic_General_CI_AS, column type is NVARCHAR(MAX).

We tried connecting to a database using two following schemes, both causing different problems.

1. PDO mssql:

   $dbh = new PDO ('mssql:host='.$mssql_server.';dbname='.$mssql_db, $mssql_login, $mssql_pwd);
   

   in which case a result of a simple query like that:

   SELECT field1 FROM tbl1 WHERE id=1
   

   shows field1 data truncated to 255 bytes.

2. PDO odbc:

   $dbh = new PDO ('odbc:DSN=myDSN;UID='.$mssql_login.';PWD='.$mssql_pwd);
   

   in which case a result of the same query shows full not truncated data but with question marks instead of Russian symbols.

---

Notes:

• In the SQL Management Studio data is not truncated and Russian symbols are displayed properly as well.
• We have Windows 2003 Enterprise Edition SP2

So what should we choose as a connection method and how to fix corresponding issues?

Answer 1

I noticed the same problem too, with an implementation of SQL server 2005 and PHP 5.x using PDO. When I changed nvarchar(MAX) field to nvarchar(255) the odd question mark characters stopped appearing. I definitely believe it has to do with the ODBC drivers in PDO and MS SQL server. When you implicitly specify the max characters in your varchar, it solves the problem.

Answer 2

If you are not set on PDO, use FreeTDS - aka procedural mssql_* calls. This is one of the recommended work arounds until PDO is fixed. Since PHP 5.1.2, FreeTDS has a mssql.charset-option.

Answer 3

I've always had the best luck using utf8_general_ci across the board - for connections, collations - everything.

However, I only have that experience with MySQL and PostgreSql - not with SQL Server.

As to your DSN question - I'm not sure.

Good Luck!

Answer 4

Try executing SET NAMES "charset" after you connect.

I don't know what the charset to match Cyrillic_General_CI_AS is, but try "Cyrillic"?

Answer 5

I had to do this to get usable data from my NVARCHAR fields in MSSQL:

$_ = iconv('Windows-1252', 'UTF-8', $_);