使用 PDO 调用带有 Out 参数的存储过程 [英] Calling stored procedure with Out parameter using PDO
问题描述
我已经使用 PDO 一段时间了,我正在重构一个项目,以便它使用存储过程而不是内联 SQL.我收到一个无法解释的错误.我使用的是 PHP 5.3.5 版和 MySQL 5.0.7 版.
我只是想让一个基本的存储过程和输出工作.这是存储过程:
DELIMITER//创建过程`proc_OUT`(OUT var1 VARCHAR(100))开始SET var1 = '这是一个测试';结尾//
这是我用来调用 proc 的代码,$db 是 PDO 的一个实例:
$stmt = $db->prepare("CALL proc_OUT(?)");$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);//调用存储过程$stmt->execute();回声 $returnvalue;
简单吧?但是,它会导致以下错误:
带有消息SQLSTATE[42000]"的异常PDOException":语法错误或访问冲突:例程 mydb.proc_OUT 的 1414 OUT 或 INOUT 参数 1 不是变量或 BEFORE 触发器中的新伪变量
如果我像这样直接调用 proc:
CALL proc_OUT(@res);选择@res;
它按预期工作,这让我相信 PHP 调用它的方式存在问题,但是我似乎无法找到问题所在.我正在按照手册中的说明进行操作,但仍然出现此错误.谁能建议我可能做错了什么?任何建议将不胜感激.非常感谢!
这里似乎存在一个错误,我找到的最佳解决方案是:
http://www.php.net/manual/en/pdo.prepared-statements.php#101993
来自上面链接的评论:
<块引用>$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)");$dbh->query("SELECT @someOutParameter");//或者,如果你非常想使用 PDO.Prepare(),//在您的存储过程中插入SELECT @someOutParameter",然后使用:$stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)");$stmt ->execute(array($someInParameter1, $someInParameter2));
另见:https://stackoverflow.com/a/4502524/815386
I've been using PDO for awhile now and am refactoring a project so that it uses stored procs instead of inline SQL. I am getting an error that I can't explain.I am using PHP version 5.3.5 and MySQL version 5.0.7.
I'm just trying to get a basic stored proc with an output to work. Here is the stored proc:
DELIMITER //
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END //
Here is the code I am using to call the proc, $db is an instance of PDO:
$stmt = $db->prepare("CALL proc_OUT(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
// call the stored procedure
$stmt->execute();
echo $returnvalue;
Simple right? However, it results in the following error:
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine mydb.proc_OUT is not a variable or NEW pseudo-variable in BEFORE trigger
If I call the proc directly like so:
CALL proc_OUT(@res);
SELECT @res;
it works as expected which leads me to believe that there is a problem with how it is being called with PHP, however I can't seem to find what the issue is. I am following the instructions in the manual but am still getting this error. Could anyone suggest what I could be doing wrong? Any advice would be very much appreciated. Thanks much!
It would seem that there is a bug at work here, best solution I've found is this:
http://www.php.net/manual/en/pdo.prepared-statements.php#101993
From the comment at the link above:
$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); $dbh->query("SELECT @someOutParameter"); // OR, if you want very much to use PDO.Prepare(), // insert "SELECT @someOutParameter" in your stored procedure and then use: $stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); $stmt ->execute(array($someInParameter1, $someInParameter2));
See also this: https://stackoverflow.com/a/4502524/815386
这篇关于使用 PDO 调用带有 Out 参数的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!