Share via

Accessing SQL Server Databases from PHP

  • Article

The Microsoft Drivers for PHP for SQL Server are Microsoft-supported extensions of PHP 5 that provide data access to all editions of SQL Server, beginning with SQL Server 2005. The Microsoft Drivers for PHP for SQL Server include two PHP extensions for accessing data in SQL Server: a procedural interface (SQLSRV) and an object oriented interface (PDO). Both the SQLSRV and PDO drivers provide a comprehensive data access solution from PHP, and include support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling, UTF-8 encoding, error handling, and SQL Database access. This paper discusses how to use several of these features by closely examining parts of the example applications in the product documentation on MSDN (see Example Application (SQLSRV Driver) and Example Application (PDO Driver)). This paper assumes that the reader is familiar with programming in PHP, that the reader has a computer that meets the System Requirements listed for using the driver, and that the AdventureWorks example database is installed from CodePlex.

Applies to: SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 - all editions

 Wiki note: A Word version of this paper can be downloaded here: http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/sql_server_access_via_php.docx. However, the most up-to-date content will be found in this wiki article. The content of the downloadable whitepaper will be updated periodically based on updates made to the wiki article.

Table of Contents

Introduction

The Microsoft Drivers for PHP for SQL Server allow PHP developers to access data in all editions of SQL Server, beginning with SQL Server 2005. The Microsoft Drivers for PHP for SQL Server include two PHP extensions for accessing data in SQL Server: a procedural interface (SQLSRV) and an object oriented interface (PDO). Both the SQLSRV and PDO APIs provide a comprehensive data access solution from PHP, and include support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling, UTF-8 encoding, error handling, and SQL Database access.

This paper explains how to load and configure each of the drivers, and discusses how to leverage several of the features mentioned above. Parts of the example applications (Example Application (SQLSRV Driver) and Example Application (PDO Driver)) in the product documentation will be used to demonstrate these programming scenarios.

For more information and a complete list of driver features and functions, see the SQLSRV API Reference and PHP Data Objects (PDO) Reference  sections in the product documentation on MSDN.

The Microsoft Drivers for PHP for SQL Server rely on the Microsoft SQL Server Native Access Client's ODBC driver to handle the low-level communication with SQL Server. As a result, the Microsoft Drivers for PHP for SQL Server are only supported on Windows. Microsoft provides support for these drivers under its normal support methods. While the source code for these drivers has been made available on the codeplex.com website, Microsoft supports only the signed versions of the drivers from the MSDN download site and the Web Platform Installer.

This paper is divided into two major sections: Using the SQLSRV Driver and Using the PDO Driver. This paper also assumes that the reader is familiar with programming in PHP, that the reader has a computer that meets the System Requirements listed for using the driver, and that the AdventureWorks example database is installed.

Using the SQLSRV Driver

Loading the Driver (SQLSRV)

You can download the SQLSRV driver from the Microsoft Download Center or by using the Web Platform Installer. Included in the download are several .dll files, each with a name that indicates whether it is the procedural or PDO extension (sqlsrv or pdo), whether it is compatible with PHP 5.3 or PHP 5.2 (53 or 52), whether it thread-safe or non-thread-safe (ts or nts), and which compiler the extension was compiled with (VC6 or VC9). For example, the php_sqlsrv_53_nts_vc9.dll file is the procedural extension, is compatible with PHP 5.3, is non-thread-safe, and was compiled with Visual C++ 9 (vc9) compiler. Note that the recommended way to run PHP with Internet Information Services is to use the FastCGI module and a non-thread-safe version of PHP (and therefore a non-thread-safe version of the SQLSRV driver). Whether you choose a vc6 or vc9 version of the driver will depend on the compiler that your version of PHP was compiled with. For more information about which .dll file you should use, see System Requirements.

Loading the SQLSRV driver is similar to loading any PHP extension:

  1. Put the driver file in your PHP extension directory.
  2. Modify the php.ini file to include the driver. For example:

              extension=php_sqlsrv_53_nts_vc9.dll

  1. See Figure 1 (SQLSRV) below for more detail.
  2. Restart the Web server.

For more information, see Loading the Driver in the product documentation.

 Configuring the Driver (SQLSRV)

The SQLSRV driver has three configuration options:

  • LogSubsystems: Use this option to turn the logging of subsystems on or off. The default setting is SQLSRV_LOG_SYSTEM_OFF (logging is turned off by default).
  • LogSeverity: Use this option to specify what to log after logging has been turned on. The default setting is SQLSRV_LOG_SEVERITY_ERROR (only errors are logged by default after logging has been turned on).
  • WarningsReturnAsErrors: By default, the SQLSRV driver treats warnings generated by sqlsrv functions as errors. Use the WarningsReturnAsErrors option to change this behavior. The default setting for this option is true (1).

Note   There are exceptions to this rule. For example, the warning generated by changing the database context is never treated as an error.

For more information about these options and settings, see Configuring the Driver in the product documentation.

Configuration options can be set in the php.ini file, or they can be set in a PHP script with the sqlsrv_configure function. The figure below shows the Dynamic Extensions section of the php.ini file modified to load the driver, log activity on all subsystems, log all activity (errors, warnings, and notices), and turn off the WarningsReturnAsErrors behavior.

 

Figure 1 (SQLSRV): The Dynamic Extensions section of the php.ini.

 

For more information about how to change the default settings, see Logging Activity and How to: Configure Error and Warning Handling Using the SQLSRV Extension in the product documentation.

One way to be sure that the driver is loaded and to see the configuration settings is to run a script that calls the phpinfo() function. To do this, follow these steps:

  1. Open a text file and copy the following code into it:

<?php phpinfo(); ?>

  1. Save the file as info.php in your Web server's root directory.
  2. Open a browser and go to http://localhost/info.php.
  3. Scroll down the resulting page to find the sqlsrv section.

The following figure shows the sqlsrv section of the phpinfo() page. This output confirms the driver is loaded and the configuration settings are set to default values.

 

Figure 2: The sqlsrv section of the phpinfo() page.

** **

Creating a Connection (SQLSRV)

The sqlsrv_connect function is used to establish a connection to the server. The code shown here (from the Example Application (SQLSRV Driver) in the product documentation) establishes a connection to the local instance of SQL Server Express and specifies the AdventureWorks database as the database in use:

$serverName = "(local)\sqlexpress";

$connectionOptions = array("Database"=>"AdventureWorks");

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

if( $conn === false )

die( FormatErrors( sqlsrv_errors() ) );

By default, the sqlsrv_connect function uses Windows Authentication to establish a connection. In most scenarios, this means that the Web server's process identity or thread identity (if the Web server is using impersonation) is used to connect to the server, not an end-user's identity.

The sqlsrv_connect function accepts two parameters: $serverName and $connectionOptions (optional).

  • $serverName - This required parameter is used to specify the name of the server to which you want to connect. This parameter can be use to specify a SQL Server instance or a port number. For example:

$serverName = "myServer\instanceName";

-or-

$serverName = "myServer, 1521";

  • $connectionOptions - This optional parameter is an array of key-value pairs that set options on the connection. In the code above, the database is set to AdventureWorks for the connection. Other options include ConnectionPooling, Encrypt, UID, and PWD. For more information, see Connection Options in the product documentation.

Note   The UID and PWD options must be set in the $connectionOptions parameter to log into the server with SQL Server Authentication.

For more information about creating a connection, see the Connection Pooling (SQLSRV and PDO) section later in this paper and  Connecting to the Server in the product documentation.

Note    The FormatErrors function that is shown in the example is a custom function for formatting error output. It is described in the Handling Errors and Warnings (SQLSRV) section later in this paper.

 Executing a Query (SQLSRV)

The SQLSRV driver provides two options for executing queries: the sqlsrv_query function, or the combination of the sqlsrv_prepare and sqlsrv_execute functions.

The sqlsrv_query function does both statement preparation and execution with one function call and is best suited for executing one-time queries. An alternate method for executing queries (a method well-suited for executing a query multiple times with different parameter values) is the combination sqlsrv_prepare and sqlsrv_execute. This option breaks statement preparation and execution into two function calls.

When a statement is prepared, an execution plan is created on the server. This execution plan is reused when the same query is run again (even if the query is executed with different parameter values). This not only can improve database performance, but it can prevent SQL Injection attacks (see How and Why to Use Parameterized Queries for more information).

The general programming pattern for either option requires doing the following before calling sqlsrv_query or sqlsrv_prepare/sqlsrv_execute:

  • Establishing a connection to the server (see the Creating a Connection (SQLSRV) section)
  • Defining a Transact-SQL query
  • Providing an array of parameter values (only required for parameterized queries)
  • Setting options on the connection (optional)

The following code (from the Example Application (SQLSRV Driver) in the product documentation) demonstrates the use of the sqlsrv_query function:

$params = array(&$_POST['query']);

$tsql = "SELECT ProductID, Name, Color, Size, ListPrice

         FROM Production.Product

         WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0";

$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);

$getProducts = sqlsrv_query($conn, $tsql, $params, $options);

if ($getProducts === false)

        die( FormatErrors( sqlsrv_errors() ) );

Note   If query parameter variables are not prefixed with "&", the SQLSRV driver will attempt to bind all parameters by reference. If a parameter variable is not prefixed with "&", the driver will generate a warning.

The sqlsrv_query and sqlsrv_prepare functions each accept four parameters: $conn, $tsql, $params (optional), and $options (optional).

  • $conn - This required parameter is a PHP connection resource created with the sqlsrv_connect function (see the Creating a Connection (SQLSRV) section).
  • $tsql - This required parameter is a string that defines a Transact-SQL query. Question marks (?) are used as placeholders for parameter values.
  • $params - This optional parameter is an array of values that correspond (in order) to the parameter placeholders (question marks) in the query defined by the $tsql parameter. Each value in the $params array can be a literal value (such as 5), a PHP variable (such as $myVar), or an array with the following structure:

array($value [, $direction [, $phpType [, $sqlType]]])

This array is used to specify the parameter value, the parameter direction (in the case where the parameter is being passed to a stored procedure), the PHP type of the parameter, and the SQL Server type of a value sent to the server. For more information about this array, see the Sending Images to the Server section. For more information, see Using Directional Parameters, How to: Send Data as a Stream, and How to: Specify SQL Server Data Types When Using the SQLSRV Extension in the product documentation.

  • $options - This optional parameter is an associative array that sets properties on the query. Three keys are supported: QueryTimeout, SendStreamParamsAtExec, and Scrollable. The QueryTimeout key sets the maximum time in seconds that a query is allowed to run. The SendStreamParamsAtExec key determines if all stream data is sent at the time of query execution or if subsequent calls to sqlsrv_send_stream_data are necessary to send all stream data. (For more information, see How to: Send Data as a Stream.) The Scrollable key determines what type of cursor the result set is read with (forward, static, dynamic, or keyset). (For more information, see Specifying a Cursor Type and Selecting Rows.)

 Retrieving Data From the Server (SQLSRV)

The general pattern for retrieving data with the SQLSRV driver involves defining and executing a query (see the Executing a Query (SQLSRV) section) and then using one of the following three options to retrieve data from the result set.

Note  By default, these functions provide forward-only access to the rows of a result set. To enable scrolling through a result set, use the Scrollable option when preparing the query. For more information, see Specifying a Cursor Type and Selecting Rows in the product documentation.

When choosing which option to use, consider the following:

  • The sqlsrv_fetch_array and sqlsrv_fetch_object functions pull an entire row of data into script memory. This may not be desirable for rows that contain large amounts of data.
  • Data returned by the sqlsrv_fetch_array and sqlsrv_fetch_object functions will be typed according to the defaults PHP data types assigned by the driver. For more information, see Default PHP Data Types in the product documentation.
  • Using the combination of sqlsrv_fetch and sqlsrv_get_field allows you to specify the PHP data type of the returned data, including specification of the data as a stream.

For more information about retrieving data, see Retrieving Data in the product documentation.

 Retrieving Data as an Array (SQLSRV)

In this section, we examine code in the Example Application (SQLSRV Driver) that retrieves data as an array. The following code uses the sqlsrv_fetch_array function to retrieve one row at a time from a result set. Each row is retrieved as an associative array and is passed to the custom function PopulateProductsTable for processing. Note that the sqlsrv_has_rows function is used to determine if the result set contains rows and that the sqlsrv_num_rows function is used to determine the number of rows in the result set. The parameter passed to each of these functions ($getProducts) is a statement resource that was created with a scrollable cursor.

if(sqlsrv_has_rows($getProducts))

{

$rowCount = sqlsrv_num_rows($getProducts);

BeginProductsTable($rowCount);

while($row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC))

{

PopulateProductsTable( $row );

}

EndProductsTable();

}

 

The sqlsrv_fetch_array function accepts four parameters, $stmt, $fetchType (optional), $row (optional), and $offset (optional):

  • The $stmt parameter is a PHP statement resource created with sqlsrv_query or sqlsrv_execute (see the Executing a Query (SQLSRV) section).
  • The $fetchType parameter (optional) is a driver-defined constant that specifies what type of array will be returned: associative, numeric, or both. The corresponding constants are SQLSRV_FETCH_ASSOC, SQLSRV_FETCH_NUMERIC, and SQLSRV_FETCH_BOTH. By default, an array with both types of indices is returned.
  • The $row parameter (optional) is used to specify the row to access in a result set that uses a scrollable cursor. For more information, see sqlsrv_fetch_array in the product documentation.
  • The $offset parameter (optional) is used in combination with the $row parameter to specify which row to retrieve. For more information, see sqlsrv_fetch_array in the product documentation.

 Retrieving Images (SQLSRV)

In this section, we examine code in the Example Application (SQLSRV Driver) that retrieves an image from the server. The code below executes a query that retrieves an image from the server, specifies that the returned data be retrieved as a binary stream, and dumps the data to the page using the PHP fpassthru function:

$tsql = "SELECT LargePhoto

         FROM Production.ProductPhoto AS p

         JOIN Production.ProductProductPhoto AS q

         ON p.ProductPhotoID = q.ProductPhotoID

         WHERE ProductID = ?";

$params = array(&$_REQUEST['productId']);

$stmt = sqlsrv_query($conn, $tsql, $params);

if( $stmt === false )

{

     echo "Error in statement execution.</br>";

     die( print_r( sqlsrv_errors(), true));

} 

$getAsType = SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY);

if ( sqlsrv_fetch( $stmt ) )

{

   $image = sqlsrv_get_field( $stmt, 0, $getAsType);

   fpassthru($image);

}

 

The code above defines a parameterized Transact-SQL query ($tsql), specifies the parameter value ($params), and executes the query with the sqlsrv_query function (see the Executing a Query (SQLSRV) section). The result set is consumed by calling sqlsrv_fetch (to make the next row of the result set available for reading) followed by calling sqlsrv_get_field (to read one field in the active row). The sqlsrv_fetch function takes a single parameter ($stmt in the example) that is a PHP resource created by sqlsrv_query or sqlsrv_execute.

Note   If query parameter variables are not prefixed with "&", the SQLSRV driver will attempt to bind all parameters by reference. If a parameter variable is not prefixed with "&", the driver will generate a warning.

The sqlsrv_get_field takes three parameters, $stmt, $fieldIndex, and $getAsType (optional):

  • The $stmt parameter is a PHP resource corresponding to an executed statement. The next (or first) row of data is made available to the sqlsrv_get_field function by first passing the $stmt parameter to the sqlsrv_fetch function.
  • The $fieldIndex specifies the index of the field to be retrieved. Indices start at zero.
  • The $getAsType parameter (optional) is used to specify the PHP type (and encoding, in this case) of the returned data. If this parameter is not provided, data will be returned according to its default PHP type. For more information, see Default PHP Data Types in the product documentation.

For more information about retrieving images and binary/large data, see Retrieving Data as a Stream in the product documentation.

Retrieving data with the combination of the sqlsrv_fetch and sqlsrv_get_field functions can be used to specify the PHP type of returned data, not only for retrieving data as a stream. For more information, see How to: Specify PHP Data Types in the product documentation.

Retrieving Specific Rows in a Result Set (SQLSRV)

By default, the SQLSRV driver uses a forward-only cursor when retrieving rows in a result set. In other words, by default you can only retrieve rows by starting with the first row and then consuming the remaining rows in the order they were returned from the server. However, the SQLSRV driver also supports scrollable cursors, which allow you more flexibility in retrieving the rows of a result set. To execute a query with a scrollable cursor you must use the SCROLLABLE option in the $options array when calling sqlsrv_query or sqlsrv_prepare (for more information, see the Executing a Query (SQLSRV) section and Specifying a Cursor Type and Selecting Rows in the product documentation).

The following code uses a keyset cursor to retrieve 5th row in a result set (the first row in the result set is 0):

$tsql = "SELECT ProductID FROM Production.Product";

$params = array();

$queryOptions = array("Scrollable"=>"keyset");

$stmt = sqlsrv_query($conn, $tsql, $params, $queryOptions);     

$row = sqlsrv_fetch_array($stmt,

          SQLSRV_FETCH_ASSOC,

          SQLSRV_SCROLL_ABSOLUTE,

          4);

print_r($row);

 

 Sending Data to the Server (SQLSRV)

The general pattern for sending data to the server with the SQLSRV driver involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT query) with the sqlsrv_query function or the combination of the sqlsrv_prepare and sqlsrv_execute functions (see the Executing a Query (SQLSRV) section). For example, the code below (from the Example Application (SQLSRV Driver) in the product documentation) sends a product review to the server using the combination of the sqlsrv_prepare and sqlsrv_execute functions:

$comments = "data://text/plain,".$_REQUEST['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() ) );

 

The parameters for sqlsrv_prepare are the same as those for sqlsrv_query (see the Executing a Query (SQLSRV) section). The sqlsrv_execute function takes one parameter ($insertReview in the example) that is a PHP resource specifying the prepared statement to be executed.

Note    The query in the example could have been executed with the sqlsrv_query function. The recommended practice for executing a single query is to use the sqlsrv_query function. The use of sqlsrv_prepare and sqlsrv_execute in the example is done to demonstrate how these functions are used together.)

Note   If query parameter variables are not prefixed with "&", the SQLSRV driver will attempt to bind all parameters by reference. If a parameter variable is not prefixed with "&", the driver will generate a warning.

This example highlights the driver's streaming capabilities. The customer's comments ($comments) are opened as a text stream ($stream), which is a parameter in the query. By default, all stream data is sent to the server at the time of query execution. However, the driver also provides functionality that allows up to 8KB of stream data to be sent to the server at a time. For more information, see the Sending Images to the Server (SQLSRV) section below or How to: Send Data as a Stream in the product documentation.

For more information about sending data to the server, see Updating Data in the product documentation.

 Sending Images to the Server (SQLSRV)

In this section, we examine code in the Example Application (SQLSRV Driver) that sends an image to the server as a binary stream. The following code opens an image as a stream before sending it to the server:

$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() ) );

 

Note   If query parameter variables are not prefixed with "&", the SQLSRV driver will attempt to bind all parameters by reference. If a parameter variable is not prefixed with "&", the driver will generate a warning.

As noted in the previous example, the general pattern for sending data to the server involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT statement). The notable differences in this example are described here:

  • Types are specified in the in the $params array. The PHP type must be specified here so that the driver will interpret the data as a binary stream. The SQL Server type must be specified so that the server will interpret the incoming data correctly.

For more information, see How to: Specify PHP Data Types and How to: Specify SQL Server Data Types in the product documentation.

  • The $options parameter is used when calling sqlsrv_prepare. The default behavior is to send all stream data to the server at the time of query execution. However, data can be sent to the server in chunks up to 8KB in size by setting "SendStreamParamsAtExec" to 0 in the $options parameter. When this is done, calls to sqlsrv_send_stream data (after query execution) are required to send stream data to the server (see next bullet). For more information, sqlsrv_send_stream_data see in the product documentation.

 Moving Through Multiple Result Sets (SQLSRV)

The SQLSRV driver provides the sqlsrv_next_result function for moving forward through multiple result sets returned by batch queries or stored procedures. This function makes the next result set, row count, or output parameter of an active statement available for reading.

The code below demonstrates how to use sqlsrv_next_result to move through results. The code here is taken from the Example Application (SQLSRV Driver) in the product documentation and immediately follows the code in the previous example. The $uploadPic statement corresponds to a batch query in the previous example. The code here moves to the second result in that statement and uses the retrieved value to execute a query that associates the new ProductPhotoID with a ProductID:

/*Skip the open result set (row affected). */

$next_result = sqlsrv_next_result($uploadPic);

if( $next_result === false )

die( FormatErrors( sqlsrv_errors() ) );

                    

/* Fetch the next result set. */

if( sqlsrv_fetch($uploadPic) === false)

die( FormatErrors( sqlsrv_errors() ) );

 

/* Get the first field - the identity from INSERT. */

photoID = sqlsrv_get_field($uploadPic, 0);

 

/* Associate the new photoID with the productID. */

$tsql = "UPDATE Production.ProductProductPhoto

         SET ProductPhotoID = ?

         WHERE ProductID = ?";

$reslt = sqlsrv_query($conn, $tsql, array($photoID, $_POST['productid']));

if($reslt === false )

die( FormatErrors( sqlsrv_errors() ) );

 

The required parameter for the sqlsrv_next_result function is a PHP resource corresponding to an active statement. It is not necessary to call sqlsrv_next_result to access the first result of a statement. This function will return null if there are no more results on the statement.

 Handling Errors and Warnings (SQLSRV)

The SQLSRV driver provides the sqlsrv_errors function for retrieving details about errors and warnings. If an error occurs in any sqlsrv function, the function returns false and the error details are added to the error collection. The sqlsrv_errors function provides access to this error collection.

Note    By default, warnings are treated as errors with a few exceptions: warnings that correspond to the SQLSTATE values 01000, 01001, 01003, and 01S02 are never treated as errors. This default behavior can be changed so that warnings are not treated as errors. For more information, see How to: Configure Error and Warning Handling in the product documentation and the Configuring the Driver section.

The general pattern for using sqlsrv_errors is to check the return value of a sqlsrv function and then handle errors accordingly. This code from the Example Application (SQLSRV Driver) in the product documentation demonstrates the pattern:

 

if( sqlsrv_execute($insertReview) === false )

die( FormatErrors( sqlsrv_errors() ) );

 

The sqlsrv_errors function returns a collection of arrays, one array for each error that occurred. Each array contains detailed error information. The custom function FormatErrors in the Example Application (SQLSRV Driver) simply iterates through the collection of arrays and displays error information:

 

function FormatErrors( $errors )

{

/* Display errors. */

echo "Error information: <br/>"; 

foreach ( $errors as $error )

{

     echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";

     echo "Code: ".$error['code']."<br/>";

     echo "Message: ".$error['message']."<br/>";

}

}

 

When evaluating the return value of a sqlsrv function, it is best to use the PHP triple equals operator (===). This is because all sqlsrv functions return false if an error occurs. For sqlsrv functions that could return some value that PHP evaluates to false it is important to use the triple equals operator to force a literal comparison. For example, sqlsrv_fetch could return null if there are no more rows in a result set. In this case, using a double equals operator (==) to check for an error ($result == false) would evaluate to true, resulting in unexpected program flow.

For more information, see Handling Errors and Warnings in the product documentation.

Using the PDO Driver

Loading the Driver (PDO)

You can download the PDO driver from the Microsoft Download Center or by using the Web Platform Installer. Included in the download are several .dll files, each with a name that indicates whether it is the procedural or PDO driver (sqlsrv or pdo), whether it is compatible with PHP 5.3 or PHP 5.2 (53 or 52), whether it thread-safe or non-thread-safe (ts or nts), and which compiler the driver was compiled with (vc6 or vc9). For example, the php_pdo_sqlsrv_53_nts_vc9.dll file is the PDO driver, is compatible with PHP 5.3, is non-thread-safe, and was compiled with Visual C++ 9 (vc9) compiler. Note that the recommended way to run PHP with Internet Information Services is to use the FastCGI module and a non-thread-safe version of PHP (and therefore a non-thread-safe version of the PDO driver). Whether you choose a vc6 or vc9 version of the driver will depend on the compiler that your version of PHP was compiled with. For more information about which .dll file you should use, see System Requirements.

Loading the PDO driver is similar to loading any PHP extension:

  1. Put the driver file in your PHP extension directory.
  2. Modify the php.ini file to include the extension. For example:

extension=php_pdo_sqlsrv_53_nts_vc9.dll

  1. See Figure 1 (PDO) below for more detail.
  2. Restart the Web server.

For more information, see Loading the Driver in the product documentation.

Configuring the Driver (PDO)

The PDO driver has one configuration option:

  • LogSeverity: Use this option to specify what to log after logging has been turned on. The default setting is 0 (nothing is logged by default).

For more information, see Logging Activity in the product documentation.

Configuration options can be set in the php.ini file. The figure below shows the Dynamic Extensions section of the php.ini file modified to load the driver and log errors, warnings, and notices.

 

Figure 1 (PDO): The Dynamic Extensions section of the php.ini.

 

One way to be sure that the driver is loaded and to see the configuration settings is to run a script that calls the phpinfo() function. To do this, follow these steps:

  1. Open a text file and copy the following code into it:

<?php phpinfo(); ?>

  1. Save the file as info.php in your Web server's root directory.
  2. Open a browser and go to http://localhost/info.php.
  3. Scroll down the resulting page to find the sqlsrv section.

The following figure shows the pdo_sqlsrv section of the phpinfo() page. This output confirms the driver is loaded and the configuration settings are set to default values.

 

Figure 2: The pdo_sqlsrv section of the phpinfo() page.

 Creating A Connection

The PDO driver is an object oriented interface. A connection is established by creating a new PDO object. The code shown here (from the Example Application (PDO Driver) in the product documentation) establishes a connection to the local instance of SQL Server Express and specifies the AdventureWorks database as the database in use:

$serverName = "(local)\sqlexpress";

try

{

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

  $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

}

catch(Exception $e)

{

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

}

 

The code above establishes a connection using Windows Authentication by passing empty strings for the $username and $password parameters. In most scenarios, this means that the Web server's process identity or thread identity (if the Web server is using impersonation) is used to connect to the server, not an end-user's identity.

The PDO::__construct function accepts four parameters: $dsn, $username (optional), $password (optional), and $driverOptions (optional).

  • $dsn - This required parameter is a string that contains the prefix name (always sqlsrv), a colon, and the server keyword. For example, "sqlsrv:server=(local)\sqlexpress". You can optionally specify other connection keywords. (See Connection Options for a description of the server keyword and the other connection keywords.) The entire $dsn parameter is in quotation marks, so each connection keyword should not be individually quoted.
  • $username - This optional parameter is a string that contains the user's login name. To connect using SQL Server Authentication, specify the login ID. To connect using Windows Authentication, specify "".
  • $password - This optional parameter is a string that contains the user's password. To connect using SQL Server Authentication, specify the password. To connect using Windows Authentication, specify "".
  • $driverOptions - This optional parameter is used to specify PDO Driver Manager attributes, and SQL Server Driver for PHP specific driver attributes -- PDO::SQLSRV_ATTR_ENCODING and PDO::SQLSRV_ATTR_DIRECT_QUERY. An invalid attribute will not generate an exception. Invalid attributes generate exceptions when specified with PDO::setAttribute.

For more information about creating a connection, see the Connection Pooling (SQLSRV and PDO) section later in this paper and  Connecting to the Server in the product documentation.

Executing a Query (PDO)

The PDO interface provides three options for executing queries: the PDO::exec method, the PDO::query method, or the combination of the PDO::prepare and PDOStatement::execute methods.

The PDO::exec method does both statement preparation and execution, but it only returns the number of rows affected by a query. The PDO::query method executes a query and returns a result set, but does not allow for execution of parameterized queries. The combination of the PDO::prepare and PDOStatement::execute methods prepares and executes a query while allowing for parameterized queries.

When a statement is prepared, an execution plan is created on the server. This execution plan is reused when the same query is run again (even if the query is executed with different parameter values). This not only can improve database performance, but it can prevent SQL Injection attacks (see How and Why to Use Parameterized Queries for more information).

The general programming pattern for using the combination of the PDO::prepare and PDOStatement::execute methods requires doing the following first:

  • Establishing a connection to the server (see the Creating a Connection (PDO) section)
  • Defining a Transact-SQL query
  • Providing an array of parameter values (only required for parameterized queries)
  • Setting options on the connection (optional)

 

The following code (from the Example Application (PDO Extension) in the product documentation) demonstrates the use of the PDO::prepare and PDOStatement::execute methods:

$params = 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($params);

 

The PDO::prepare method accepts two parameters: $tsql and $options (optional).

  • $tsql - This required parameter is a string that defines a Transact-SQL query. Question marks (?) or named parameters (prefixed with a colon) are used as placeholders for parameters.
  • $params - This optional parameter is an array of key-value pairs that allow you to set options on the query. The possible keys are PDO::ATTR_CURSOR, PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ATTR_DIRECT_QUERY, and PDO::SQLSRV_ATTR_QUERY_TIMEOUT. For information about the possible corresponding values, see PDO::prepare.

The PDOStatement::execute method accepts one optional parameter: $params.

  • $params: This optional parameter is an associative array that contains the values of parameters in the query that is being executed. If question marks (?) are used as parameter placeholders in the query, the $params array is simply an array of parameter values. If named paramerters are used in the query, the $params array is an array of key-value pairs where each key is the name used as the placeholder and the value is the parameter value. See PDO::prepare for examples of each type of usage.

Note The PDO driver supports both question marks (?) and named parameters (:paramName) as placeholders for parameter values. For an example that demonstrates the use of named parameters, see PDO::prepare.

Retrieving Data from the Server (PDO)

The general pattern for retrieving data with the PDO driver involves defining and executing a query (see the Executing a Query (PDO) section) and then using one of the following four methods to retrieve data from the result set:

For more information about retrieving data, see Retrieving Data in the product documentation.

Retrieving Data as an Array (PDO)

In this section, we examine code in the Example Application (PDO Driver) that retrieves data as an array. The following code uses the PDOStatement::fetchAll method to retrieve all rows of a result set. While this does bring an entire result set into memory (and with it concerns about memory usage), it also allows us to count the number of rows that are in the result set. Each row is stored in the array ($products) as another array. The code below iterates over the result set and passes each row to the custom function PopulateProductsTable for processing. Note that $getProducts object is a PDO::Statement object (see the Executing a Query (PDO) section for more information). 

$products = $getProducts->fetchAll(PDO::FETCH_ASSOC);

$productCount = count($products);

if($productCount > 0)

{

BeginProductsTable($productCount);

foreach( $products as $row )

{

PopulateProductsTable( $row );

}

EndProductsTable();

}

 

The PDOStatement::fetchAll method accepts three parameters - $fetch_style, $column_index (optional), and $ctor_args (optional):

  • The $fetch_style parameter (optional) is a constant specifying the format of the row data. See PDOStatement::fetch for a complete list of values.
  • The $column_index parameter (optional) is an integer value representing the column to return if $fetch_style is PDO::FETCH_COLUMN. Zero (0) is the default.
  • The $ctor_args parameter (optional) is an array of the parameters for a class constructor when $fetch_style is PDO::FETCH_CLASS or PDO::FETCH_OBJ.

Retrieving Images (PDO)

In this section, we examine code in the Example Application (PDO Extension) that retrieves an image from the server. The code below executes a query that retrieves an image from the server, specifies that the returned data be retrieved as binary data, and dumps the data to the page:

$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;

 

The code above defines a parameterized Transact-SQL query ($tsql), prepares the query with the PDO::prepare method, and executes the query with the PDOStatement::execute method (see the Executing a Query (PDO) section). The PDOStatement::bindColumn method is then used to bind the first column of the result to the $image variable while specifying that the incoming data be treated as binary data. Finally, the result set is consumed by using the PDOStatement::fetch method.

Note The PDO driver supports both question marks (?) and named parameters (:paramName) as placeholders for parameter values. For an example that demonstrates the use of named parameters, see PDO::prepare.

The PDOStatement::bindColumn method takes five parameters - $column, $param, $type (optional), $maxLen (optional), and $driverdata (optional):

  • The $column parameter specifies the number of the column (1-based index) or name of the column in the result set.
  • The $param specifies the name of the PHP variable to which the column will be bound.
  • The $type parameter (optional) specifies the data type of the parameter, represented by a PDO::PARAM_* constant.
  • The $maxLen parameter (optional) is not used by this PDO implementation.
  • The $driverdata parameter (optional) is used to specify driver-specific data. For example, you could specify PDO::SQLSRV_ENCODING_UTF8 to bind the column to a variable as a string encoded in UTF-8.

The PDOStatement::fetch method takes three parameters - $fetch_style (optional), $cursor_orientation (optional), and $cursor_offset (optional):

  • The $fetch_style parameter (optional) is a constant that specifies the format of the returned data. See PDOStatement::fetch for a complete list of possible values.
  • The $cursor_orientation parameter (optional) is a constant that specifies the row to retrieve when the prepare statement specifies PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL. See PDOStatement::fetch for a complete list of possible values.
  • The $cursor_offset parameter (optional) specifies the row to fetch when $cursor_orientation is either PDO::FETCH_ORI_ABS or PDO::FETCH_ORI_REL and PDO::ATTR_CURSOR is PDO::CURSOR_SCROLL.

Sending Data to the Server (PDO)

The general pattern for sending data to the server with the PDO driver involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT query) with one of the methods mentioned in the the Executing a Query (PDO) section. For example, the code below (from the Example Application (PDO Driver) in the product documentation) sends a product review to the server using the combination of the PDO::prepare and PDOStatement::execute methods:

 

$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);

Note The PDO driver supports both question marks (?) and named parameters (:paramName) as placeholders for parameter values. For an example that demonstrates the use of named parameters, see PDO::prepare.

For more information about sending data to the server, see Updating Data in the product documentation.

Sending Images to the Server (PDO)

In this section, we examine code in the Example Application (PDO Driver) that sends an image to the server as a binary stream:

$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();

 

As noted in the previous example, the general pattern for sending data to the server involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT statement). The notable difference in this code snippet is in the use of the PDOStatement::bindParam method. In the code above, the first parameter in the query (place held by a question mark (?)) is bound to the stream handle $fileStream. Additionally, the data type for the parameter is specified as PDO::PARAM_LOB and the encoding is specified as binary.

Note The PDO driver supports both question marks (?) and named parameters (:paramName) as placeholders for parameter values. For an example that demonstrates the use of named parameters, see PDO::prepare.

The PDOStatement::bindParam method takes five parameters - $parameter, $variable, $data_type (optional), $length (optional), and $driver_options (optional):

  • The $parameter parameter is a parameter identifier. For a statement using named placeholders, a parameter name (:name). For a prepared statement using the question mark syntax, this will be the 1-based index of the parameter.
  • The $variable parameter is the name of the PHP variable to bind to the SQL statement parameter.
  • The $data_type parameter (optional) is a PDO::PARAM_* constant. The default is PDO::PARAM_STR.
  • The $length parameter (optional) is the length of the data type. You can specify SQLSRV_PARAM_OUT_DEFAULT_SIZE to indicate the default size when using PDO::PARAM_INT or PDO::PARAM_BOOL in $data_type.
  • The $driver_options parameter (optional) specifies driver-specific options. For example, you could specify PDO::SQLSRV_ENCODING_UTF8 to bind the column to a variable as a string encoded in UTF-8.

For more information, see PDOStatement::bindParam in the product documentation.

Moving Through Multiple Result Sets (PDO)

The PDO driver provides the PDOStatement::nextRowset method for moving forward through multiple results returned by batch queries or stored procedures. This function makes the next result set of an active statement available for reading.

The Example Application (PDO Driver) in the product documentation does not demonstrate usage of the PDOStatement::nextRowset method. For more information and examples, see PDOStatement::nextRowset in the product documentation.

Handling Errors and Warnings (PDO)

The PDO driver implements error handling as defined by the PDO specification. For more information and examples, see the documentation for the PDO::setAttribute method.

The code below from the Example Application (PDO Driver) demonstrates how to use the PDO::setAttribute method to specify that errors be handled as exceptions for an instance of the PDO class:

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

$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Throughout the Example Application (PDO Driver), try...catch blocks are used to catch errors as exceptions. The following code demonstrates how this is done:

 

try

{

$tsql = "SELECT Name FROM Production.Product WHERE ProductID = ?";

$getName = $conn->prepare($tsql);

$getName->execute(array($_GET['productid']));

$name = $getName->fetchColumn(0);

}

catch(Exception $e)

{

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

}

Connection Pooling (SQLSRV and PDO)

The Microsoft Drivers for PHP for SQL Server are designed to use ODBC connection pooling. By default, connection pooling is enabled. When you connect to a server, both the SQLSRV and PDO drivers attempt to use a pooled connection before it creating a new one. If an equivalent connection is not found in the pool, a new connection is created and added to the pool. The drivers determine whether connections are equivalent based on a comparison of connection strings. In the SQLSRV driver, calling sqlsrv_close on a connection returns the connection to the pool. In the PDO driver, setting a PDO instance to null returns the connection to the pool. However, if the connection was created with the ConnectionPooling attribute set to false (see the Creating a Connection (SQLSRV) section), calling sqlsrv_close or setting a PDO instance to null closes the connection.

Note* * The first time you execute a query on a connection that was retrieved from a pool, the driver tells the server to reset the connection prior to executing the query. Resetting the connection returns the connection to its original state. For example, resetting the connection deletes any temporary objects and rolls back any pending transactions.

For more information, see Connection Pooling in the product documentation.

SQL Database Access (SQLSRV and PDO)

Both the SQLSRV and PDO drivers support access to SQL Database. When an application is developed using any edition of SQL Server as the application's database, often the only code change necessary to run the application with a SQL Database is in the connection string. For example, the following code demonstrates how to connect to a SQL Database using the SQLSRV driver:

$serverName = "tcp:ServerID.database.windows.net, 1433";

$connectionOptions = array("Database" => "ExampleDB",

                           "UID" => "Username@ServerID",

                           "PWD" => "Password");

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

if($conn === false)

{

     die(print_r(sqlsrv_errors(), true));

}

 

This code demonstrates how to connect to SQL Database using the PDO driver:

$serverName = "tcp:ServerID.database.windows.net, 1433";

try

{

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

                  "User@ServerID", "Password");

  $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

}

catch(Exception $e)

{

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

}

Once the connection code has been changed, the remaining application code may require some changes. For more information, see Getting Started with PHP and Windows Azure SQL Database.

Resources

The following resources are available for developing applications with the Microsoft Drivers for SQL Server for PHP:

Conclusion

The Microsoft Drivers for SQL Server for PHP provide fast and reliable access to SQL Server data using PHP. The drivers leverage both Microsoft and PHP technologies (such as Windows Authentication, ODBC connection pooling, and PHP streams) to enable the development of rich PHP Web applications. 

For more information:

SQL Server Web site: http://www.microsoft.com/sqlserver/

SQL Server TechCenter: http://technet.microsoft.com/en-us/sqlserver/

SQL Server DevCenter: http://msdn.microsoft.com/en-us/sqlserver/

Data Platform DevCenter: http://msdn.microsoft.com/en-us/data/