Rob Allen's DevNotes

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!

A good proportion of the projects my company undertakes end up on Windows servers with IIS and SQL Server and hence we use the SqlSrv PHP extension from Microsoft. We don't host any of these projects ourselves and leave it up to the client's IT department. This is the main reason that we use a database abstraction layer, Zend_Db, in our case as we can swap the underlying database out with less hassle.

A couple of weeks ago, I came across a problem when installing our app onto the client's server.

It didn't work.

This was a surprise as we have a few Windows Server VMWare installations on which we had tested and they had worked fine. The most obvious differences were that this server was 32bit and that it was using v2 of the SqlSrv extension. As there were a number of differences from our usual fare to the client's install, so it took me a while to (a) build a install that exhibited the problem and (b) reduce the problem to something simple.

The error I was getting is this:

exception 'Zend_Db_Statement_Sqlsrv_Exception' with message 
'[Microsoft][SQL Server Native Client 10.0]String data, right truncation' 
in C:\Websites\project1\library\Zend\Db\Statement\Sqlsrv.php:237

Googling failed me completely!

I eventually narrowed down the SQL statement that caused the problem:

SELECT email_user_include_form_details FROM "forms"

That's not especially complicated :)

At the point, I yelled for help which came in the form of Brian Swan of Microsoft. There's quite a few helpful MS guys around nowadays if you keep your ear close to the PHP community, which is really handy. Brian helped prove that I wasn't going mad and that there really was a problem.

We determined the problem to be that with v2 of the sqlsrv driver, you cannot have a fieldname longer than 30 characters.

Brian assured me that the team are aware of this and it will be fixed in a subsequent update of the driver. Also, it only affects the sqlsrv driver and not the pdo_sqlsrv driver. Hence, there are two workarounds:

1. Shorten your fieldnames to 30 characters or less
2. Use pdo_sqlsrv

Smart readers will already be commenting that there isn't a Zend_Db_Adapter_Pdo_Sqlsrv and they'd be right, but it isn't hard to write one :)

Akrabat_Db_Adapter_Pdo_Sqlsrv

I cobbled together an adapter that works for me and have put it on github. As it's not in the Zend_ namespace, we have to use an additional key in the application.ini file so that it is loaded:

application.ini:

resources.db.adapter = "Pdo_SqlSrv"
resources.db.params.adapterNamespace = "Akrabat_Db_Adapter"
resources.db.params.host = localhostSQLEXPRESS
resources.db.params.username = testuser
resources.db.params.password = testpassword
resources.db.params.dbname = testdatabase

The key difference from a standard Zend_Db adapter is the use of the resources.db.params.adapterNamespace key which tells the system the full name of the class to load.

My limited testing shows that this adapter works with v2 of pdo_sqlsrv which solves my problem with fieldnames that are longer than 30 characters!

Unfortunately, I found out about this too late for Zend Framework 1.11, so I'll have to look at getting it into Zend Framework 2.0.

I've been reading the Proposal for Exceptions in ZF2 and like it. One thing that caught my attention was that it suggests that you can catch an interface. I hadn't heard of that ability before, so I pulled out my trusty text editor to have a play.

Consider this code:

<?php
namespace My; 
 
interface ExceptionInterface {} 
 
class SplExceptionClass extends InvalidArgumentException implements ExceptionInterface {} 
class ExceptionClass extends Exception implements ExceptionInterface {} 

class A {
    static function throwAnSplException()
    {
        throw new SplExceptionClass('oops');
    }
    static function throwAMyException()
    {
        throw new ExceptionClass('oops again');
    }
}

Within our My namespace, we have two exception classes and an exception interface that both classes implement. We also define a class \My\A as a vehicle for throwing the exceptions. This is the basis of how ZF2 exceptions will work without all the actual component implementations :)

Let's do some testing to look at what happens:

Test 1

try {
    A::throwAMyException();
}
catch (ExceptionClass $e) {
    echo "Caught \\My\\ExceptionClass\n";
}

As expected, this works. We catch the \My\ExceptionClass exception that was thrown.

Test 2

try {
    A::throwAnSplException();
}
catch (ExceptionClass $e) {
    echo "Caught \\My\\ExceptionClass\n";
}

As expected, we fail to catch \My\SplExceptionClass exception that was thrown as it is not related to the \My\ExceptionClass that we are trying to catch.

Test 3

try {
    A::throwAnSplException();
}
catch (ExceptionInterface $e) {
    echo "Caught \\My\\ExceptionInterface\n";
}

This time we are catching the \My\ExceptionInterface and it works! This surprised me and is very handy.

We now have the ability with ZF2 to be able to use different exception classes to represent different error types rather than using string comparison and, at the same time, we can have a single catch() for when we don't need that level of granularity.

MongoDB was mentioned a few times at tek and I said that I wanted to have a look at.

Travis' article, MongoDB: A first look, came out a few days ago and piqued my interest further. Then Matthew sent me some source code that requires it. The stage was set for getting MongoDB working on my Mac.

MongoDB

I use homebrew as a package manager for installing open source bits and bobs like couchdb, git, and hg. Installing MongoDB was simply a case of:

brew install mongodb

Once, installed there's a convenient LaunchAgent plist supplied so that mongodb starts with the computer:

cp /usr/local/Cellar/mongodb/1.4.3-x86_64/org.mongodb.mongod.plist ~/Library/LaunchAgents
launchctl load -w ~/Library/LaunchAgents/org.mongodb.mongod.plist

And at this point, MongoDB is installed on your Mac and Travis' article and the tutorial work!

The Mongo PHP extension

If you've been following along here for a while, then you'll know that I use the stock PHP that comes with Mac OS X. I've been very happy with it so far and installed Xdebug was easy enough using pecl, so I was hopeful that the mongo extension would be equally simple.

Turns out that it is!

pecl install mongo

Compiles the extension with no problems.

To add it to your PHP install, edit php.ini and add:

extension=mongo.so

A quick sudo apachectl restart and phpinfo() shows this:

All done! You can now get at MongoDB from your PHP scripts.

I keep coming across this one, so I'm noting it here so I can find it again.

Internet Explorer doesn't like certain headers related to caching when you send it a file from an SSL site. The Microsoft knowledge base article, Internet Explorer is unable to open Office documents from an SSL Web site explains the problem quite well:

When you attempt to open or download a Microsoft Office document (.doc file, .xls file, .ppt file, and so on) from a secure Web site in Internet Explorer, you may receive one of the following error messages, even though the document is available and downloaded from the server

It turns out that the problem is directly due to sending these headers:

Pragma: no-cache  
Cache-control: no-cache,max-age=0,must-revalidate

So, make sure you don't!

Incidentally, IE6 also gets upset if you set max-age to 0 and attachment to inline, so don't do that either!