Comparing the SQLSRV and PDO APIs

  • Article

Following yesterday’s announcement of the availability of a PDO driver for SQL Server (SQL Server Driver for PHP 2.0 CTP 1), I thought I’d take a closer look at the PDO interface. For me, the best way to get to know a new API is to write some code. So,  as a learning exercise, I converted the example application that is included in the documentation from using the procedural sqlsrv API to using the object oriented PDO API (The converted files are attached to this post. I used the AdventureWorks2008 sample database, which you can download here.). Perhaps an even better exercise would be to re-write the entire example application in an OO style, but I wanted to stay focused on how the data access code changes, so I’ll save the more rigorous exercise for later.

If you have written lots of PDO code against other databases, the converted example application itself may not be that interesting to you. However, my observations of how the sqlsrv API and the PDO API compare might be interesting, so I’ll start there. I have to say that I was somewhat disappointed with the limitations of the PDO interface. This isn’t a criticism of this particular PDO driver, it’s just that the sqlsrv API offers some great functionality that cannot be easily exposed in the PDO interface. Here’s some of what I noticed:

  • The streaming functionality that is available in the procedural sqlsrv API for retrieving data cannot be leveraged in the PDO interface. The sqlsrv API leverages PHP streams to allow you to send and retrieve data as described here:  Retrieving Data as a Stream and How to: Send Data as a Stream. However, with the PDO driver, you cannot retrieve data as a stream. Depending on the application, this may have significant impact on memory usage.

 

  • The functionality offered in sqlsrv_has_rows (determines if a result set has rows) and sqlsrv_num_rows (determines the number of rows in a result set) isn’t available via PDO. There are workarounds, but they involve bringing a result set (in part or in whole) into memory. Again, this may not be an issue depending on the application, but it could be.

 

  • The ability to move through and retrieve a single field in a result set row can only be done with PDOStatement::fetchObject when using PDO. With the sqlsrv API, the combination of sqlsrv_fetch/sqlsrv_get_field not only allows you to retrieve a single field, it allow you to specify the PHP data type and encoding when you retrieve the data. This isn’t available through PDO. The PDOStatement::fetchColumn method comes close, but it doesn’t allow you to move though the fields of the current row. 

 

On the positive side, I really liked the PDO::lastInsertId method and the ability to deal with errors by handling exceptions. In the example code below you can see how these features made for much more readable code.

I’m curious about how relevant or important this information is. Are these sqlsrv features (that aren’t surfaced in the PDO interface) important to you? How important are they? Why? (Please comment below or provide feedback on the SQL Server Driver for PHP Team Blog, or on the forum.)

The remainder of this post is for folks who haven’t written much PDO code. I’ll look at some of the code from the example application in the sqlsrv driver documentation and compare it with the same code written with the PDO driver.

Connecting to a Database

No surprises here connecting with Windows Authentication (and no surprises when connecting with SQL Server Authentication). I have to make one extra call with PDO to set the error handling mode, but I get the benefit of working with exceptions now.

sqlsrv

$serverName = "(local)\sqlexpress"; $connectionOptions = array("Database"=>"AdventureWorks2008");

$conn = sqlsrv_connect( $serverName, $connectionOptions); if( $conn === false ) { die( FormatErrors( sqlsrv_errors() ) ); }

PDO

$serverName = "(local)\sqlexpress";

try { $conn = new PDO( "sqlsrv:Server=$serverName;Database=AdventureWorks2008", "", ""); $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch(Exception $e) { die( print_r( $e->getMessage() ) ); }

 

Retrieving Data

Two things to notice here:

  1. With PDO, I pulled the entire result set into memory so I can count the results. With the sqlsrv API, I can use a scrollable cursor to count the results on the server.
  2. The FormatErrors function wraps a call to sqlsrv_errors in the sqlsrv example. In the PDO code, I get the benefit of working with exceptions.

sqlsrv

$queryTerms = array($_POST['query']); $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0"; $cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET); $getProducts = sqlsrv_query($conn, $tsql, $queryTerms, $cursorType); if ( $getProducts === false) { die( FormatErrors( sqlsrv_errors() ) ); }

if(sqlsrv_has_rows($getProducts)) { $rowCount = sqlsrv_num_rows($getProducts); BeginProductsTable($rowCount); while( $row = sqlsrv_fetch_array($getProducts, SQLSRV_FETCH_ASSOC)) { PopulateProductsTable( $row ); } EndProductsTable(); } else { DisplayNoProdutsMsg(); }

PDO

try { $queryTerms = array($_POST['query']); $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0"; $getProducts = $conn->prepare($tsql); $getProducts->execute($queryTerms); $products = $getProducts->fetchAll(PDO::FETCH_ASSOC); $productCount = count($products); if($productCount > 0) { BeginProductsTable($productCount); foreach( $products as $row ) { PopulateProductsTable( $row ); } EndProductsTable(); } else { DisplayNoProdutsMsg(); } } catch(Exception $e)

{ die( print_r( $e->getMessage() ) ); }

 

Inserting Data

Really, no surprises here, but do note that the sqlsrv API allows for data to be streamed to the database whereas the PDO API does not.

sqlsrv

$comments = "data://text/plain,".$_POST['comments']; $stream = fopen( $comments, "r" ); $tsql = "INSERT INTO Production.ProductReview (ProductID, ReviewerName, ReviewDate, EmailAddress, Rating, Comments) VALUES (?,?,?,?,?,?)";

$params = array(&$_POST['productid'], &$_POST['name'], date("Y-m-d"), &$_POST['email'], &$_POST['rating'], &$stream);

$insertReview = sqlsrv_prepare($conn, $tsql, $params); if( $insertReview === false ) { die( FormatErrors( sqlsrv_errors() ) ); }

if( sqlsrv_execute($insertReview) === false ) { die( FormatErrors( sqlsrv_errors() ) ); }

PDO

try { 

   $tsql = "INSERT INTO Production.ProductReview (ProductID, ReviewerName, ReviewDate, EmailAddress, Rating, Comments) VALUES (?,?,?,?,?,?)";

   $params = array(&$_POST['productid'], &$_POST['name'], date("Y-m-d"), &$_POST['email'], &$_POST['rating'], $_POST['comments']); $insertReview = $conn->prepare($tsql); $insertReview->execute($params); } catch(Exception $e) { die( print_r( $e->getMessage() ) ); }

 

Retrieving Binary Data

Once again, two things to notice here:

  1. With the sqlsrv API, I can use the combination of sqlsrv_fetch/sqlsrv_get_field to retrieve a single field and specify that I want to retrieve it as a PHP stream.
  2. The ability to work with exceptions in the PDO code make for more readable code.

sqlsrv

$tsql = "SELECT LargePhoto FROM Production.ProductPhoto AS p JOIN Production.ProductProductPhoto AS q ON p.ProductPhotoID = q.ProductPhotoID WHERE ProductID = ?";

$params = array($_GET['productId']);

$stmt = sqlsrv_query($conn, $tsql, $params); if( $stmt === false ) { echo "Error in statement execution.</br>"; die( print_r( sqlsrv_errors(), true)); }

if ( sqlsrv_fetch( $stmt ) ) {

     $image = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY)); fpassthru($image); } else { echo "Error in retrieving data.</br>"; die(print_r( sqlsrv_errors(), true)); }

PDO

try { $tsql = "SELECT LargePhoto FROM Production.ProductPhoto AS p JOIN Production.ProductProductPhoto AS q ON p.ProductPhotoID = q.ProductPhotoID WHERE ProductID = ?";

$stmt = $conn->prepare($tsql); $stmt->execute(array($_GET['productId'])); $stmt->bindColumn(1, $image, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY); $stmt->fetch(PDO::FETCH_BOUND); echo $image; } catch(Exception $e) { die( print_r( $e->getMessage() ) ); }

 

Inserting Binary Data

The ability to work with exceptions and the PDO::lastInsertId method make the PDO code here much more readable. With the sqlsrv API, there is no built-in function for getting the last ID after an INSERT, so I needed to execute a batch query and work through the second result set.

sqlsrv

$tsql = "INSERT INTO Production.ProductPhoto (LargePhoto) VALUES (?); SELECT SCOPE_IDENTITY() AS PhotoID"; $fileStream = fopen($_FILES['file']['tmp_name'], "r");

$uploadPic = sqlsrv_prepare($conn, $tsql, array( array(&$fileStream, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'))));

if( $uploadPic === false ) { die( FormatErrors( sqlsrv_errors() ) );}

if( sqlsrv_execute($uploadPic) === false ) { die( FormatErrors( sqlsrv_errors() ) ); }

$next_result = sqlsrv_next_result($uploadPic); if( $next_result === false ) { die( FormatErrors( sqlsrv_errors() ) ); } if( sqlsrv_fetch($uploadPic) === false) { die( FormatErrors( sqlsrv_errors() ) ); }

$photoID = sqlsrv_get_field($uploadPic, 0);

$tsql = "UPDATE Production.ProductProductPhoto SET ProductPhotoID = ? WHERE ProductID = ?";

if( sqlsrv_query($conn, $tsql, array($photoID, $_POST['productid'])) === false ) { die( FormatErrors( sqlsrv_errors() ) ); }

PDO

try {

    $tsql = "INSERT INTO Production.ProductPhoto (LargePhoto) VALUES (?)"; $uploadPic = $conn->prepare($tsql); $fileStream = fopen($_FILES['file']['tmp_name'], "r"); $uploadPic->bindParam(1, $fileStream, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY); $uploadPic->execute();

    $photoID = $conn->lastInsertId(); $tsql = "UPDATE Production.ProductProductPhoto SET ProductPhotoID = ? WHERE ProductID = ?"; $associateIds = $conn->prepare($tsql); $associateIds->execute(array($photoID, $_POST['productid'])); } catch(Exception $e) { die(print_r($e->getMessage())); }

Again, the two files that make up the example application are in the zip file attached to this post.

That’s it for this post. Please let me know what you think of this preview release of the SQL Server Driver for PHP 2.0. I’ll make sure the development team hears your feedback.

Thanks.

-Brian

Share this on Twitter

adventureworks_demo_pdo.zip