使用PDO使用Out参数调用存储过程

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.

我一直在使用PDO一段时间，并且正在重构一个项目，以便它使用存储过程而不是内联SQL。我收到一个我无法解释的错误。我正在使用PHP版本5.3.5和MySQL版本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:

这是我用来调用proc的代码，$ db是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:

如果我像这样直接调用proc：

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!

它按预期工作，这让我相信它是如何用PHP调用的，但我似乎无法找到问题所在。我按照手册中的说明操作，但仍然收到此错误。有谁能建议我做错了什么？任何建议将非常感谢。非常感谢！

3 个解决方案

#1

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://*.com/a/4502524/815386

另见：https：//*.com/a/4502524/815386

#2

You need to specify that your parameter is IN/OUT style like PHP web site example :

您需要指定您的参数是IN / OUT样式，如PHP网站示例：

http://php.net/manual/en/pdo.prepared-statements.php example #5

http://php.net/manual/en/pdo.prepared-statements.php示例＃5

<?php

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";

#3

Got it! Just add a

得到它了！只需添加一个

SELECT @outputparam;

at the end of the stored procedure, where @outputparam is the name used for the param in the stored procedure definition. If you cannot edit the stored procedure, you should do a second query, for SELECT @outputparam, with PHP PDO to get the output param value.

在存储过程的末尾，其中@outputparam是存储过程定义中用于param的名称。如果无法编辑存储过程，则应对SELECT @outputparam执行第二次查询，使用PHP PDO获取输出参数值。

Tip: If you're using the deprecated DBLib to connect to SQL Server and you modified the stored procedure as suggested, you'll also need to tweak your syntax to get the output param value in the calling PHP script:

提示：如果您使用已弃用的DBLib连接到SQL Server并且您按照建议修改了存储过程，则还需要调整语法以在调用PHP脚本中获取输出参数值：

$out = 0;
$sth = $db->prepare("DECLARE @myout INT; EXECUTE mysp :firstparam, :secondparam, @myout OUTPUT;"); // the DECLARE trick is needed with DBLib
$sth->bindParam(':firstparam', $firstparam, PDO::PARAM_INT);
$sth->execute();
$sth->bindColumn(1, $out, PDO::PARAM_INT);
$sth->fetch(PDO::FETCH_BOUND);

var_dump($out); // works

#1