To call a stored procedure from a PHP application,
you prepare and execute an SQL CALL statement. The procedure that
you call can include input parameters (IN), output parameters (OUT),
and input and output parameters (INOUT).
Procedure
To call a stored procedure:
- Call the db2_prepare function, passing
the following arguments:
- connection
- A valid database connection resource returned from db2_connect or db2_pconnect.
- statement
- A string that contains the SQL CALL statement, including parameter
markers (?) for any input or output parameters
- options
- Optional: A associative array that specifies the
type of cursor to return for result sets. You can use this parameter
to request a scrollable cursor on database servers that support this
type of cursor. By default, a forward-only cursor is returned.
- For each parameter marker in the CALL statement, call the db2_bind_param function,
passing the following arguments:
- stmt
- The prepared statement returned by the call to the db2_prepare function.
- parameter-number
- An integer that represents the position of the parameter marker
in the SQL statement.
- variable-name
- The name of the PHP variable to bind to the parameter specified
by parameter-number.
- parameter-type
- A constant that specifies whether to bind the PHP variable to
the SQL parameter as an input parameter (DB2_PARAM_INPUT), an output
parameter (DB2_PARAM_OUTPUT), or a parameter that accepts input and
returns output (DB2_PARAM_INPUT_OUTPUT).
This step binds each parameter marker to the
name of a PHP variable that will hold the output.
- Call the db2_execute function, passing
the prepared statement as an argument.
For more information
about the ibm_db2 API, see http://www.php.net/docs.php.
Example
Prepare
and execute an SQL CALL statement.
$sql = 'CALL match_animal(?, ?)';
$stmt = db2_prepare($conn, $sql);
$second_name = "Rickety Ride";
$weight = 0;
db2_bind_param($stmt, 1, "second_name", DB2_PARAM_INOUT);
db2_bind_param($stmt, 2, "weight", DB2_PARAM_OUT);
print "Values of bound parameters _before_ CALL:\n";
print " 1: {$second_name} 2: {$weight}\n";
db2_execute($stmt);
print "Values of bound parameters _after_ CALL:\n";
print " 1: {$second_name} 2: {$weight}\n";
What to do next
If
the procedure call returns one or more result sets, you can begin
fetching rows from the statement resource.