Using the FILESTREAM Functionality of SQL Server 2008

Anonymous — Thu, 12 Feb 2009 01:17:00 GMT

In this post I want to demonstrate how the streaming capabilities of the SQL Server 2005 Driver for PHP work seamlessly with the new FILESTREAM functionality in SQL Server 2008.

The FILESTREAM functionality in SQL Server 2008 enables applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data. You can learn more about the FILESTREAM feature by starting here: Designing and Implementing FILESTREAM Storage.

To follow this demonstration, you will need to do two things first:

1) Enable FILESTREAM on an instance of SQL Server. Instructions for doing this are here: How to: Enable FILESTREAM.

2) Create a FILESTREAM-enabled database. Instructions for doing this are here: How to: Create a FILESTREAM-Enabled Database. The database I will use for demonstration purposes can be created with this script:

USE [master];
GO

IF EXISTS (SELECT * FROM sys.databases
           WHERE name = 'FilestreamDB')
    DROP DATABASE FilestreamDB;
GO

CREATE DATABASE FilestreamDB
ON
PRIMARY (NAME = F_Stream1, FILENAME = 'c:\data\FilestreamDB.mdf'),
    FILEGROUP FileStreamGroup1 CONTAINS

        FILESTREAM(NAME = F_Stream2, FILENAME = 'c:\data\filestream1')
    LOG ON (NAME = F_StreamLog1, FILENAME = 'c:\data\FilestreamDB_log.ldf')
GO

USE FilestreamDB;
GO

Create Table StreamTable (
     ID uniqueidentifier rowguidcol NOT NULL unique,
     myFileStream varbinary(max) FILESTREAM
)

Once you have completed these two steps, the coding patterns for sending and retrieving stream data with the SQL Server 2005 Driver for PHP are no different than those for sending/retrieving stream data from other column types (see How to: Send Stream Data and Retrieving Stream Data for more information).

Let’s look at an example of sending and retrieving FILESTREAM data.

Sending FILESTREAM Data

Although the code pattern for sending stream data to a FILESTREAM column is the same as it is for sending stream data to any column, the Transact-SQL that defines the query may new. A FILESTREAM-enabled database requires a uniqueidentifier column, and there is no function (such as SCOPE_IDENTITY()) to retrieve the generated GUID after performing an INSERT. So, in the code below, I’ve modified the Transact-SQL so I can select the GUID for use later. Also note that, in the parameter array, I’ve specified the PHP type as a binary stream and the SQL type as varbinary(‘max’) (see How to: Specify PHP Data Types and How to: Specify SQL Server Data Types for more information).

/* Define a parameterized query to insert data. */
$tsql = "DECLARE @id UNIQUEIDENTIFIER;
SET @id = NEWID();
INSERT INTO StreamTable (ID, myFileStream)

VALUES (@id, ?);
 SELECT @id AS ID";

/* Open data as a stream. */
$pic = fopen("C:\Pics\Tree.jpg","r");

/* Define the parameter array. */
/* Note the specification of the PHPTYPE and SQLTYPE. */
$params = array(
 array(
 $pic,
 SQLSRV_PARAM_IN,
 SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
 SQLSRV_SQLTYPE_VARBINARY('max')
 )
 );

/* Execute the query. */
$stmt = sqlsrv_query($conn, $tsql, $params);
if ($stmt === false) {
 echo "Error in statement execution.";
 die( print_r( sqlsrv_errors(), true));
}

Retrieving FILESTREAM Data

The coding pattern for retrieving FILESTREAM data is exactly as it is for retrieving any type of streamable data. In the code below, I have to do a little work to retrieve the GUID by moving to the next result set of the query executed in the code above.

/* Skip the rows affected result by moving to the next result. */
if(sqlsrv_next_result($stmt) === false)
{
 echo "Error in moving to next result.";
 die( print_r( sqlsrv_errors(), true));
}

/* Retrieve the first (only) row of the next result. */
if(sqlsrv_fetch($stmt) === false)
{
 echo "Error in retrieving row.";
 die( print_r( sqlsrv_errors(), true));
}

/* Get the first field of the row and assign the value to $id. */
if( !($id = sqlsrv_get_field($stmt, 0)) )
{
 echo "Error in retrieving field.";
 die( print_r( sqlsrv_errors(), true));
}

/* Define a parameterized query to retrieve the newly inserted data. */
$tsql = "SELECT myFileStream FROM StreamTable WHERE ID = ?";

/* Use the retrieved uniqueidentifier as the parameter value. */
$params = array($id);

/* Execute the query. */
$stmt = sqlsrv_query($conn, $tsql, $params);
if( $stmt === false )
{
 echo "Error in statement execution.";
 die( print_r( sqlsrv_errors(), true));
}

/* Retrieve the results as a binary stream and display in the browser. */
if ( sqlsrv_fetch( $stmt ) )
{
 $image = sqlsrv_get_field($stmt, 0,

SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
 fpassthru($image);
}
else
{
 echo "Error in retrieving data.";
 die(print_r( sqlsrv_errors(), true));
}

That’s it! There are many advantages to using the FILESTREAM functionality of SQL Server 2008, and it is easy to leverage this functionality with the streaming capabilities of the the SQL Server 2005 Driver for PHP.

Thanks.

Brian Swan

Programming Writer, SQL Server 2005 Driver for PHP

Search results matching tags 'PHP' and 'Filestream'

Using the FILESTREAM Functionality of SQL Server 2008