How to: Send Data as a Stream

Microsoft Drivers for PHP for SQL Server

previous page next page
Collapse image Expand Image Copy image CopyHover image

The Microsoft Drivers for PHP for SQL Server takes advantage of PHP streams for sending large objects to the server. The examples in this topic demonstrate how to send data as a stream. The first example uses the SQLSRV driver to demonstrate the default behavior, which is to send all stream data at the time of query execution. The second example uses the SQLSRV driver to demonstrate how to send up to eight kilobytes (8K) of stream data at a time to the server.

The third example shows how to send stream data to the server using the PDO_SQLSRV driver.

Example

The following example inserts a row into the Production.ProductReview table of the AdventureWorks database. The customer comments ($comments) are opened as a stream with the PHP fopen function and then streamed to the server upon execution of the query.

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console.

  Copy imageCopy Code 
<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Set up the Transact-SQL query. */
$tsql = "INSERT INTO Production.ProductReview (ProductID, 
                                               ReviewerName,
                                               ReviewDate,
                                               EmailAddress,
                                               Rating,
                                               Comments)
         VALUES (?, ?, ?, ?, ?, ?)";

/* Set the parameter values and put them in an array.
Note that $comments is opened as a stream. */
$productID = '709';
$name = 'Customer Name';
$date = date("Y-m-d");
$email = '[email protected]';
$rating = 3;
$comments = fopen( "data://text/plain,[ Insert lengthy comment here.]",
                  "r");
$params = array($productID, $name, $date, $email, $rating, $comments);

/* Execute the query. All stream data is sent upon execution.*/
$stmt = sqlsrv_query($conn, $tsql, $params);
if( $stmt === false )
{
     echo "Error in statement execution.\n";
     die( print_r( sqlsrv_errors(), true));
}
else
{
     echo "The query was successfully executed.";
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

The next example is the same as the example above, but the default behavior of sending all stream data at execution is turned off. The example uses sqlsrv_send_stream_data to send stream data to the server. Up to eight kilobytes (8K) of data is sent with each call to sqlsrv_send_stream_data. The script counts the number of calls made by sqlsrv_send_stream_data and displays the count to the console.

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console.

  Copy imageCopy Code 
<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Set up the Transact-SQL query. */
$tsql = "INSERT INTO Production.ProductReview (ProductID, 
                                               ReviewerName,
                                               ReviewDate,
                                               EmailAddress,
                                               Rating,
                                               Comments)
         VALUES (?, ?, ?, ?, ?, ?)";

/* Set the parameter values and put them in an array.
Note that $comments is opened as a stream. */
$productID = '709';
$name = 'Customer Name';
$date = date("Y-m-d");
$email = '[email protected]';
$rating = 3;
$comments = fopen( "data://text/plain,[ Insert lengthy comment here.]",
                  "r");
$params = array($productID, $name, $date, $email, $rating, $comments);

/* Turn off the default behavior of sending all stream data at
execution. */
$options = array("SendStreamParamsAtExec" => 0);

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

/* Send up to 8K of parameter data to the server with each call to
sqlsrv_send_stream_data. Count the calls. */
$i = 1;
while( sqlsrv_send_stream_data( $stmt)) 
{
     echo "$i call(s) made.\n";
     $i++;
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Although the examples in this topic send character data to the server, data in any format can be sent as a stream. For example, you can also use the techniques that are demonstrated in this topic to send images in binary format as streams.

  Copy imageCopy Code 
<?php
   $server = "(local)"; 
   $database = "Test";
   $conn = new PDO( "sqlsrv:server=$server;Database = $database", "", "");

   $binary_source = fopen( "data://text/plain,", "r");

   $stmt = $conn->prepare("insert into binaries (imagedata) values (?)");
   $stmt->bindParam(1, $binary_source, PDO::PARAM_LOB); 

   $conn->beginTransaction();
   $stmt->execute();
   $conn->commit();
?>

See Also

previous page start next page