PHP PDO cannot get OUT parameter value

Question

I just started using PHP PDO with MySQL stored procedures and I have problem with how to get OUT parameters from the procedure call. I looked at many similar stackoverflow topics, but unfortunately I couldn't find a way to resolve my issue :|

Here are the details:

The procedure takes 1 input parameter and has 2 mandatory output parameters, and returns result status in them.

This is how I call it:

$input = 5;
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();

The procedure returns INT in the @o_code parameter and STRING in the @o_message parameter. If it's called from CLI, and after the call I write in the CLI

select @o_code, @o_message;

everything is OK, that is I am able to see the values returned in these OUT parameters. However I cannot do it from PHP code - for some reason I always get FALSE results. The procedure do it's job correctly, but I just cannot get its results.

I tried the following methods to get the values, right after I make the call described above:

$output = $proc->fetch(PDO::FETCH_ASSOC); // also with PDO:FETCH_OBJ
$output = $mydb->query("select @o_code, @o_message");
$output = $mydb->query("select @o_code, @o_message")->fetch();
$output = $mydb->query("select @o_code, @o_message")->fetchColumn(); 
$output = $mydb->query("select @o_code, @o_message")->fetchAll();

but none of these return any result different from NULL or FALSE. I also tried with bindParam, but still could not make it work.

Thank you for any help on this issue and good day!

----- EDIT -----

Here is the code that I tried with bindParam, which still does not work:

$input = 5;
$proc = $mydb->prepare("CALL proc_name(?, ?, ?)");
$proc->bindParam(1, $input, PDO::PARAM_INT); 
$proc->bindParam(2, $code, PDO::PARAM_INT); 
$proc->bindParam(3, $message, PDO::PARAM_STR);
$proc->execute();

var_dump($code, $message); // NULL, NULL

The question that you're pointing to is of a different problem and it does not solve the problem of the question posted here. You can check the accepted answer below to see that the problem was the "closeCursor()" should be called for the things to work properly. Your answer in the other quesion does have that line of code, so my guess is that it will not work either. — luben, Aug 27 '15 at 07:28
The answer is about getting output param value! The closeCursor() call is not involved. I updated the answer to clarify better btw — Marco Marsala, Aug 27 '15 at 12:52

luben · Accepted Answer · 2011-06-24T09:32:04.990

The problem was that the first query that is calling the stored procedure is not considered as finished and closed, and PDO will not execute another query until the previous query is done.

The solution was to add $proc->closeCursor();

The whole working sample is:

$input = 5;
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor();

$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>value, 'o_message'=>value)

PHP PDO cannot get OUT parameter value

1 Answers1

Linked