Easysoft Data Access

How do I retrieve output parameter values from SQL Server stored procedures by using PHP ODBC?

Article:
01037
Last Reviewed:
12th June 2012
Revision:
1

A SQL Server stored procedure can return information by using either a RETURN statement or one or more output parameters. A RETURN statement enables a stored procedure to return an integer value, which reports the execution status of the procedure. Output parameters enable procedures to return other types of values, for example, character strings.

PHP ODBC, the PHP extension that enables PHP to access databases for which an ODBC driver is available, does not provide direct support for output parameters. This article describes a workaround in which two stored procedures are used. The first procedure, which is not called from PHP, returns information by using output parameters. The second procedure, which is called from PHP, calls the first procedure and converts the output parameter values into a result set.

Note PHP Data Objects (PDO), which also enables PHP to access databases for which an ODBC driver is available, does provide direct support for output parameters.

The example PHP scripts in this article use this workaround to return a single output parameter value and multiple output parameter values. To run the sample PHP scripts, you need:

  1. A SQL Server database in which you have permission to create stored procedures.
  2. A SQL Server ODBC driver.

    Easysoft provide a SQL Server ODBC driver for Linux and UNIX platforms:

    1. Download the SQL Server ODBC driver for the Linux or UNIX platform on which PHP is installed. (Registration required.)
    2. Install, license and test the SQL Server ODBC driver on this machine.

      To test the SQL Server ODBC driver, create an ODBC data source that connects to the SQL Server database where you have permission to create procedures.

      For more information, see the SQL Server ODBC driver documentation and Getting Started Guide.

Example: Retrieving a Single Output Parameter Value

<?php
/* =====================================================================
How to retrieve a single output parameter value from a SQL Server stored
procedure by using PHP ODBC.
======================================================= */

/* Do not display ODBC warning messages. */
error_reporting( E_ERROR | E_PARSE );


/* Replace myDSN with the name of your ODBC data source.
Replace myUser and myPassword with the SQL Server database username
and password. */
$dataSource = "myDSN";
$user = "myUser";
$password = "myPassword";
$conn = odbc_connect( $dataSource, $user, $password);
if( !$conn )
{
     if( phpversion() < "4.0" )
     {
          exit( "Connection Failed: . $php_errormsg" );
     }
     else
     {
          exit( "Connection Failed:" . odbc_errormsg() );
     }
}


/* This stored procedure accepts one input parameter, a number,
which is then squared and returned in an output parameter. */
$ret = odbc_exec( $conn, "CREATE PROCEDURE InputSquared
                          @InputVal integer,
                          @OutputVal integer OUTPUT
                          AS
                          SELECT @OutputVal=@InputVal*@InputVal" );


if( $ret )
{

     /* To retrieve the output parameter value via PHP ODBC, a second procedure is
     is required. This procedure calls InputSquared and converts the output parameter
     value returned into a result set. */
     odbc_exec( $conn, "CREATE PROCEDURE ReturnInputSquared @InputVal integer
                        AS
                        DECLARE @OutputVal integer
                        EXEC InputSquared @InputVal, @OutputVal OUTPUT
                        SELECT @OutputVal AS OutputValue" );

     /* Call the intermediary procedure from PHP. */
     $stmt = odbc_prepare( $conn, "{call ReturnInputSquared( ? )}" );
     $i = 2;
     odbc_execute( $stmt, array($i) );
     $result = odbc_result_all( $stmt );
     odbc_exec( $conn, "DROP PROCEDURE InputSquared, ReturnInputSquared" );
}


/* $dataSource needs to connect to a SQL Server database in which you have
permission to create a stored procedure. */
else
{
     print( "Unable to create stored procedure:" . odbc_errormsg() );
}


// Disconnect the database from the database handle.
odbc_close( $conn );
?>

Example: Retrieving Multiple Output Parameter Values

<?php
/* =====================================================================
How to retrieve a multiple output parameter values from a SQL Server
stored procedure by using PHP ODBC.
======================================================= */

/* Do not display ODBC warning messages. */
error_reporting( E_ERROR | E_PARSE );

/* Replace myDSN with the name of your ODBC data source.
Replace myUser and myPassword with the SQL Server database username and
password. */
$dataSource = "myDSN";
$user = "myUser";
$password = "myPassword";
$conn = odbc_connect( $dataSource, $user, $password);
if( !$conn )
{
     if( phpversion() < "4.0" )
     {
          exit( "Connection Failed: . $php_errormsg" );
     }
     else
     {
          exit( "Connection Failed:" . odbc_errormsg() );
     }
}

/* This stored procedure accepts one input parameter, a number,
which is then squared, cubed and returned in two output parameters. */
$ret = odbc_exec( $conn, "CREATE PROCEDURE InputToPowerOf
                          @InputVal integer,
                          @SquaredOutputVal integer OUTPUT,
                          @CubedOutputVal integer OUTPUT
                          AS
                          SELECT @SquaredOutputVal=@InputVal*@InputVal
                          SELECT @CubedOutputVal=@InputVal*@InputVal*@InputVal" );

if( $ret )
{

     /* To retrieve the output parameter value via PHP ODBC, a second procedure is
     is required. This procedure calls ReturnInputToPowerOf and converts the output
     parameters values returned into a result set. The result set is a single row with
     multiple fields. */
     odbc_exec( $conn, "CREATE PROCEDURE ReturnInputToPowerOf
                        @InputVal integer
                        AS
                        DECLARE @SquaredOutputVal integer
                        DECLARE @CubedOutputVal integer
                        EXEC InputToPowerOf @InputVal, @SquaredOutputVal OUTPUT, @CubedOutputVal OUTPUT
                        SELECT @SquaredOutputVal, @CubedOutputVal" );

     $stmt = odbc_prepare( $conn, "{call ReturnInputToPowerOf( ? )}" );
     $i = 2;
     odbc_execute( $stmt, array($i) );

     print "Single result set:\n";
     $result = odbc_result_all( $stmt );

     odbc_exec( $conn, "DROP PROCEDURE ReturnInputToPowerOf" );

     /* This procedure calls ReturnInputToPowerOf and converts the output
     parameters values returned into a multiple result sets. */
     odbc_exec( $conn, "CREATE PROCEDURE ReturnInputToPowerOf
                        @InputVal integer
                        AS
                        DECLARE @SquaredOutputVal integer
                        DECLARE @CubedOutputVal integer
                        EXEC InputToPowerOf @InputVal, @SquaredOutputVal OUTPUT, @CubedOutputVal OUTPUT
                        SELECT @SquaredOutputVal
                        SELECT @CubedOutputVal" );

     $stmt = odbc_prepare( $conn, "{call ReturnInputToPowerOf( ? )}" );
     odbc_execute( $stmt, array($i) );

     print "Multiple result sets:\n";

     do
     {
          $result = odbc_result_all( $stmt );
     } while ( odbc_next_result( $stmt ) );

     odbc_exec( $conn, "DROP PROCEDURE InputToPowerOf, ReturnInputToPowerOf" );
}

/* $dataSource needs to connect to a SQL Server database in which you have
permission to create a stored procedure. */
else
{
     print( "Unable to create stored procedure:" . odbc_errormsg() );
}

// Disconnect the database from the database handle.
odbc_close( $conn );
?>
Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.