Zend Framework connection to SQL Server using SqlSrv

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

I haven’t managed to get much time on the challenge this week as I had hoped. Both my sons’ birthday parties have taken place along with one son’s birthday (the other’s is tomorrow).

It turns out that fellow challenger Juozas Kaziukenas also needs a connection between Zend Framework and SQL Server, so we have joined forces to create App_Db_Adapter_SqlSrv and associated classes. Juozas started a project on codeplex at http://zfmssql.codeplex.com/ and has enabled me to commit to the project too.

Juozas committed an initial implementation and so far I’ve updated the _connect() method to allow for all the parameters supported by sqlserv_connect().

My current application.ini looks like this:

resources.db.adapter = SQLSRV
resources.db.params.adapterNamespace = "App_Db_Adapter"
resources.db.params.host = "RKAWIN2008SQLEXPRESS"
resources.db.params.username = "rob"
resources.db.params.password = "123456"
resources.db.params.dbname = zf-tutorial
resources.db.params.driver_options.ConnectionPooling = 1
resources.db.params.driver_options.Encrypt = 0
resources.db.params.driver_options.TransactionIsolation = SQLSRV_TXN_READ_COMMITTED

The adapter correctly converts the SQLSRV_TXN_READ_COMMITTED string from the ini file into the constant value required by the sqlsrv_connect() function using the very useful constant() function.

I had a slight hiccup with detection of the table’s primary key. Microsoft’s documentation is quite clear once you have found what you are looking for, so that was easily fixed.

At this point the adapter is working for my ZF tutorial, though more work needs to be done on it.