CODEWITHSUNDEEP
phpmysqlstored-procedurespdo
aSystemOverload
aSystemOverload

Reputation: 3084

PHP PDO / Retrieving OUT Parameters from MySQL Stored Procedure

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.

Upvotes: 2

Views: 6472

Answers (3)

Carlos H
Carlos H

Reputation: 584

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

Upvotes: 4

Álvaro González
Álvaro González

Reputation: 146390

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.

Upvotes: 1

Amit
Amit

Reputation: 1385

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

SELECT @newUserOK, @stprComment

Upvotes: 0

Related Questions