PDOException: SQLSTATE[IMSSP]: error occurred translating string to UCS-2, using drupal 7 at azure and sql azure backend

I recently started setting up a drupal 7 website on Azure cloud using a web role and SQL azure backend. I was very much pleased until I hit a character set brickwall.

I have a virtual machine with Windows Server 2008 R2, IIS 7.5, SQL Server 2008 R2 as a development server, trying changes before I upload them to the cloud. I have some extra tables in drupal database to support my custom module for the application. Among others, at some point I need to run some code like that:

$r = db_select('my_creators', 'c')
    ->fields('c')
    ->condition('my_url', $creator_url)
    ->execute()
    ->fetchAll(PDO::FETCH_ASSOC);

$creator = $r[0];

foreach ($creator as $k => $vv)
    $s.= t('@key -->> @value<br>', array('@key' => $k, '@value' => $v));

print $s;

I have cut out a lot of extra code, but this is the code having the problem, when $creator_url has a value with some special character (e.g. André_Breton). This runs perfectly on my development machine, fetching the record and displaying the data so I was very surprised when I uploaded to the cloud and got this error

PDOException: SQLSTATE[IMSSP]: An error occurred translating string for input param 7 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page. in dblog_watchdog() (line 157 ofE:\sitesroot\0\modules\dblog\dblog.module).

The underlying databases are identical, actually I data-sync the sql azure database to the development sql server database daily. All character data fields are nvarchar.

I cross-checked all versions of IIS, php, php extensions, php.ini and all are identical.

I managed to kind of work around this by adding

Database::getConnection()->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);

$creator_url = iconv('UTF-8', 'UCS-2', $creator_url);

before the select, and then for the print

foreach ($creator as $k => $vv) {
    $v=iconv('ISO8859-1', 'UTF-8', $vv);

    $s.= t('@key -->> @value<br>', array('@key' => $k, '@value' => $v));
}

Using this workaround the select goes through, the correct record is selected and displayed, but then I get several warnings

Warning: htmlspecialchars(): Invalid multibyte sequence in argument in check_plain() (line 1572 ofE:\sitesroot\0\includes\bootstrap.inc). Warning: htmlspecialchars(): Invalid multibyte sequence in argument in check_plain() (line 1572 ofE:\sitesroot\0\includes\bootstrap.inc).

which makes sense since usually drupal functions expect all strings to be UTF-8.

I am hitting my head against a wall for a couple of days now, any help will be deeply appreciated.

Thank you!

Solution

I am not familiar with Drupal or PHP. But according to http://blogs.msdn.com/b/qingsongyao/archive/2009/04/10/sql-server-and-utf-8-encoding-1-true-or-false.aspx, SQL Server (and similarly SQL Azure) doesn’t stand by UTF-8. It supports UTF-16. In most cases, SQL Server will automatically convert the encoding. However, if a character exists in UTF-8 but not UTF-16, the issue may be encountered. In this case, please try to escape this character. For example, you may define a rule so that “/myspecialcharacter” corresponds to a special character that does not exist in UTF-16. I would like to suggest you to escape/unescape on the PHP side.

Best Regards,

Ming Xu.