What SQL Server 2012 Means for PHP Developers

Last week, Microsoft held a virtual conference to announce the availability of SQL Server 2012. The conference included a number of events (speakers, videos, training activities, etc.) that focused on the new functionality available in this release. Now that most of the fanfare has died down a bit, I’d like to take a look at what some of that new functionality means for PHP developers. Combined with the release of the Microsoft Driver for SQL Server for PHP, I think the SQL Server 2012 release makes some big improvements in developing PHP/SQL Server applications.

Easier Setup of Your Development Environment

If you have used SQL Server Express in the past, you may not have been overjoyed at the time it took to download, install, and configure your database server. With the introduction of SQL Server Express LocalDB, you can have this done in less than 5 minutes. Here’s how:

1. Download SQL Server Express LocalDB. Choose either the ENU\x64\SqlLocalDB.MSI or the ENU\x86\SqlLocalDB.MSI depending on your computer architecture.

2. Download the command line Tools. Find “Microsoft® SQL Server® 2012 Command Line Utilities” and select the download for your computer architecture.

With those two steps complete, you can connect to LocalDB and begin creating databases with the sqlcmd utility:

image

There is also a SqlLocalDB utility that lets you create and manage an instance of SQL Server Express LocalDB.

3. Download the Microsoft Drivers for SQL Server for PHP. Add the driver to your PHP extension directory and enable it in your php.ini file, and you can connect to it like this:

$serverName = '(localdb)\v11.0';
$connOptions = array('AttachDBFileName'=>'C:\Users\bswan\ExampleDB.mdf','Database'=>'ExampleDB');
 
$conn = sqlsrv_connect($serverName, $connOptions);
if($conn === false)      
    die(print_r(sqlsrv_errors(), true)); 
else
    echo "Connected via sqlsrv!<br />";

That’s it. From zero to writing SQL Server database queries in PHP much faster than before.

Of course, that’s not entirely it. There can be some gotchas, and there may be a couple more steps, depending on what you want to do. Here are a few things I ran into when getting set up:

  • If you are installing LocalDB and the command line tools on a machine that has a previous version of SQL Server, you will need to alter your PATH environment variable. You will need to move both the C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ and c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ paths before any other paths to SQL Server Tools (they will likely be at the end of your PATH variable by default – the order corresponds to installation order). This will make sure that when you use sqlcmd, the right version will be called.
  • Note that in my PHP code, I’m providing an absolute path to the .mdf file. You must provide an absolute path. By default, the sqlcmd utility will create .mdf files in the User directory. However, you can use any .mdf file you might have…just provide an absolute path to it (and make sure that the sqlcmd utility has access to the directory). Beware that if the .mdf file was created with a previous version of SQL Server, it will be upgraded the first time you connect to it (which will produce a whole host of warnings). After it has been upgraded, everything should work smoothly.
  • Also note that in my PHP code, I’m connecting using Windows Authentication (I’ve omitted the UID and PWD elements from the connection options array). When using LocalDB, you can only connect with Windows Authentication. And, if you are using IIS, it is the credentials of the application pool for PHP that are used to login. So, since we are talking about a dev environment here, I suggest setting the application pool credentials to an identity that is also a login for the targeted database. I did this by starting IIS Manager, clicking Application Pools, right clicking the DefaultAppPool (PHP is running under this pool in my case), then following these steps:

image

Friendlier Syntax: OFFSET…FETCH NEXT

I’ve written a few articles on strategies for paging data with the SQL Server Driver for PHP, all of which relied on the ROW_NUMBER…OVER syntax or server-side cursors. The ROW_NUMBER…OVER syntax is cumbersome compared to the MySQL LIMIT…OFFSET syntax, and server-side cursors are not appropriate for all scenarios. Enter the OFFSET…FETCH NEXT syntax in SQL Server 2012. Now it’s easy to get exactly the subset of data you want from a much larger set:

$sql = "SELECT Name, ProductNumber 
        FROM Production.Product
        ORDER BY ProductNumber
        OFFSET ? ROWS
        FETCH NEXT ? ROWS ONLY";
 
$params = array((int) $_GET['low'], (int) $_GET['high']);    
 
$stmt = sqlsrv_query($conn, $sql, $params);

 

More Flexibility: Client-Side Cursors

Prior to the release of v3.0 of the PHP driver, the only options available for moving through a result set were “forward only” (the default, which forces you to consume rows in order, and you can’t move back to previous rows) or to use server-side cursors (which, as mentioned earlier, are not ideal for all scenarios). However, in the 3.0 release, client-side cursors are available for getting random access to the rows of a result set. To enable this, set the Scrollable option to buffered on sqlsrv_query or sqlsrv_prepare:

$sql = "SELECT Name, ProductNumber 
        FROM Production.Product";
 
$stmt = sqlsrv_query($conn, $sql, null, array("Scrollable"=>"buffered"));
if($stmt === false)
    die(print_r(sqlsrv_errors(), true));

With client-side queries (a.k.a “buffered” queries) enabled, you can use the sqlsrv_num_rows function to get the number of rows, and you can use the following constants with sqlsrv_fetch, sqlsrv_fetch_array,  or sqlsrv_fetch_object to randomly access rows:

  • SQLSRV_SCROLL_NEXT
  • SQLSRV_SCROLL_PRIOR
  • SQLSRV_SCROLL_FIRST
  • SQLSRV_SCROLL_LAST
  • SQLSRV_SCROLL_ABSOLUTE
  • SQLSRV_SCROLL_RELATIVE

Just for fun, here’s one way you could use those constants to work backwards through a result set:

$num_rows = sqlsrv_num_rows($stmt);
for($i = $num_rows - 1; $i >= 0; $i--)
{
    $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC, SQLSRV_SCROLL_ABSOLUTE, $i);
    // process row here
}

Of course, you can also use client-side cursors with the PDO extension. There are plenty of examples in the documentation.

There’s More

The improvements mentioned above are those that I think have the biggest impact for PHP developers now. But there are other things you might be interested in, such as support for AlwaysOn Connectivity and for the PDO::ATTR_EMULATE_PREPARES constant in the PHP Driver. And, the PHP driver still provides access to SQL Azure, the SQL Server ODBC Driver for Linux is in a beta release (which some PHP developers are already taking advantage of), and Hadoop for Azure is on its way. The only down note in this release is the the PHP driver does not currently support PHP 5.4, but that’s a temporary thing. I know the team is working hard to quickly add that support.

As I mentioned at the beginning, I think the release of SQL Server 2012 takes some big steps toward improving the development experience for PHP developers. Thanks to all the folks who have provided feedback to help get us here…we’ve been listening. I’d be very interested to hear if you agree.

Thanks.

-Brian

Share this on Twitter

No Comments