FAQ: How do I retrieve varchar(max)/nvarchar(max) OUTPUT parameters from a SQL Server stored procedure with SQL Server Driver for PHP?

  • Article

Question 

I want to retrieve a large character-based output parameter with varchar(max)/nvarchar(max) from a stored procedure with SQL Server Driver for PHP, however I could not find a way.

 

Answer 

This is a by design limitation currently. The root cause of this issue is that the underlying SQL Server Native Client 10.x (ODBC) does not support OUTPUT parameters of steam types.

There are two ways to work around this issue currently:

  • 1. Initialize the parameter string with a specified length

Before you bind the output parameters in PHP, you can initialize a long length string like this:

$op_param1 = str_repeat("\0" , 6000); 

  • 2. Change your stored procedure to a new one without using the OUTPUT parameter.

For example, change your stored procedure to one like this:
CREATE PROCEDURE dbo.getDocument(@docId int)

as

SELECT DocumentSummary FROM Production.Document

WHERE DocumentID=@docID

 

The [DocumentSummary] is a varchar(max) column. This procedure produces a resultset to the client instead of an output value.

 

To retrieve the value, you can refer to the following 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 = "{call dbo.getDocument( ? )}";

 

 

/* Set the parameter value. */

$docId = 3;

$params = array(

              array($docId,SQLSRV_PARAM_IN)

              );

 

/* Execute the query. */

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

if( $stmt === false )

{

 echo "Error in statement execution.\n";

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

}

 

/* Retrieve and display the data. The first three fields are retrieved

as strings and the fourth as a stream with character encoding. */

if(sqlsrv_fetch( $stmt ) === false )

{

 echo "Error in retrieving row.\n";

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

}

 

$docSummary = sqlsrv_get_field( $stmt, 0,

  SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR));

 

fpassthru($docSummary);

 

/* Free the statement and connection resources. */

sqlsrv_free_stmt( $stmt);

sqlsrv_close( $conn);

?>

Applies to

 SQL Server Driver for PHP 1.0
SQL Server Driver for PHP 2.0