MSSQL Server 2005 and CodeIgniter

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.

Paul

23rd July, 2008

Posted by Paul

Tagged as

29 Comments

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

daleedom 11th Aug 08

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!

Zeno 4th Sep 08

@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.

Jon 4th Sep 08

It works! Hopefully time will tell that it keeps working. Thanks again guys! :)

Zeno 4th Sep 08

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)

Zeno 8th Sep 08

@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;

Jon 11th Sep 08

[...] 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 [...]

Ideas, vivencias y demás :: PHP_SQLSRV, dónde estas? :: October :: 2008 15th Oct 08

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?

NTulip 23rd Oct 08

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.

NTulip 23rd Oct 08

@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.

Jon 24th Oct 08

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. :D

Scott 8th Nov 08

Hi, thank you for the information. I'm tring tu run CodeIgniter 1.7 in Windows 2008 with IIS7 and SQLServer 2005. I also updated the sqlsrv drivers for SQLServer 2005. Right now only get a blank screen when I try to load the database library having the database.php configured to use sqlsrv driver. The same happened when I tried to use mssql driver. Any thoughts? :(

Julian 28th Jan 09

Hi Julian, Unfortunately Jon (the main man for this) is away until Wednesday next week. Will get him to drop you a line then.

Kaweb 30th Jan 09

If you're experiencing a blank screen, it's indicative of a mistake in your configuration (especially since the same happens with the MSSQL driver). First port of call would be to check your IIS7 error log, and your connection settings in config/database.php.

Jon 4th Feb 09

Thank you for this. I also got a blank screen and "PHP Fatal error: Call to undefined method CI_DB_sqlsrv_driver::_db_set_charset(). Adding function db_set_charset to sqlsrv_driver.php solved the issue: function db_set_charset($charset, $collation) { return TRUE; } (Using IIS7, SQL Server 2008 and Codeigniter 1.7.2)

Mikko 30th Nov 09

Thanks for this CI driver! I was *finally* able to connect to SQL Server 2005. I'm running an AppServ 2.5.8 install on a Windows XP machine. I followed the instructions on the Microsoft site to install the new PHP driver. I did have to create an empty db_set_charset function in sqlsrv_driver.php to finally get it to work. Thanks again for your work!

Schwebbie 6th Jan 10

Hi all. I've actually found a few minutes to update the driver for php_sqlsrv.dll version 1.1, and CodeIgniter 1.7.2 - You can get a newer version of it from the following URL: http://www.phrenzy.org/code/sqlsrv-1.1.tar.gz It contains many of the fixes mentioned in the comments here, plus a few from the CodeIgniter forums. I hope this helps some of you.

Jon 12th Jan 10

MASSIVE THANKYOU!!! Spent Hours upon Hours trying to connect to MIcrosoft SQL 2005 Server.

Karl 15th Apr 10

Thanks a lot. Hopefully this will be included in the main CI build soon. Great site design, btw. Best regards, Kervin

Kervin 26th May 10

Hi! I got an error when using the version 1.1 Fatal error: Call to undefined function sqlsrv_connect() in ....../system/database/drivers/sqlsrv/sqlsrv_driver.php on line 85 What can I do to fix this?

Julio Bitencourt 8th Jul 10

Thanks a lot for sqlsrv library. It works perfectly with my SQL 2008 instance except the only error message ERROR: [2] sqlsrv_rows_affected(): supplied resource is not a valid sqlsrv_stmt resource (line 297) be written in the CI's log when I call affected_rows() after an UPDATE execution. I figured it out by change affected_rows()'s code, according to MSDN(http://msdn.microsoft.com/en-us/library/cc296178(SQL.90).aspx), to function affected_rows() { return @sqlsrv_rows_affected($this->result_id); } which seems to fix the problem. Best regards, Noon

gnoon 16th Jul 10

I just realized that only change above is not help. Still get the error message 'This function only works with statements that are not scrollable' after called sqlsrv_rows_affected(), which means that SCROLLABLE should be determined during execute statement. Here the final code // will not specify scrollable if it's DML commands function _execute($sql) { $sql = $this->_prep_query($sql); if(stripos($sql,'UPDATE') !== FALSE || stripos($sql,'INSERT') !== FALSE) { return sqlsrv_query($this->conn_id, $sql, null, array()); } return sqlsrv_query($this->conn_id, $sql, null, array( 'Scrollable' => SQLSRV_CURSOR_STATIC, 'SendStreamParamsAtExec' => true )); } function affected_rows() { return @sqlsrv_rows_affected($this->result_id); } Best regards, Noon

gnoon 16th Jul 10

Just what the doctor ordered. Thank you very much. I agree with Kirven, hopefully this will make it into the main CI, or atleast be made easier to find :)

mdrisser 9th Aug 10

Very helpfully, you are the best, thank you very much!

Peter 10th Nov 10

[...] That driver uses a different API then the regular PHP mssql_* functions. In fact, it uses sqlsrv_* functions instead. So CodeIgniter can’t work with it out of the box. Luckily I found an excellent 2 and 1/2 year-old post by a guy who wrote the CodeIgniter driver to work with the Microsoft drivers. Just download the code, and drop it into system/database/drivers. Read up here: http://www.kaweb.co.uk/blog/mssql-server-2005-and-codeigniter/ [...]

CodeIgniter/PHP + IIS + MySQL + MSSQL: It Works! 11th Jan 11

[...] Blog & information about the new driver (download link) [...]

Database classes for SQL Server 2005 and SQL Server 2008 (using php_sqlsrv.dll from the Microsoft Data Programmability team) | DEEP in PHP 9th Feb 11

Thanx that is just what i needed. Thank you very much. Continue the good work.

freak 16th Feb 11

A PHP Error was encountered Severity: Warning Message: CI_DB_sqlsrv_driver::db_connect(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'Asia/Krasnoyarsk' for '7.0/no DST' instead Filename: sqlsrv/sqlsrv_driver.php Line Number: 85

mpu 15th Mar 11

Thank you SO MUCH guys! I was about to ditch my project completely and rewrite everything in asp because the latest CI adapters are so slow and shoddy when connecting to an azure SQL database. But thanks to this, I don't have too. Keep up the good work!

Sam 11th Apr 13

Make your own comment

Fields marked with * are required. Your email address will not be published.