SQLSRV driver vs. PDO driver for PHP with MS SQL Server

Question

What considerations should I take into account when choosing between SQLSRV driver vs. PDO driver (for PHP with MS SQL server)?

I saw this previous Stackoverflow post ( When using PHP on Windows, what is better (1) the native driver for SQL Server or (2) the PDO driver? ) but the answer seems a bit lacking and doesn't mention all the benefits of using the SQLSRV driver as mentioned in this article.

I'm looking for a comprehensive and up-to-date (eg. is it still the case that SQLSRV driver is only available for Windows?) answer that programmers can refer to as a resource.

Answer 1

SQLSRV and PDO_SQLSRV are the two current-generation php drivers available from Microsoft, but both use the same code underneath: SQL Server Native Client 11. (That's why there's no Mac or Linux version of the php drivers: they are just wrappers.) Performance of the two drivers should be similar; it's just a matter of which API you prefer.

In most cases one would use the PDO_SQLSRV driver because of cross-platform considerations. However, after looking at both drivers for a new (small) project I went with the SQLSRV driver because it returns data as [a map of] the underlying SQL Server datatypes, whereas the PDO_SQLSRV returns everything as a string.

So if your sql is:

SELECT 1234 as integer, Cast(123.456 as float) as float, 
       getdate() as date, '1234' as string1,'123.456' as string2;

Then var_dump of the row from PDO_SQLSRV gives:

  array(1) {
    [0] =>
    array(5) {
      'integer' =>
      string(4) "1234"
      'float' =>
      string(7) "123.456"
      'date' =>
      string(23) "2012-12-06 22:35:05.373"
      'string1' =>
      string(4) "1234"
      'string2' =>
      string(7) "123.456"
    }
  }

while the SQLSRV driver gives:

array(1) {
    [0] =>
    array(5) {
      'integer' =>
      int(1234)
      'float' =>
      double(123.456)
      'date' =>
      class DateTime#1 (3) {
        ...
      }
      'string1' =>
      string(4) "1234"
      'string2' =>
      string(7) "123.456"
    }
  }

It drove me nuts that PDO_SQLSRV cast all of my data to a string whether I wanted it to or not, so I used SQLSRV. (I have to admit I set ReturnDatesAsStrings=true because I was too lazy to deal with the date class.)

I also like the syntax a bit better, but that's just me.

Answer 2

PDO allows you to write you code to be reasonably DB-neutral.

If you want truly DB-neutral, you'd want to use a full DB abstraction layer like NotORM -- with plain PDO, you'd still need to be careful about SQL syntax difference, but at least your basic PHP code would be DB-neutral.

Being DB-neutral may not seem important now -- if you're using SQL Server, then you've probably been told that's what is required and nothing else -- but you can't predict how things will change in the future, so if the choice is between a DB-neutral driver and a DB-specific driver, and you don't have any other reason for a preference, then go with the neutral one.... it'll make life a lot easier if your company gets taken over and the new boss wants to use Oracle as the DB!

Also, because it's DB neutral, PDO is more standard and more well-known in the PHP community. You'll get a lot more help with PDO from sites online (like this one) than with the MSSQL driver.