MSSQL Server 2005 and CodeIgniter
23rd July, 2008 — Written by Jon
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 isssue 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 aleviated 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.
August 11th, 2008 at 5:31 am
Tried it with the Scaffolding functionality and got the following:
Call to undefined method CI_DB_sqlsrv_driver::db_set_charset() in …\system\database\DB_driver.php on line 178
September 4th, 2008 at 8:26 am
Hi there,
We have been struggeling with the same issues (with a mssql 2000 server and php/codeIgniter). I came across your blog/article.
Great work!
I am nog going to download the driver and your CodeIgniter library.. Hopefully this works out correctly.
Thanks!
September 4th, 2008 at 8:42 am
@daleedom:
As far as I can see, there is no way to set the character set programatically with this api. I tested the scaffolding against our project here and it seemed fine. The only thing I can suggest is perhaps putting in a ‘dummy’ db_set_charset() function into the CI_DB_sqlsrv_driver class and see if that rectifies your situation.
September 4th, 2008 at 8:48 am
It works!
Hopefully time will tell that it keeps working.
Thanks again guys!
September 8th, 2008 at 8:36 am
It seems to work better then the original driver.
But, unfortunately I still get ‘cannot select database ..x..’ errors every now and again.
(SQL2000 server, with PHP5 on windows Apache Machine)
September 11th, 2008 at 12:23 pm
@Zeno,
The SQLSRV driver that the data access team at Microsoft is actually designed for MSSQL 2005+ (SQL Server 9.x). You can often see a string such as ‘SQL Server 9.0.1399′ inside your MSSQL Studio tool near the top (when you have a database selected), which will show you the current version. Alternatively, you can perform the SQL command:
select @@version;
If I remember correctly, MSSQL2000 is running on 8.x - my advice would be to upgrade your server to a version that supports 9.00.x;
October 15th, 2008 at 6:12 am
[...] vano al momento conectarme con mi maledetta BD. Probé tambien con otro driver para Code Igniter, SQLSRV. Sin embargo, este funciona con una dll experimental (php_sqlsrv.dll y/o php_sqlsrv_ts.dll) que SE [...]
October 23rd, 2008 at 9:34 pm
Connecting to a named instance “server\instance” throws the same error like the above:
Call to undefined method CI_DB_sqlsrv_driver::db_set_charset()
Created dummy function db_set_charset() and that error is no longer shown.
System does throw a different error now:
Unable to set client connection character set: utf8
Any ideas on a fix?
October 23rd, 2008 at 9:46 pm
Re: My previous comment:
In database.php
change $db['default']['db_debug'] to FALSE;
Fixed. This doesn’t mean there won’t be a problem, but the welcome screen comes up.
October 24th, 2008 at 7:45 am
@NTulip:
Ah yes, thank you for your comment. As mentioned in another one of the comments here, according to the documentation released by Microsofts Data team, there is no way to set the character set at this moment in time.
Incidentally, Microsoft just released an October update, so I will see if that has any new configuration options which would allow UTF-8 connections.
November 8th, 2008 at 9:20 pm
Hey thanks for this. You think 2005 is bad? We’re moving to sql server 2008, and it pretty much kills any remaining support with PHP outside of php_sqlsrv.dll. I was just hoping I could find someone who’s already written an abstraction layer for it so I wouldn’t have to do it myself.