How to Retrieve Output from Stored Procedures with OUT Parameters Using PDO?

Calling Stored Procedures with OUT Parameters Using PDO

In database programming, stored procedures are essential for encapsulating complex database operations. Retrieving output from stored procedures using PDO can be challenging.

Problem:
When calling a stored procedure with an OUT parameter using PDO, you might encounter an error like:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine mydb.proc_OUT is not a variable or NEW pseudo-variable in BEFORE trigger

Solution:
PDO expects OUT parameters to be retrieved using a separate query. The following steps outline the correct approach:

1. Prepare the PDO statement:

   $stmt = $db->prepare("CALL proc_OUT(?)");

2. Bind the OUT parameter:

   $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

3. Execute the stored procedure:

   $stmt->execute();

4. Query the OUT parameter:

   $stmt2 = $db->query("SELECT @return_value");
   $row = $stmt2->fetch(PDO::FETCH_NUM);
   $return_value = $row[0];

Example:
Here is an example of calling a stored procedure with an OUT parameter:

$db = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");

// Stored procedure definition
$sql = "CREATE PROCEDURE proc_OUT (OUT var1 VARCHAR(100))
BEGIN
    SET var1 = 'This is a test';
END";
$db->query($sql);

// Call the stored procedure
$stmt = $db->prepare("CALL proc_OUT(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
$stmt->execute();

// Query the OUT parameter
$stmt2 = $db->query("SELECT @return_value");
$row = $stmt2->fetch(PDO::FETCH_NUM);
$return_value = $row[0];

echo $return_value; // Output: 'This is a test'

By following this approach, you can successfully call stored procedures with OUT parameters using PDO.

The above is the detailed content of How to Retrieve Output from Stored Procedures with OUT Parameters Using PDO?. For more information, please follow other related articles on the PHP Chinese website!