Use SQL Server with PHP Cake : Encoding problem

Question

I have to use MS SQL Server 2008 to store data of PHP CAKE application.

I successfully installed the Microsoft's PDO driver for PHP and the dbo datasource of php cake.

My application communicate with the server.

But I've some problem with encoding and I don't know how to manage.

I made some research and it appears that SQL server doesn't really support utf8 for varchar fields(if I correctly understood).

SQL Server tells me, with the "SELECT SERVERPROPERTY('Collation')" query, that I've the French_CI_AS encoding(which seems to be correct).

I made some search php cake config, and it appears that we can specify an "encoding" parameter for the database config (in app/config/database.php). I tried to set it to the French_CI_AS, and utf8, but it doesn't change anything, I still have unrecognized caracters on my page.

I'm not sure that the sql server data source is actually using this parameter, so I searched into the dbo_sqlsrv.php, and I found a "charset" property, so I set it to "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", but now I've another error, cake doesn't find anymore my table:

Warning (2): sqlsrv_query() expects parameter 1 to be resource, boolean given [CORE\cake\libs\model\datasources\dbo\dbo_sqlsrv.php, line 185] Warning (512): SQL Error: An invalid parameter was passed to sqlsrv_query. [CORE\cake\libs\model\datasources\dbo_source.php, line 684] Query: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
Missing Database Table Error: Database table moniteurs for model Moniteur was not found. Notice: If you want to customize this error message, create app\views\errors\missing_table.ctp

But if I run myself this command, I clearly see this table:

TABLE_NAME

garagistes

moniteurs

(2 row(s) affected)

and like you can see, I don't even have any non-ascii char in my table names.

So I'm little lost and I don't know where to search now, do you have any idea?

Thank you!

J4N J4N · Accepted Answer · 2011-07-15T08:50:43

I finally found the solution!

I had to convert all my fields in nvarchar instead of varchar, and set the charset of my php database configuration file to "UTF-8"(the "-" is matters):

var $default = array(
        'driver' => 'sqlsrv',
        'host' => 'localhost',
        'login' => 'myUser',
        'password' => 'myPassword',
        'database' => 'myDatabase',
        'prefix' => '',
        'charset'=>'UTF-8'
    );

Now everything appears in the correct encoding!

Use SQL Server with PHP Cake : Encoding problem

TABLE_NAME

1 Answers