Accessing SQL Server Databases with PHP

SQL Server Technical Article

Writer: Brian Swan
Published: August 2008
Applies to: SQL Server 2005 and SQL Server 2008 – all editions

Summary:

The SQL Server 2005 Driver for PHP is a Microsoft-supported extension of PHP 5 that provides data access to SQL Server 2005 and SQL Server 2008. The extension provides a procedural interface for accessing data in all editions of SQL Server 2005 and SQL Server 2008. The SQL Server 2005 Driver for PHP API provides a comprehensive data access solution from PHP, and includes support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling. This paper discusses how to use several of these features by closely examining parts of the Example Application in the SQL Server 2005 Driver for PHP product documentation in MSDN. 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.

Introduction

The Microsoft SQL Server 2005 Driver for PHP allows PHP developers to access data in SQL Server 2005 and SQL Server 2008 databases. The driver includes support for Windows and SQL Server Authentication methods, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling.

This paper explains how to load and configure the driver, and discusses how to leverage several of the features mentioned above. Parts of the Example Application in the product documentation will be used to demonstrate these programming scenarios.

For more information about a complete list of driver features and functions, see the API Reference in the SQL Server 2005 Driver for PHP product documentation in MSDN. The prefix for all driver functions is sqlsrv.

The SQL Server Driver for PHP relies on the Microsoft SQL Server 2005 ODBC Driver to handle the low-level communication with SQL Server. As a result, the SQL Server Driver for PHP is only supported on Windows. Microsoft provides support for this driver under its normal support methods. While the source code for this driver has been made available on the codeplex.com website, Microsoft supports only the signed version of the driver from the MSDN download site.

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.

Loading the Driver

You can download the SQL Server 2005 Driver for PHP at the Microsoft Download Center. Included in the download are two .dll files: php_sqlsrv.dll and php_sqlsrv_ts.dll.

These files correspond to the non-thread-safe and thread-safe versions of the driver respectively.

Loading the SQL Server 2005 Driver for PHP is similar to loading any PHP extension:

1. Put the extension file (php_sqlsrv.dll or php_sqlsrv_ts.dll) in the PHP extension
directory. If you are running the non-thread-safe version of PHP (php5.dll),
you should use the non-thread-safe version of the driver (php_sqlsrv.dll).
Similarly, if you are running the thread-safe version of PHP (php5ts.dll), you
should use the thread-safe version of the driver (php_sqlsrv_ts.dll).

2. Modify the php.ini file to include the extension.

Depending on which version of the driver you want to load (non-thread-safe or thread-safe), you will need to add one of the following lines to the Dynamic Extensions section
of your php.ini file:

extension=php_sqlsrv.dll

-or-

extension=php_sqlsrv_ts.dll

See Figure 1 below for more detail.

3. Restart the Web server.

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

Configuring the Driver

The SQL Server 2005 Driver for PHP 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 SQL Server 2005 Driver for PHP 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.

Settings for the 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 thread-safe version of the driver, log activity on
all subsystems, log all activity (errors, warnings, and notices), and turn off
the WarningsReturnAsErrors behavior.

Figure
1: 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 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(); ?>

2. Save the file as
info.php in your Web server’s root directory.

3. Open a browser
and go to http://localhost/info.php.

4. 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

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

$serverName = "(local)";

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

/* Connect using Windows
Authentication. */

$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. In the code above,
a connection is established to the local server. This parameter can also 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 sqlsrv_connect 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 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 section later in this paper.

Connection Pooling

The SQL Server 2005 Driver for PHP is designed to use ODBC
connection pooling. By default, connection pooling is enabled. When you connect
to a server, the driver attempts to use a pooled connection before it creates a
new one. If an equivalent connection is not found in the pool, a new connection
is created and added to the pool. The driver determines whether connections are
equivalent based on a comparison of connection strings. Calling sqlsrv_close on a
connection returns the connection to the pool. However, if the connection was
created with the ConnectionPooling attribute set to false (see the Creating
a Connection section), calling sqlsrv_close 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.

Executing a Query

The SQL Server 2005 Driver for PHP
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.
For more information, see Comparing
Execution Functions in the product documentation.

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 section)

·
Defining a Transact-SQL statement

·
Providing an array of parameter values (only required for
parameterized queries)

· Setting options on the connection (optional)

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

$tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product WHERE
Name LIKE '%' + ? + '%' AND ListPrice > 0.0";
$params = array( $_REQUEST['query'] );
$getProducts = sqlsrv_query(
$conn, $tsql, $params);

if ( $getProducts === false){ die( FormatErrors( sqlsrv_errors() ) ); }

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

· $conn – This required parameter is a PHP connection
resource created with the sqlsrv_connect function (see the Creating a
Connection section).

· $tsql – This required parameter is a string that
defines a Transact-SQL query. Question marks (?) are used as placeholders for
parameters.

· $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 in the product documentation.

· $options – This optional parameter (not shown in
the example) is an associative array that sets properties on the query. Two
keys are supported: QueryTimeout and SendStreamParamsAtExec. 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.

Retrieving Data from the
Server

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

· The sqlsrv_fetch_array
function (retrieves a row of data as an array).

· The sqlsrv_fetch_object
function (retrieves a row of data as a PHP object).

· The combination of the sqlsrv_fetch and sqlsrv_get_field functions (retrieves a single field from a row of data).

Note These functions provide forward-only
access to the rows of a result set.

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 data retrieval functionality, see Comparing Data Retrieval Functions in the product documentation.

For more information about retrieving data with the SQL Server 2005 Driver for PHP see Retrieving Data in the product documentation.

Retrieving Data as an Array

In this section, we examine code in the Example Application 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:

$productCount = 0;
while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC))
{
PopulateProductsTable(
$row );
$productCount++;
}

The sqlsrv_fetch_array function
accepts two parameters, $stmt and $fetchType (optional):

· The $stmt parameter is a PHP statement resource created
with sqlsrv_query or sqlsrv_execute (see the Executing a Query
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.

Retrieving Images

In this section, we examine code in the Example Application 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 Web page using the PHP fpassthru
function:

/* Get the product picture for
a given product ID. */

$tsql = "SELECT LargePhoto
FROM Production.ProductPhoto AS p
JOIN Production.ProductProductPhoto AS q
ON p.ProductPhotoID = q.ProductPhotoID
WHERE ProductID = ?";

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

/* Execute the query. */

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

if( $stmt === false )
{
echo "Error in statement execution.</br>";
die(
print_r( sqlsrv_errors(), true));
}

/* Retrieve the image as a binary stream. */

$fieldIndex = 0;
$getAsType = SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY);

if ( sqlsrv_fetch( $stmt ) )
{
$image = sqlsrv_get_field( $stmt, $fieldIndex, $getAsType);
fpassthru($image);
}
else
{
echo
"Error in retrieving data.</br>";

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

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 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):

· 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 if 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.

Sending Data to the Server

The general pattern for sending data to the server 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 section). For example, the code below (from the Example Application in the product documentation) sends a product review to the server using the combination of the sqlsrv_prepare and sqlsrv_execute functions:

/*Prepend the review so it can be opened as a stream.*/

$comments = "data://text/plain,".$_REQUEST['comments'];
$stream = fopen( $comments, "r" );

$tsql = "INSERT INTO
Production.ProductReview (ProductID,ReviewerName,ReviewDate,EmailAddress,
Rating,Comments)
VALUES (?,?,?,?,?,?)";

$params = array($_REQUEST['productid'],
$_REQUEST['name'],
date("Y-m-d"),
$_REQUEST['email'],
$_REQUEST['rating'],
$stream);

/* Prepare and execute the statement. */

$insertReview =
sqlsrv_prepare($conn, $tsql, $params);

if( $insertReview === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

/* By default, all stream data is sent at the time of query execution. */

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
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 section below or How to: Send Data as a
Stream in the product documentation.

For more information about sending
data to the server with the SQL Server 2005 Driver for PHP, see Updating Data in the
product documentation.

Sending
Images to the Server

In this section, we examine code in
the Example Application

that sends an image to the server as a binary stream. The following code opens
an image as a stream and then sends the file up to the server in parts up to 8KB
at a time:

$tsql = "INSERT INTO Production.ProductPhoto
(LargePhoto)VALUES (?);
SELECT SCOPE_IDENTITY() AS PhotoID";
$fileStream =
fopen($_FILES['file']['tmp_name'], "r");
$params = array(array($fileStream, SQLSRV_PARAM_IN,SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),SQLSRV_SQLTYPE_VARBINARY('max')));

/* Turn off the default behavior of sending all stream data to the server at the time of query execution. */

$options = array("SendStreamParamsAtExec"=>0);
$uploadPic = sqlsrv_prepare($conn, $tsql, $params, $options);

if( $uploadPic === false )
{ die( FormatErrors( sqlsrv_errors() ) );}

if( sqlsrv_execute($uploadPic) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

/* Stream data to the database
in chunks. */

while( $success = sqlsrv_send_stream_data( $uploadPic))

{

}

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 of sending all stream data to the server at the time of
query execution is turned off by setting "SendStreamParamsAtExec" to
0 in the $options parameter. When this behavior is turned off, calls to sqlsrv_send_stream data

(after query execution) are required to send stream data to the server (see
next bullet).

· The sqlsrv_send_stream
data function is used to send data up to 8KB of stream data to the
server at a time. Turning off the default behavior of sending all stream data
to the server at once and using sqlsrv_send_stream_data to send stream
data allows for flexibility in application design. For example, it allows an
application to present users with a progress bar when uploading a large image.

Moving
Through Groups of Result Sets

The SQL Server 2005 Driver for PHP
provides the sqlsrv_next_result

function for moving forward through multiple results 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
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 (first) result
set (rows 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 = ?";

$params = array($photoID, $_REQUEST['productid']);

if( sqlsrv_query($conn, $tsql, $params) === 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.

For more information about moving
through results, see How to: Work with
Multiple Result Sets and How to: Detect Empty
Result Sets in the product documentation.

Handling
Errors and Warnings

The SQL Server 2005 Driver for PHP
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 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 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.

Resources

The following resources are available for
developing applications with the SQL Server 2005 Driver for PHP:

· Download site: SQL Server 2005 Driver for PHP in the Microsoft Download Center

· Peer-to-peer support: SQL Server Driver for PHP in the MSDN Forums

· Online documentation: SQL Server 2005 Driver for PHP Documentation in MSDN Library

· Source code: Microsoft SQL Server 2005 Driver for PHP in CodePlex

Conclusion

The SQL Server 2005 Driver for PHP provides fast and reliable
access to SQL Server data using PHP. The driver leverages 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/