MSSQL Server 2005 and CodeIgniter - Kaweb

MSSQL Server 2005 and CodeIgniter

database structure

During development work on one of our larger projects, we uncovered some rather alarming problems when using php_mssql.dll in conjunction with SQL Server 2005 Express. Just by perusing some of the comments over at the PHP manual, you can see that we weren’t alone.

Refreshing the page would cause the mssql_connect function to fail. A further three or four refreshes would result in further failures, until suddenly one more refresh manages to establish a connection. This situation would repeat itself, and is completely random. Despite months of trying to track it down when we had spare time, we just could not get to the bottom of it.

A few months ago, a result began cropping up on Google that appeared to contain some answers. Part of Microsoft’s data programmability team announced they were working a new driver for PHP for use with SQL Server 2005. Excellent, we thought! Of course, we haven’t been able to use this driver in a production environment yet as it is still in beta and subject to change (and change it has). However, we’ve ran tests using the driver and so far, it does appear to be more stable. The only issue is that the symptom we were experiencing is very difficult to replicate (if nigh-on impossible), and so we cannot know for sure if the driver has alleviated the problem.Parts of the project are written using an excellent framework known as CodeIgniter. We took the liberty of also writing an php_sqlsrv.dll compatible driver for it which you can download here. To use the driver, just unzip it into a folder called “system/database/drivers/sqlsrv” and change your database.php to reflect the changes (change the driver to “sqlsrv“). Hopefully this will help one person that was in the same boat as us!

The Microsoft SQL Server 2005 driver for PHP is a PHP 5 only extension. It allows the reading and writing of SQL Server data from within PHP scripts. The extension provides a procedural interface for accessing data in all editions (importantly, including Express) of SQL Server 2005 and also SQL Server 2000. The API makes use of PHP features such as PHP streams to read and write large objects.

Written by kaweb in Design & Build