json_encode produce JSON_ERROR_UTF8 from MSSQL-SELECT

Question

I have an annoying problem with a database query to mssql. If the result contains special characters like the german 'ä', I cannot use json_encode to get the result as json correctly.

json_last_error return 5 which is equal to JSON_ERROR_UTF8. I guess the database does not return the values as UTF-8 encoded. The database collection is *Latin1_General_CI_AS* and the affected columns are varchars.

The php mssql.charset configuration has no effect.

I read that mysql users could use mysql_query('SET CHARACTER SET utf8'); to encode the return values correctly. What can I do, to get the values correctly for mssql?

Hint - I cannot change the anything at the database.

Solution 1

Before you JSON encode, use utf8_encode() around the string.

Solution 2

You can just set this in your connection also:

$result = sqlsrv_connect($hostname, array(
    'UID' => $username,
    'PWD' => $password,
    'Database' => $database,
    "CharacterSet" => "UTF-8"   // <---- here the magic happens 
));

For me, this is the easer way.

Comments

[Andre Hofmeister]

It seams to be the best solution. Just iterrate throught the results and convert. Thanks for that hint.