← Back

MSSQL Server 2005 and CodeIgniter

23rd July, 2008 — Written by Jon

Tags:    •    •    •    •    •  

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.

22 Responses to “MSSQL Server 2005 and CodeIgniter”

  1. daleedom Says:
    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

  2. Zeno Says:
    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!

  3. Jon Says:
    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.

  4. Zeno Says:
    September 4th, 2008 at 8:48 am

    It works!

    Hopefully time will tell that it keeps working.

    Thanks again guys! :)

  5. Zeno Says:
    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)

  6. Jon Says:
    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;

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

  8. NTulip Says:
    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?

  9. NTulip Says:
    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.

  10. Jon Says:
    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.

  11. Scott Says:
    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. :D

  12. Julian Says:
    January 28th, 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? :(

  13. Kaweb Says:
    January 30th, 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.

  14. Jon Says:
    February 4th, 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.

  15. Mikko Says:
    November 30th, 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)

  16. Schwebbie Says:
    January 6th, 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!

  17. Jon Says:
    January 12th, 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.

  18. Karl Says:
    April 15th, 2010 at 8:14 am

    MASSIVE THANKYOU!!!

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

  19. Kervin Says:
    May 26th, 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

  20. Julio Bitencourt Says:
    July 8th, 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?

  21. gnoon Says:
    July 16th, 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

  22. gnoon Says:
    July 16th, 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

Leave a Reply