PHP PDO无法获得OUT参数值 [英] PHP PDO cannot get OUT parameter value

问题描述

我刚开始在MySQL存储过程中使用PHP PDO，但是我对如何从过程调用中获取OUT参数有疑问.我看过许多类似的stackoverflow主题，但不幸的是，我找不到解决问题的方法:|

I just started using PHP PDO with MySQL stored procedures and I have problem with how to get OUT parameters from the procedure call. I looked at many similar stackoverflow topics, but unfortunately I couldn't find a way to resolve my issue :|

以下是详细信息:

该过程采用1个输入参数和2个强制输出参数，并在其中返回结果状态.

The procedure takes 1 input parameter and has 2 mandatory output parameters, and returns result status in them.

这就是我所说的:

$input = 5;
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();

该过程在@o_code参数中返回INT，在@o_message参数中返回STRING. 如果是从CLI调用的，那么在调用之后，我会在CLI中编写

The procedure returns INT in the @o_code parameter and STRING in the @o_message parameter. If it's called from CLI, and after the call I write in the CLI

select @o_code, @o_message;

一切正常，也就是说，我能够看到这些OUT参数中返回的值. 但是我不能从PHP代码中做到这一点-由于某种原因，我总是得到FALSE结果.该程序可以正确执行它的工作，但我只是无法获得其结果.

everything is OK, that is I am able to see the values returned in these OUT parameters. However I cannot do it from PHP code - for some reason I always get FALSE results. The procedure do it's job correctly, but I just cannot get its results.

在进行上述调用之后，我尝试了以下方法来获取值:

I tried the following methods to get the values, right after I make the call described above:

$output = $proc->fetch(PDO::FETCH_ASSOC); // also with PDO:FETCH_OBJ
$output = $mydb->query("select @o_code, @o_message");
$output = $mydb->query("select @o_code, @o_message")->fetch();
$output = $mydb->query("select @o_code, @o_message")->fetchColumn(); 
$output = $mydb->query("select @o_code, @o_message")->fetchAll();

但这些都不返回与NULL或FALSE不同的任何结果.我还尝试了bindParam，但仍然无法使其正常工作.

but none of these return any result different from NULL or FALSE. I also tried with bindParam, but still could not make it work.

感谢您在此问题上的任何帮助，祝您一切顺利！

Thank you for any help on this issue and good day!

-----编辑-----

----- EDIT -----

这是我用bindParam尝试过的代码，仍然无法使用:

Here is the code that I tried with bindParam, which still does not work:

$input = 5;
$proc = $mydb->prepare("CALL proc_name(?, ?, ?)");
$proc->bindParam(1, $input, PDO::PARAM_INT); 
$proc->bindParam(2, $code, PDO::PARAM_INT); 
$proc->bindParam(3, $message, PDO::PARAM_STR);
$proc->execute();

var_dump($code, $message); // NULL, NULL

推荐答案

问题是调用存储过程的第一个查询不被视为完成并关闭，并且PDO在上一个查询被执行之前不会执行另一个查询.完成.

The problem was that the first query that is calling the stored procedure is not considered as finished and closed, and PDO will not execute another query until the previous query is done.

解决方案是添加$proc->closeCursor();

整个工作示例是:

$input = 5;
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor();

$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>value, 'o_message'=>value)

这篇关于PHP PDO无法获得OUT参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！