Comparative review of Microsoft SQL Driver for PHP

A search for “SQL Server Driver for PHP” returned no results, and I decided to write this article.

Some already know that Microsoft released their driver for PHP with blackjack using the capabilities of the Native SQL Client and, even, opened the source code .

Why is it needed?

Firstly, the standard MS SQL driver (php_mssql.dll), which comes bundled with PHP, has several disadvantages. The main thing is that it is based on the ntwdblib.dll library , which is no longer supported by Microsoft, and is no longer included with new versions of MS SQL Server. The latest version has the following problems:

• The limit on the maximum field length in the resultset is 255 characters;
• The limit on the maximum length of a variable when calling a stored procedure in the same 255 characters;
• The problem with empty lines when calling a stored procedure;
  If you call a stored procedure, where the parameter is an empty string, the library will magically turn the string into NULL.
• A problem with the varchar and nvarchar types with a dimension greater than 255 characters;
  Anything longer is simply cut off. A workaround is to cast the field type to varchar (max). In this case, everything works correctly.
• Also ntwdblib.dll is significantly inferior in speed of receiving results of Native SQL Client.

All of the above problems are solved using the "SQL Server Driver for PHP". However, we are finding new challenges:

• The absence (possibly temporarily) of several very useful analog functions of the old MSSQL driver;
  For example, getting the number of rows changed, and, until recently, there was no function to get the number of rows in the resultset.
• Problems getting the returned result from the stored procedure when multiple resultset are returned, even if they are empty;
  This, of course, can be circumvented by setting the NOCOUNT flag to ON by calling SET NOCOUNT ON before the request, but this is not the most beautiful solution.
• When a deadlock occurs, the new driver does not have a convenient mechanism to bypass this situation;
  A workaround is to use the sleep () or usleep () PHP function and, if a lock occurs and after a couple of seconds try to run the request again.
• Upon receiving the results, the new driver converts data from the database type to its analogue in PHP, although this is correct from the point of view of data typing;
  So, for example, a field with datetime type will be converted to PHP of DateTime type, and binary and text fields to PHP Stream, which is not convenient, because We have to further process the results to obtain the final result that interests us.
• Complete lack of support under * nix systems
  . While php_mssql can be compiled using FreeTDS.
• Perhaps the most serious drawback is not always stable work under IIS6, under IIS7 and Apache no such problems were noticed.

Despite such a rather large list of new problems, it makes sense to look at the Microsoft driver and examine its work in its projects under Windows Server and MS SQL. The project is developing steadily and problems are being addressed.

I hope this review is useful to someone.

Thanks for attention!