Microsoft is announcing an important interoperability milestone: the release of the Microsoft Drivers for PHP for SQL Server 2.0!!

The major highlight of this release is the addition of the PDO_SQLSRV driver, which adds support for PHP Data Objects (PDO). The PHP community has signaled that PDO is the future as it removes data access complexity in PHP applications by enabling developers to focus their efforts on the applications themselves rather than database-specific code. Providing the PDO_SQLSRV driver enables popular PHP applications to use the PDO data access “style” to interoperate with Microsoft’s SQL Server database and make it easier for PHP developers to take advantage of SQL Server's proven track record and to leverage features such as SQL Server's Reporting Services and Business Intelligence capabilities. In addition to accessing SQL Server, both drivers (SQLSRV and PDO_SQLSRV) also enable PHP developers to easily connect to and use Microsoft's cloud database offering, SQL Azure, and enjoy the benefits of a reliable and scalable relational database in the cloud, as well as functionality like exposing OData feeds.

New architecture

While the major focus of this release was the PDO_SQLSRV driver, we took this opportunity to re-architect our code create a core functional layer so that we can offer the same functionality and consistency in both drivers (SQLSRV and PDO_SQLSRV). This new architecture enables us to add new features easily to both drivers.

Microsoft Drivers for PHP for SQL Server

PHP developers are now free to select the driver of their choice, using either the native SQLSRV API (SQLSRV driver) or the PDO API (PDO_SQLSRV driver) for accessing SQL Server or SQL Azure. The following code snippets provide an illustration of a simple task (query and list products from AdventureWorks sample database) using each driver:

SQLSRV driver:

<?php

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

  /* Connect to SQL Server using Windows Authentication. */ 
  $conn = sqlsrv_connect( $serverName, $connectionOptions );

  /* Get products by querying against the product name.*/ 
  $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product";

  /* Execute the query. */ 
  $getProducts = sqlsrv_query( $conn, $tsql );

  /* Loop thru recordset and display each record. */ 
  while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC ) ) 
  { 
    print_r( $row ); 
  }

  /* Free the statement and connection resource. */
  sqlsrv_free_stmt( $getProducts );
  sqlsrv_close( $conn );

?>

PDO_SQLSRV driver:

<?php

  $serverName = "(local)\sqlexpress"; 

  /* Connect to SQL Server using Windows Authentication. */ 
  $conn = new PDO( “sqlsrv:server=$serverName;Database=AdventureWorks" );

  /* Get products by querying against the product name.*/ 
  $tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product";

  /* Execute the query. */ 
  $getProducts = $conn->query( $tsql );

  /* Loop thru recordset and display each record. */ 
  while( $row = $getProducts->fetch( PDO::FETCH_ASSOC ) ) 
  { 
    print_r( $row ); 
  }

  /* Free the statement and connection resource. */
  $getProducts = NULL;
  $conn = NULL;

?>

Community Engagement

We’d like to think our engagement with the PHP community has been among our best and has helped achieve this high level of interoperability with Microsoft’s SQL Server. We listened to comments from the PHP community including core contributors to PHP, many core contributors from several popular PHP applications (such as Drupal, Doctrine, xPDO), several software vendors, and individual developers. We worked hard to meet the intent of PDO (simplifying data access) in our design for PDO_SQLSRV. We worked through several challenges some of which are outlined in a blog by my colleague, Brian Swan. Once again, we heard from our partners and community with the primary goal of delivering a driver that best meets interoperability needs of PHP developers. The team received and responded to many comments in our blog, online forum, at conferences and other events, from participants on the CodePlex project site, through conference calls, and even twitter. We’d like to thank everyone for their effort and look forward to continued community engagement.

Functionality highlights

  1. A fully functional PDO_SQLSRV driver - all API defined by PDO are implemented, even those marked EXPERIMENTAL by PDO. For clarity, all custom attributes or constants in PDO_SQLSRV start with "PDO::SQLSRV_".
  2. The SQLSRV driver rebuilt with the re-architected code, and several critical bug fixes.
  3. The Connection Options are almost identical for both drivers (exceptions only when the option did not apply to PDO_SQLSRV). However, the Connection Options need to be specified in the connection string of PDO_SQLSRV.
  4. All errors are now encoded per the setting of the CharacterSet connection option in SQLSRV, and the setting of the PDO::SQLSRV_ATTR_ENCODING attribute in PDO_SQLSRV.
  5. PDO does not define an OBJECT data type, so all DATETIMEs from SQL Server are always returned as strings by PDO_SQLSRV. The default for SQLSRV continues to be PHP's DateTime object and can be modified using the ReturnDatesAsStrings in the Connection Options array.
  6. The custom attribute PDO::SQLSRV_ATTR_DIRECT_QUERY offers many benefits and additional flexibility for developers:
    1. It enables a more performant use of the PDO::query() API
    2. It enables the use of SQL Server's temporary tables feature by multiple queries
    3. It offers the ability to bind parameters not traditionally offered by PDO::query()
  7. In PDO_SQLSRV, a developer has the additional flexibility to encode data at per connection, and per column.
  8. PDO offers the ability to define IN and IN_OUT parameters, in addition PDO_SQLSRV offers the capability to define OUT parameters very easily.
  9. Developers can set the query timeout using the PDO::SQLSRV_ATTR_QUERY_TIMEOUT per connection and per statement, and exploit it to provide a better user experience in their applications.
  10. Both drivers, SQLSRV & PDO_SQLSRV, require SQL Server Native Access Client 2008 R2 which offers an improved experience when connecting to SQL Azure as well as SQL Server 2005 and later.
  11. The example application for SQLSRV updated to demonstrate new features in our SQLSRV driver in our v1.1 release, and the same application is also ported to demonstrate the same functionality using PDO_SQLSRV.

Documentation and download

The Microsoft Drivers for PHP for SQL Server 2.0 release is available for download on the MSDN Download Center as well as the Web Platform Installer. We encourage you to download it and explore the programming guides, the API documentation, and the two example applications in the .chm file. We will continue to offer our v1.1 release along with our new v2.0 release on our MSDN Download Center for a few months. There is also the ability to access all documentation on MSDN, including the example applications.

Open source code

We have published the source code for both drivers on our CodePlex project site. At this time we are releasing the source code under the Apache 2.0 license. Microsoft supports only the Microsoft signed versions of the drivers.

Feedback and bug reports

We thank community members for their comments. We will continue to be responsive to feedback on this blog, our MSDN forum, as well as the established SQL Server product feedback mechanism. In addition to reporting problems on our forum, the forum is a great resource for looking up issues reported by others and finding the solution that worked. Please send feedback our way as we continue our interoperability work to give developers choices when it comes to our platform.

Thanks.

Ashay Chaudhary
Program Manager, SQL Connectivity – PHP