使用PDO调用带有Out参数的存储过程 [英] Calling stored procedure with Out parameter using PDO

问题描述

我已经使用PDO一段时间了，并且正在重构一个项目，以便它使用存储的proc而不是内联SQL.我收到了我无法解释的错误.我使用的是PHP 5.3.5版和MySQL 5.0.7版.

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 //  

这是我用来调用proc的代码，$ db是PDO的一个实例:

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

如果我直接这样调用proc:

If I call the proc directly like so:

CALL proc_OUT(@res);
SELECT @res;

它按预期工作，这使我相信PHP调用它存在问题，但是我似乎找不到问题所在.我正在按照手册中的说明进行操作，但仍然出现此错误.有人可以建议我做错了什么吗?任何建议将不胜感激.非常感谢！

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

来自上面链接的评论:

$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));

另请参见: https://stackoverflow.com/a/4502524/815386

这篇关于使用PDO调用带有Out参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！