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:
Easysoft provide a SQL Server ODBC driver for Linux and UNIX platforms:
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.
<?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 ); ?>
<?php /* ===================================================================== How to retrieve 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 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 ); ?>
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle® is a registered trademark of Oracle® Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
