Why should I use it?

Sep 12, 2008 at 8:10 AM
Could you explain why this library is better than the one from php.net? A comparison table would be nice. And is the library already 100% useable?

Regards,
Robert

Jan 14, 2010 at 10:55 PM

Hi Robert,

Excuse the delay in response, do expect more timely responses from now onwards.

This one is fully supported by Microsoft. This was one of the most common requests from the PHP community as well as customers wanting to deploy PHP applications using SQL Server back end. The goal is to provide the functionality required by the PHP applications. We don't have a comparison table at this time.

Yes, this library is 100% useable. If you do run into issues, feel free to post here.

Thanks.

Ashay Chaudhary
Program Manager, SQL Server drivers for PHP
Microsoft Corp.

Feb 25, 2010 at 10:18 AM

I had the exact same question and couldn't find anywhere on the internet. I was happy with the functionality of the MSSQL extension (even though it does have a limited lifespan because its only available in the VC6 builds).

 

I ran some tests to find out which extension would be faster. By my reckoning, the original MSSQL extension is 8% faster but the new extension has many more features.

 

You can read the full results here: http://www.veadas.net/article/mssql-vs-sqlsrv-speed-comparison

Feb 26, 2010 at 7:17 AM

We appreciate your effort veadas.

Our prioirty in the driver is to provide the features to enable the developer to leverage functionality of SQL Server. Having said that, the code is availabe to all under a pretty permissive license, and certainly does not prevent one from tweaking the code to improve performance or functionality to meet one's needs.

If you do run into issues, feel free to post them here, open a bug, or post to our MSDN forum.

Best regards,

Ashay Chaudhary
Program Manager, SQL Server Driver for PHP

Mar 11, 2010 at 11:19 AM
Edited Mar 11, 2010 at 12:46 PM

We have been using the PHP native MSSQL library for connection to SQL 2000 server over 5 years on our company’s intranet applications. It raised me LOTS of problems which we ned work with.

Before half year we started testing the SQLSRV driver and the we successfully switched on it.

Here follows my personal comparison of native MSSQL php library and new SQLSRV driver:

  • fixes empty string bug
    $value = get_cell_using_mssql_driver("SELECT '' "); // this results in string " " in PHP… very annoying.
    From SQL Server 2000, instead of empty string the old driver returns one space. SQLSRV does not suffer with that.
  • returns and accepts date as DateTime object instead of locale-formatted string
    There is no possibility to get UNIX TIMESTAMP from the MSSQL server, so we ned to strtotime() all received dates after select. Also, all dates ned to be converted to some sort of formatted string before passing to the driver.
  • allows to use UTF-8
    $value = get_cell_using_mssql_driver("SELECT CONVERT(IMAGE, CONVERT(VARBINARY(4000), multibyte_column)) FROM … WHERE …");
    $value = iconv('UCS-2LE', 'UTF-8',$value);
    OMG! This was the most annoying thing on the old driver… There is no possibility to get characters from non-local ANSI codepage!!! However, new SQLSRV driver does this, and performs UTF-8 <=> UCS-2 conversion silently for you… both for input and output data.
  • nonbuffered queries
    Don’t tell me that cursors are slow… I know it. but it is still faster to use a one cursor to select 20 records starting at 523.000 in resultset having million rows, instead of 3 nested subselects, which resorted and cutted the select to get desired rows. SQLSRV driver knows cursors and knows unbuffered queries, which is great workaround for old SQL 2000 server.
  • overcomes the textsize limit for returned value
    There is no possibility to get WHOLE string from text column via standard SELECT. It always returned only 4kB of text so we created crazy functions to select whole text in one function. SQLSRV driver allows to select much longer string at once.
  • detailed error messages
    when something goes wrong in SQL server, often more than one errors are issued. MSSQL library returns only the last one, but SQLSRV returns all errors. In MSSQL driver, you could see that "The statement has been terminated", but in SQLSRV driver you can also see previous errors explaining where the error occurred.
Mar 31, 2010 at 9:28 PM

I also wrote a couple of blog posts about the sqlsrv driver vs. the mssql driver:

http://blogs.msdn.com/brian_swan/archive/2010/03/08/mssql-vs-sqlsrv-what-s-the-difference-part-1.aspx

http://blogs.msdn.com/brian_swan/archive/2010/03/10/mssql-vs-sqlsrv-what-s-the-difference-part-2.aspx

Maybe those will be helpful too.

-Brian

Jan 3, 2012 at 10:39 PM
Edited Jan 3, 2012 at 11:00 PM

Hello,

 I was very pleased to find this tool and so far it has been a gem in achieving the results I need. The biggest reason I chose to use this tool is the handling of text. I am creating an auto-matted migration system, it's working very well but is designed to mingle with up to 3 different databases at a time.

The changing of the database context was achieved with a simple sql statement much like the below statement, where [somedatabase]=customer data and [migrationdatabase]=migration application database because the only known of the customers database is the name, until my app analyzes it.

USE [somedatabase]; select something; USE [migrationdatabase];

Unfortunately it seems this tool does not handle this well, as is seems to loose my database context to the applications database.

Question:

      1) does the ability to change the database context from underneath itself provided?

      2) Can I work with multiple databases from same database connection or do I need to create a database context for each database I need to reach out to and write the code to be a bit more thoughtful? 

 

Just seems like in today's technology the concept of accessing multiple databases should work, ie. shared databases - I must be missing something, can anyone help put me on the right track?

Thanks in advance!

 

I did find this:

The SQLSRV extension does not have a dedicated function for changing which database is connected to. The target database is specified in the $connectionOptions array that is passed to sqlsrv_connect. To change the database on an open connection, execute the following query "USE dbName" (e.g. sqlsrv_query($conn, "USE dbName")).

BUT - Does this mean I can't send a qry like this:  

"USE [somedatabase]; select something; USE [migrationdatabase];"

and have it execute ALL three statements? 


Jan 5, 2012 at 12:54 AM

Hi swinetails,

The ConnectionInfo object is optional - you can connect without specifying a database:
http://msdn.microsoft.com/en-us/library/cc296161.aspx

Have you tried not specifying the database in the options?

Thanks,

Jonathan

Jan 5, 2012 at 12:49 PM
Edited Jan 5, 2012 at 1:02 PM

Hi Jonathan!

Thank you for the suggestion, I ended up fully qualifying my queries to various database, that seemed to solve the problem and is probably a better choice than the design I had originally.

I would like to point out a couple things I ran into with this tool:

1) It does not seem to allow you to execute multiple queries in the same calls, for example,

$qry = " update sometable set somefield='frog';  update anothertable set somefield='toad';";

$stmt = sqlsrv_query($conn, $qry);

2) If you have a stored procedure that executes multiple items, this tool seems to return after the first execution that returns a response, for instance I have a stored procedure that creates a backup of a template database, then creates a new database from that backup. I cant remember where I found this answer but here it is:  

sqlsrv_configure("WarningsReturnAsErrors", 0);
    if ( ($stmt = sqlsrv_query($conn, $query)) )
    {
        do
        {
            print_r(sqlsrv_errors());
            echo " * ---End of result --- *\r\n";
        } while ( sqlsrv_next_result($stmt) ) ;
        sqlsrv_free_stmt($stmt);
    }
    sqlsrv_configure("WarningsReturnAsErrors", 1);

 

3) The datetime, if you do not choose to have your dates return as strings, all you have to do is make sure you convert your dates, ie. convert(varchar(20), getdate(),100)

Thank you for your response!

Aug 8, 2013 at 7:17 AM
Edited Aug 8, 2013 at 7:18 AM
It's been a while but in case someone is still having the problem in #2: all you have to do is to call SET NOCOUNT ON at the beginning of the stored procedure. This will prevent the "rows affected" messages to be sent to the client and be mistaken for the output of the procedure.