Some notes on SQL Server blobs with sqlsrv

I recently updated my use of SQL Server with Zend_Db_Adapter_Sqlsrv to use UTF-8 throughout. This turned out to be easy enough:

• Use ntext, nvarchar types in the database
• add: resources.db.params.driver_options.CharacterSet = "UTF-8" to your application.ini

I subsequently noticed a problem with storing binary data to a varbinary(max) field. The error was:

An error occurred translating string for input param 2 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.

urgh!

The code looked something like this:

$data['filename'] = 'test.gif';
$data["file_contents"] = $binaryData;
$db->insert($data);

Fortunately, my friend Elizabeth Smith, pointed me in the right direction by suggesting I find out about bindings. So I did some research and it turns out that I just need to use an array for the parameter that I pass into the update() or insert() method of Zend_Db_Adapter_Sqlsrv.

It turns out that all you need to do is change the ‘file_contents‘ element of the array to an array that also specifies the data type. The code I ended up with now looks something like this:

$data['filename'] = 'test.gif';
$data["file_contents"] = $binaryData;
if ($adapter == 'Zend_Db_Adapter_Sqlsrv') {
$data["file_contents"] = array($binaryData, SQLSRV_PARAM_IN,
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'));
}
$db->insert($data);

And all is fine.

The list of constants for the Sql Server Driver for PHP is helpfully available on MSDN and the documentation for sqlsrv_query() is worth reading too!