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.
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.
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:
extension=php_sqlsrv_53_nts_vc9.dll
For more information, see Loading the Driver in the product documentation.
The SQLSRV driver has three configuration options:
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:
<?php phpinfo(); ?>
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.
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 = "myServer\instanceName";
-or-
$serverName = "myServer, 1521";
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.
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:
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)
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).
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.
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:
For more information about retrieving data, see Retrieving Data in the product documentation.
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):
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.
The sqlsrv_get_field takes three parameters, $stmt, $fieldIndex, and $getAsType (optional):
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.
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);
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 )
if( sqlsrv_execute($insertReview) === false )
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.)
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.
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 )
if( sqlsrv_execute($uploadPic) === false )
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:
For more information, see How to: Specify PHP Data Types and How to: Specify SQL Server Data Types in the product documentation.
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 )
/* Fetch the next result set. */
if( sqlsrv_fetch($uploadPic) === false)
/* 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 = ?
$reslt = sqlsrv_query($conn, $tsql, array($photoID, $_POST['productid']));
if($reslt === false )
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.
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:
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.
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:
extension=php_pdo_sqlsrv_53_nts_vc9.dll
The PDO driver has one configuration option:
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.
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.
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:
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).
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.
The general programming pattern for using the combination of the PDO::prepare and PDOStatement::execute methods requires doing the following first:
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']);
$getProducts = $conn->prepare($tsql);
$getProducts->execute($params);
The PDO::prepare method accepts two parameters: $tsql and $options (optional).
The PDOStatement::execute method accepts one optional parameter: $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.
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:
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 )
The PDOStatement::fetchAll method accepts three parameters - $fetch_style, $column_index (optional), and $ctor_args (optional):
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:
$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.
The PDOStatement::bindColumn method takes five parameters - $column, $param, $type (optional), $maxLen (optional), and $driverdata (optional):
The PDOStatement::fetch method takes three parameters - $fetch_style (optional), $cursor_orientation (optional), and $cursor_offset (optional):
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:
&$_POST['comments']);
$insertReview = $conn->prepare($tsql);
$insertReview->execute($params);
In this section, we examine code in the Example Application (PDO Driver) that sends an image to the server as a binary stream:
VALUES (?)";
$uploadPic = $conn->prepare($tsql);
$uploadPic->bindParam(1,
$fileStream,
$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.
The PDOStatement::bindParam method takes five parameters - $parameter, $variable, $data_type (optional), $length (optional), and $driver_options (optional):
For more information, see PDOStatement::bindParam in the product documentation.
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.
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", "", "");
Throughout the Example Application (PDO Driver), try...catch blocks are used to catch errors as exceptions. The following code demonstrates how this is done:
$tsql = "SELECT Name FROM Production.Product WHERE ProductID = ?";
$getName = $conn->prepare($tsql);
$getName->execute(array($_GET['productid']));
$name = $getName->fetchColumn(0);
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.
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:
$conn = new PDO("sqlsrv:server=$serverName;Database=DBName",
"User@ServerID", "Password");
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.
The following resources are available for developing applications with the Microsoft Drivers for SQL Server for PHP:
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.
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/