Stored Procedure output via PDO

[joemangrove]

Hi,

 

I am trying to get the output of a stored procedure via PDO.  I am currently using the latest versions of PHP and Mysql. 

 

Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `database`.`example_sproc` $$
CREATE DEFINER=`user`@`ip` PROCEDURE `example_sproc`(IN size_variable VARCHAR(20), OUT out_variable VARCHAR(20))
BEGIN
SELECT size FROM room WHERE size=size_variable;
END $$

DELIMITER ;

 

PHP:

<?php
$size_variable = "very big";
$username="test";
$password="1234567";

$dsn = 'mysql:dbname=database;host=hostname;port=3306';
try {
  $dbh = new PDO($dsn, $user, $password);
}
catch (PDOException $e) {
  die('Connection failed: '.$e->getMessage(  ));
}
$sql="CALL example_sproc(?, @output_variable)";
$sth = $dbh->prepare($sql)  or die(implode(':', $sth->errorInfo(  )));
$sth = bindparam(1, $size_variable, PDO::PARAM_STR, 4000);
$sth->execute() or die (implode(':', $sth->errorInfo()));
$sql="SELECT @out_variable";
foreach ($dbh->query($sql) as $row) {
  printf("%s\n", $row[0]);
}
?>

 

The above code does not print out the desired output and does not print a php or mysql error.  However, when using mysqli the stored procedure outputs the variable just fine, so the stored procedure shoud be ok. 

 

This is driving me crazy; any help would be greatly appreciated.

 

Sincerely,

Ryan