Thread

Hello all.

We have (over the last 18 months) written a large web application for the insurance market that is under heavy load.  We found that the current driver (php_mssql.dll) is very buggy and inefficient under load, and refuses to connect to the database 50% of the time, despite all the details being correct.  Other people have also had this issue.  So, we tried out the new beta driver from Microsoft and wrote a driver for our API.  As the front end of the site is supplied with the CodeIgniter framework, we also wrote a CodeIgniter driver for MSSQL using the new driver.

Blog & information about the new driver (download link)

Hopefully this will help one person!  If you need to download the DLL for this new driver, you can find it at:
http://www.microsoft.com/downloads/details.aspx?familyid=85f99a70-5df5-4558-991f-8aee8506833c&displaylang=en

If you find any problems with it, please let me know in this post and ill sort them out the best I can.  Apologies if you find anything that is scrappy, it was written in a rush!

I’ve had a message letting me know that the driver doesn’t quite work with scaffolding.  I’ll have this sorted shortly and I will repost the driver.
For anybody that is interested, Microsoft just launched v1.0 of their driver for SQL Server 2005/2008. You can get it from:

http://blogs.msdn.com/sqlphp/archive/2008/07/28/microsoft-sql-server-2005-driver-for-php-v1-0-has-released.aspx

Hi Guys

Great work, this may well get us out of a really deep hole we’ve landed in.

I’ve just been through all the basics to test and have just two things that don’t seem happy:

afffected_rows()
This returns nothing. It’s actually throwing an error (remove the @ to see). This may be an extension problem as non CI tests I’ve done suggest that sqlsrv_rows_affected will throw and error unless you follow the exact technique described here. That is, to construct your query with placeholders and pass values in using the “optional” $params argument. So I think this may be a non-starter as I don’t think the CI Active Record class will support that. Unless someone knows better… ?

last_query()
This seems to get stuck on the first query run in my Controller and doesn’t update if you run several. Again, this may be CI or Extension problem as MSSQL has the same behaviour.

For info I’m using CI 1.7 out of SVN and SQL Server Express 2005

cheers
Darren

My link to the MSDN site got mashed.
It’s here (copy and paste)

<a href="http://msdn.microsoft.com/en-us/library/cc296178SQL.90">http://msdn.microsoft.com/en-us/library/cc296178(SQL.90</a>).aspx

Hi jontse,

I leave a message in your blog but I’d also comment here in case this happened to anyone else. I’m tring to run CodeIgniter 1.7 in Windows 2008 with IIS7 and SQLServer 2005. (the client need this configuration :( )

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, without success.

Do you know any idea what the problem can be?

Thanks in advance.

I noticed at Event Viewer in Windows 2008 that there is a fatal error :(

Faulting application php-cgi.exe, version 5.2.8.8

However, the screen still blank. I’m using PHP 5.2.8.8 maybe I need to try with a previous distribution.

Well, hopefully it worked with a lower version of PHP. I have installed 5.2.6 non-thread-safe and now the fault segmentation message not appear any more.

Now, I need to figure if is possible to connect to SQL Server 2005 using Windows Authentication only (not mixed mode).

However, the screen still blank. I’m using PHP 5.2.8.8 maybe I need to try with a previous distribution.

Hi again, I have resolved this a few days ago, and maybe you are interesting to know the simple modification I made to your sqlsrv database library, in order to work with Windows Authentication (not SQL Server mode only).

I only have changed the db_connect() function in database/drivers/sqlsrv/sqlsrv_driver.php for this:

function db_connect($pooling = false)
    {
        if (empty($this->username) )
        {
            $connection = array( 'Database' => $this->database );
        
        } else {
        
            $connection = array(
                'UID' => (empty($this->username)) ? '' : $this->username,
                'PWD' => (empty($this->password)) ? '' : $this->password,
                'Database' => $this->database,
                'ConnectionPooling' => ($pooling) ? 1 : 0
            );
        }
        
        $result =  sqlsrv_connect($this->hostname, $connection);
        if (!$result)
        {
            // print_r( sqlsrv_errors() );
        }

        return $result;
    }

Explanation:

The driver assumes to use Windows Authentication if USR and PWD are not specified.

Moreover, you can ever print sqlsrv_errors() in case of an error, to know why it was not able to connect.

Now, I need to figure if is possible to connect to SQL Server 2005 using Windows Authentication only (not mixed mode).

Did you notice the num_rows() function is not working properly?

Do you have any clue how this function can work with the SQLServer Driver for PHP?

I’m using SQL Server 2005 but I was not able to execute num_query() since it count the fields instead of rows. :(

Thanks

I’ve had a message letting me know that the driver doesn’t quite work with scaffolding.  I’ll have this sorted shortly and I will repost the driver.
For anybody that is interested, Microsoft just launched v1.0 of their driver for SQL Server 2005/2008. You can get it from:

http://blogs.msdn.com/sqlphp/archive/2008/07/28/microsoft-sql-server-2005-driver-for-php-v1-0-has-released.aspx

Hi again jontce. I have made a few modifications to your sqlsrv driver in order to use CodeIgniter’s Active Record.

I think you made a great library.

If you are interested in these changes please let me know and I will send you my files.

Thanks,

Julian

Hello all.
We have (over the last 18 months) written a large web application for the insurance market that is under heavy load.  We found that the current driver (php_mssql.dll) is very buggy and inefficient under load, and refuses to connect to the database 50% of the time, despite all the details being correct.  Other people have also had this issue.  So, we tried out the new beta driver from Microsoft and wrote a driver for our API.  As the front end of the site is supplied with the CodeIgniter framework, we also wrote a CodeIgniter driver for MSSQL using the new driver.

Julian, I’d be interested in seeing you modified sqlsrv driver since I’m stuck with MSSQL. Also, which version of PHP did you finally get it working with? I too am just getting a blank page.

Thanks

Hi bmeredyk,

I have used 5.2.6 instead of 5.2.8 (this was the latest stable version at the time I prepared the environment for my project). However, using PHP version 5.2.8 I was not able to run it properly on IIS with sqlsrv, that is why I rolled back to 5.2.6

I will send you my modified version of sqlsrv but please give me a couple of days since I’m not in my location now.

Thanks.

Julian Magnone

Julian, I’d be interested in seeing you modified sqlsrv driver since I’m stuck with MSSQL. Also, which version of PHP did you finally get it working with? I too am just getting a blank page.

Thanks

Thanks Julian!

I’ve been trying to get it working with XAMPP and Lighty2Go with I think php 5.2.8 and 5.2.6 respectively. (I don’t have access to that computer now either, so I can’t check.)  It seems that it loads as far as attempting to connect and then just dies. 

Brian

@bmeredyk, BTW I recommend you the following:

- check you are using sqlsrv_ts.dll if you are running in XAMPP (this is the thread safe version).
- look at your Windows Event log to check if there were memory errors.

Thanks,

Julian, I’d be interested in seeing you modified sqlsrv driver since I’m stuck with MSSQL. Also, which version of PHP did you finally get it working with? I too am just getting a blank page.

Thanks

Thanks for the tip - I’d been using the non-thread safe version. I’ll have to try sqlsrv_ts.dll tomorrow and check the logs.