CTP 2 of Microsoft Driver for PHP for SQL Server Released!

  • Article

The SQL Server Driver for PHP team has announced the release of CTP 2 of the Microsoft Driver for PHP for SQL Server 2.0 (download here). (If you don’t know what a “CTP” release is, see Ashay’s blog post from yesterday.) Back in April the team announced the release of CTP1 at DrupalCon SF, which added PDO support to the 1.1 release of the driver.  Since then, the team has been working hard to incorporate community feedback into this release. In this post, I’ll take a look at some of the changes that have been incorporated based on community feedback.

Here’s a high-level list of the work that was done for this release:

  • Code refactored to share common functionality between the SQLSRV and PDO_SQLSRV drivers.
  • SQLSRV extension is rebuilt with refactored code (no new features).
  • PDO::__construct(): a design change from CTP1, Connection Options now need to be specified in the DSN string instead of the driver_options[] array.
  • PDO::SQLSRV_ATTR_DIRECT_QUERY: new custom driver attribute to provide more flexibility to the developer.
    • Ability to execute two queries within the same context or different contexts (useful for using temp tables and other features that are not always available with prepared statements). 
  • Error messages are now encoded per the setting of CharacterSet / PDO::SQLSRV_ATTR_ENCODING.
  • Bug fixes to both the PHP_SQLSRV and PDO_SQLSRV extensions.

Some of these changes demonstrate that the team is committed to maintaining this driver (re-factoring) and others show that they are committed to improving its quality (bug fixes). But what I want to focus on are some of the changes that will affect how you write code when using this driver.

First, let’s look at the changes in the PDO constructor (PDO::__construct()). The change here is that connection options now need to be specified in the DSN string (as opposed to the driver options array). For example, to connect to the server and specify a database, here’s what you need to do:

$serverName = "server_name\SQLEXPRESS";
$UID = "user_name";
$PWD = "password";
$driverOptions = array(); //Specify values for driver-specific attributes in this array. 

try
{
$conn = new PDO( "sqlsrv:Server=".$serverName."; Database=DB_name", $UID, $PWD, $driverOptions);
echo "Connected!";
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}

Note that the Server and Database are specified in the DSN connection string and are separated by a semi-colon. These options are specified as OptionName = OptionValue and are separated by semicolons. Connection options used to be specified as Key => Value pairs in the $connectionOptions array. (For a complete list of connection options, see Connection Options in the documentation.) The $connectionOptions array (which I'm now calling the $driverOptions array in my code examples) is now used to specify the predefined PDO attributes and the driver specific attributes PDO::SQLSRV_ATTR_ENCODING and PDO::SQLSRV_ATTR_DIRECT_QUERY (see below for information about using these). For more information, see the documentation for PDO::__construct().

Next, let’s look at the new PDO::SQLSRV_ATTR_DIRECT_QUERY attribute. The value  for this attribute (true or false, the default is false) can be set on a connection in the connection options array of PDO::__construct() or by using the PDO::setAttribute() method. Setting this attribute to true allows you to preserve context from one query to the next. However, if the value is set to true, queries that are executed on the connection will not be prepared (i.e. an execution plan is not cached on the server). This can result in a slight performance hit if you want to re-execute a query several times with different parameter values. To understand this better, let’s take a look at the example that is in the driver documentation:

$conn = new PDO('sqlsrv:Server=(local)', '', '');
$conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true);

$stmt1 = $conn->query("DROP TABLE #php_test_table");

$stmt2 = $conn->query("CREATE TABLE #php_test_table ([c1_int] int, [c2_int] int)");

$v1 = 1;
$v2 = 2;

$stmt3 = $conn->prepare("INSERT INTO #php_test_table (c1_int, c2_int) VALUES (:var1, :var2)");

if ($stmt3) {
$stmt3->bindValue(1, $v1);
$stmt3->bindValue(2, $v2);

   if ($stmt3->execute())
echo "Execution succeeded\n";
else
echo "Execution failed\n";
}
else
var_dump($conn->errorInfo());

$stmt4 = $conn->query("DROP TABLE #php_test_table");

Because the value of SQLSRV_ATTR_DIRECT_QUERY is set to true in this example, temp tables are usable. Temp tables wouldn’t useable from query to query if the value of the attribute were false because context would not be preserved from query to query (and temp tables are context-specific)…you can test this by setting the value to false. However, if we wanted to re-execute the INSERT statement several times with different parameter values, we would lose the performance benefit of having a query plan cached on the server. So, this attribute basically gives you control over whether you want to optimize for performance of re-executed queries or a preserved context (and you now can get the benefit of parameter binding when working with temporary tables).

Lastly, let’s look at the new possibilities for error message encoding. You can now specify that error messages are encoded according the the CharacterSet connection option (if you are using the native sqlsrv extension) or according to the PDO::SQLSRV_ATTR_ENCODING attribute if you are using the PDO extension. So if you want to return errors with UTF-8 encoding, here’s how you would connect with the native driver:

$serverName = "serverName\sqlexpress";
$uid = "user_name";
$pwd = "password";
$connectionInfo = array( "UID"=>$uid, "PWD"=>$pwd, "Database"=>"TestDB", "CharacterSet"=>"UTF-8");

$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}
else
    echo "Connected!";

And, here’s how you connect with the PDO driver to return errors with UTF-8 encoding:

$serverName = "serverName\SQLEXPRESS";
$UID = "user_name";
$PWD = "password";
$driverOptions = array(PDO::SQLSRV_ATTR_ENCODING=>PDO::SQLSRV_ENCODING_UTF8); 

try
{
$conn = new PDO( "sqlsrv:Server=".$serverName."; Database=DB_Name", $UID, $PWD, $driverOptions);
echo "Connected!";
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}

That’s it. Please let me know if you have questions.

Oh…one more thing: The example application in the documentation has been updated to show how to use the PDO driver. If you are interested, you can take a look at it here.

-Brian

Share this on Twitter