我需要从MySQL存储过程中检索OUT参数。我找不到任何解释这个(对我来说有意义)的东西。
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";
我在另一个SO项上找到了最后两行,但它只返回NULL值。
试试这个…
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";
除了使用MySQL会话变量,您还可以使用bindParam():
bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )
将PHP变量绑定到相应的命名号或问号的SQL语句中的占位符声明。与PDOStatement::bindValue()不同,变量被绑定为推荐信,只会在那时进行评估PDOStatement::execute()被调用
大多数参数都是输入参数,也就是说,参数是以只读方式使用,以构建查询。有些司机支持调用返回数据作为输出的存储过程参数,有些也作为输入/输出参数发送进来数据和更新接收它。
不要忘记使用相应的占位符:
$stmt = $dbh->prepare("CALL superior_main_db.stprNewUser(:usrEmail, :newUserOK, :stprComment)");
一旦你执行了语句,你的变量将自动包含你需要的值。
要检索@newUserOK和@stprComment变量,只需在调用下面的存储过程后执行下面的查询
SELECT @newUserOK, @stprComment