Version 3.0 (beta) of the SQL Server Drivers for PHP Released!

A Community Technology Preview (a beta release) of v3.0 of the SQL Server Drivers for PHP was released today (see the announcement on the team blog). You can download it here: Download v3.0 of the SQL Server Drivers for PHP. In this release, there are three new features: buffered queries, support for LocalDB, and support for high availability and disaster recovery. It’s important to note that the latter two features are dependent on the next version of SQL Server (code named “Denali”). A preview of Denali can be downloaded for free here (see notes later in this article about the installation process): Download SQL Server Denali CTP 3. More detail about each new feature is in the sections below. We’re hoping to get feedback from you. If you have feedback, please comment on this post or reach out to me (@brian_swan) and/or Jonathan Guerin (@kop48, the Program Manager for the drivers) on Twitter.

Buffered Queries

Perhaps a more descriptive name for “buffered queries” would be “buffered result sets”. With this feature, you can execute a query and bring the entire result set into memory. This allows you to easily get the row count and move back and forth through rows. Prior to this feature, to enable scrolling through a result set, you needed to use a scrollable cursor. Using a scrollable cursor is still the best option if you are dealing with large result sets, but if you have small to medium sized result sets, the buffered queries option may improve your applications performance.

SQLSRV

To bring an entire result set into memory with the SQLSRV driver, supply an options array to sqlsrv_query or sqlsrv_prepare with the following key=>value pair: “Scrollable”=>”buffered”. Then, when retrieving rows, you can call sqlsrv_num_rows to get the row count and you can use the scroll options with sqlsrv_fetch, sqlsrv_fetch_array, or sqlsrv_fetch_object:

$serverName = '.\sqlexpress';

$connectionInfo = array("UID"=>"username", "PWD"=>"password", "Database"=>"ExampleDB");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

$sql = "SELECT * FROM CUSTOMERS";

$stmt = sqlsrv_query($conn, $sql, null, array("Scrollable"=>"buffered"));

echo "Row count: " . sqlsrv_num_rows($stmt) . "<br />";

$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC, SQLSRV_SCROLL_ABSOLUTE, 10);

print_r($row);

For more information, see Cursor Types (SQLSRV Driver) in the documentation (included in the download).

PDO_SQLSRV

To bring an entire result set into memory with the PDO_SQLSRV driver, specify cursor options on the PDO::prepare method as shown below. Then, to retrieve data, use the fetch options on the PDOStatement::fetch method:

$serverName = '.\sqlexpress';

$conn = new PDO( "sqlsrv:server=$serverName ; Database = ExampleDB", "", "");

$sql = "SELECT * FROM CUSTOMERS";

$stmt = $conn->prepare( $sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED));

$stmt->execute();

echo "Row count: " . $stmt->rowCount() . "<br />";

$row = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 1 );

print_r($row);

For more information, see Cursor Types (PDO_SQLSRV Driver) in the documentation.

Note that the default buffer size is set to 10240 KB (i.e. 10 MB). You can change this value using the  sqlsrv_configure function or by editing your php.ini file (the setting name is ClientBufferMaxKBSize).

LocalDB

LocalDB is a “serverless” database (available in SQL Server “Denali”) designed specifically for developers. It is easy to install and requires no management while offering the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. (For more information, see this blog post: Introducing LocalDB, An Improved SQL Express.) Basically, LocalDB allows you to connect directly to a SQL Server database file (an .mdf file).

After you have LocalDB installed (see notes on installing SQL Server “Denali” below), you can connect to a database file by using “(localdb)\v11.0” as your server name and by supplying the path to the .mdf file in your connection options:

SQLSRV

$serverName = '(localdb)\v11.0';

$connectionInfo = array( "Database"=>"ExampleDB", "AttachDBFileName"=>'c:\Temp\ExampleDB.mdf');

$conn = sqlsrv_connect( $serverName, $connectionInfo);

For more information, see SQL Server Driver for PHP Support for LocalDB in the documentation.

PDO_SQLSRV

$serverName = '(localdb)\v11.0';

$conn = new PDO( 'sqlsrv:server=(localdb)\v11.0; Database=TestDB; AttachDBFileName=c:\Temp\TestDB.mdf', NULL, NULL);

For more information, see SQL Server Driver for PHP Support for LocalDB in the documentation.

Keep in mind that LocalDB is still a beta feature of SQL Server “Denali” (i.e. FEEDBACK IS WELCOME!) Here’s one “gotcha" that I ran into when using LocalDB with the SQLSRV driver:LocalDB only supports Windows Integrated Authentication. If you are looking closely at the code snippets above, you notice that no user name or password is supplied to connect. The SQLSRV driver attempts to connect using Windows authentication when no username and password are supplied. I found it easy to run PHP from the command line to connect to LocalDB (a command prompt runs under my identity). However, when running PHP as a FastCGI module in IIS, permissions are not so straightforward. You’ll need to configure IIS to use Windows authentication (see this article for background information: SQLServer Driver for PHP: Understanding Windows Authentication.

High Availability and Disaster Recovery

SQL Server high availability and disaster recovery features (collectively called SQL Server Always On) provide near-zero downtime and make it possible to optimize hardware usage. For more information about SQL Server Always On, see Always On – New in SQL Server Code Named “Denali” CTP 3. To understand how you an leverage these features through the SQL Server Drivers for PHP, , see SQL Server Driver for PHP Support for High Availability and Disaster Recovery in the documentation.

Notes on Installing SQL Server “Denali”

You can download SQL Server “Denali” CTP 3 from here. I just want to point out two things to make sure your installation goes smoothly:

1. When selecting an edition, be sure to choose Express. This option will install LocalDB.

2. When selecting features to install, be sure to include LocalDB.

As I said in the introduction, we would love to get your feedback…so please let us know what you think!

Thanks.

-Brian