Calling stored procedures in PHP (ibm_db2)

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).

To call a stored procedure:

1. Call the db2_prepare function, passing the listed 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.

2. For each parameter marker in the CALL statement, call the db2_bind_param function, passing the listed 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.

3. 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.

$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";