PHP PDO / Retrieving OUT Parameters from MySQL Stored Procedure

Question

I need to retrieve the OUT Parameters from a MySQL Stored Procedure. I can't find anything that explains this (and makes sense to me).

try {
$dsn = 'mysql:dbname=db_name;host=localhost';
$dbh = new PDO($dsn, 'usr_name', 'password');
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

$stmt = $dbh->prepare("CALL db.stprNewUser(:usrEmail,:newUserOK,:stprComment)");

$stmt->bindParam(':usrEmail', $tmpEmail, PDO::PARAM_STR); 
$stmt->bindParam(':newUserOK', $newUserOK, PDO::PARAM_INT,1); 
$stmt->bindParam(':stprComment', $stprComment, PDO::PARAM_STR,100); 

$stmt->execute();

$outputArray = $dbh->query("select @newUserOK, @stprComment")->fetch(PDO::FETCH_ASSOC);

print "procedure returned [" . $outputArray['@newUserOK'] . $outputArray['@stprComment'] . "]\n";

I found the last two lines on another SO item, but it just returns NULL values.

Carlos H · Accepted Answer · 2013-11-26 01:14:58Z

try this... see if works...

try 
{
    $dsn = 'mysql:dbname=db_name;host=localhost';
    $dbh = new PDO($dsn, 'usr_name', 'password');
} 
catch (PDOException $e) 
{
    echo 'Connection failed: ' . $e->getMessage();
}


//$stmt = $dbh->prepare("CALL db.stprNewUser(:usrEmail,:newUserOK,:stprComment)");
//changed :newUserOK to @newUserOK
//changed :stprComment to @stprComment
$stmt = $dbh->prepare("CALL db.stprNewUser(:usrEmail,@newUserOK,@stprComment);");

//declare only input parameters.
//good pratice put string length. assuming varchar(100).
$stmt->bindParam(':usrEmail', $tmpEmail, PDO::PARAM_STR,100); 

//dont need these
// $stmt->bindParam(':newUserOK', $newUserOK, PDO::PARAM_INT,1); 
// $stmt->bindParam(':stprComment', $stprComment, PDO::PARAM_STR,100); 

$stmt->execute();

$outputArray = $dbh->query("select @newUserOK, @stprComment;")->fetchAll();

foreach($outputArray as $row)
{
   "NewUserOk:" .  $row["@newUserOk"] . ", StprComment:" . $row["@stprComment"];
}

//$outputArray = $dbh->query("select @newUserOK, @stprComment")->fetch(PDO::FETCH_ASSOC);
//print "procedure returned [" . $outputArray['@newUserOK'] . $outputArray['@stprComment'] . "]\n";

Álvaro González · Accepted Answer · 2013-04-01 09:40:32Z

1

Apart from using MySQL session variables, you can just use bindParam():

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Most parameters are input parameters, that is, parameters that are used in a read-only fashion to build up the query. Some drivers support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.

Don't forget to use the corresponding placeholders:

$stmt = $dbh->prepare("CALL superior_main_db.stprNewUser(:usrEmail, :newUserOK, :stprComment)");

Once you execute the statement, your variables will automatically contain the values you need.

edited Apr 1, 2013 at 9:40

answered Apr 1, 2013 at 9:23

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

5 Comments

vascowhite Over a year ago

The OP is asking how to use stored procedures, not how to bind parameters isn't he?

Álvaro González Over a year ago

@vascowhite - Please read the question again. He's asking how to retrieve OUT parameters.

vascowhite Over a year ago

Oh yes, you're right. He's already said he doesn't understand the manual though, so this isn't going to help without an example.

Álvaro González Over a year ago

@vascowhite - I've made a little edit, let's see if its clearer now.

aSystemOverload Over a year ago

Still not getting the results of the output parameters. :-s

Amit · Accepted Answer · 2013-04-01 09:28:10Z

0

To retrieve @newUserOK and @stprComment variables, just execute below query after calling stored procedure as below

SELECT @newUserOK, @stprComment

answered Apr 1, 2013 at 9:28

Amit

1,3858 silver badges15 bronze badges

2 Comments

aSystemOverload Over a year ago

Amit, how do I execute the above query in the context of my current code?

Amit Over a year ago

Same as you execute other queries $stmt = $dbh->prepare ("SELECT @newUserOK, @stprComment");

Collectives™ on Stack Overflow

PHP PDO / Retrieving OUT Parameters from MySQL Stored Procedure

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related