Database classes for SQL Server 2005 and SQL Server 2008 (using php_sqlsrv.dll from the Microsoft Data Programmability t

[eluser]_jon[/eluser]
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/detai...laylang=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!

[eluser]_jon[/eluser]
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/200...eased.aspx

[eluser]darrenm[/eluser]
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

[eluser]darrenm[/eluser]
My link to the MSDN site got mashed.
It's here (copy and paste)

Code:

http://msdn.microsoft.com/en-us/library/cc296178(SQL.90).aspx

[eluser]JulianM[/eluser]
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.

[eluser]JulianM[/eluser]
I noticed at Event Viewer in Windows 2008 that there is a fatal error

Code:

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.

[eluser]JulianM[/eluser]
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).


[quote author="JulianM" date="1233210015"]
However, the screen still blank. I'm using PHP 5.2.8.8 maybe I need to try with a previous distribution.[/quote]

[eluser]JulianM[/eluser]
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:

Code:

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.


[quote author="JulianM" date="1233211074"]
Now, I need to figure if is possible to connect to SQL Server 2005 using Windows Authentication only (not mixed mode).
[/quote][/quote]

[eluser]JulianM[/eluser]
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

[quote author="jontce" date="1218547049"]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/200...eased.aspx[/quote]

[eluser]JulianM[/eluser]
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


[quote author="jontce" date="1216831516"]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.
[/quote]