Blog

MSSQL Server 2005 and CodeIgniter

  |   General   |   29 Comments

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.

29 Comments
  • daleedom | Aug 11, 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 …systemdatabaseDB_driver.php on line 178

  • Zeno | Sep 4, 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!

  • Jon | Sep 4, 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.

  • Zeno | Sep 4, 2008 at 8:48 am

    It works!

    Hopefully time will tell that it keeps working.

    Thanks again guys! 🙂

  • Zeno | Sep 8, 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)

  • Jon | Sep 11, 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;

  • Ideas, vivencias y demás :: PHP_SQLSRV, dónde estas? :: October :: 2008 | Oct 15, 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 […]

  • NTulip | Oct 23, 2008 at 9:34 pm

    Connecting to a named instance “serverinstance” 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 | Oct 23, 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.

  • Jon | Oct 24, 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.

  • Scott | Nov 8, 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. 😀

  • Julian | Jan 28, 2009 at 11:29 pm

    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? 🙁

  • Kaweb | Jan 30, 2009 at 8:40 am

    Hi Julian,

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

  • Jon | Feb 4, 2009 at 8:54 am

    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.

  • Mikko | Nov 30, 2009 at 11:40 am

    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)

  • Schwebbie | Jan 6, 2010 at 2:55 pm

    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!

  • Jon | Jan 12, 2010 at 2:47 pm

    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.

  • Karl | Apr 15, 2010 at 8:14 am

    MASSIVE THANKYOU!!!

    Spent Hours upon Hours trying to connect to MIcrosoft SQL 2005 Server.

  • Kervin | May 26, 2010 at 1:42 pm

    Thanks a lot.

    Hopefully this will be included in the main CI build soon.

    Great site design, btw.

    Best regards,
    Kervin

  • Julio Bitencourt | Jul 8, 2010 at 8:47 pm

    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?

  • gnoon | Jul 16, 2010 at 3:57 am

    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 | Jul 16, 2010 at 8:02 am

    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

  • mdrisser | Aug 9, 2010 at 5:37 pm

    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 🙂

  • Peter | Nov 10, 2010 at 9:55 pm

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

  • CodeIgniter/PHP + IIS + MySQL + MSSQL: It Works! | Jan 11, 2011 at 1:41 pm

    […] 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: https://www.kaweb.co.uk/blog/mssql-server-2005-and-codeigniter/ […]

  • Database classes for SQL Server 2005 and SQL Server 2008 (using php_sqlsrv.dll from the Microsoft Data Programmability team) | DEEP in PHP | Feb 9, 2011 at 3:05 pm

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

  • freak | Feb 16, 2011 at 11:25 am

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

  • mpu | Mar 15, 2011 at 10:29 am

    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

  • Sam | Apr 11, 2013 at 11:40 pm

    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!

Post A Comment