在Zend Framework 2中访问MySQL存储过程输出 [英] Accessing MySQL stored procedure output in Zend Framework 2

问题描述

我有一个简单的MySQL存储过程，该过程使用两个参数并将一行插入表中.我可以像这样从Zend Framework 2中很好地执行它:

I have a simple MySQL stored procedure that takes two parameters and inserts a row into a table. I can execute it just fine from Zend Framework 2 like this:

$result = $this->dbAdapter->query('CALL sp_register_user(?, ?)', array('username', 'password'));

我还可以访问从存储过程返回的任何结果集.

I can also access any result sets returned from my stored procedure.

我现在想要的是将存储过程中的输出值作为第三个参数，所以是这样的:

What I want now is to have an output value from my stored procedure as a third parameter, so something like this:

DELIMITER //
CREATE PROCEDURE sp_register_user(IN username VARCHAR(50), IN password VARCHAR(128), OUT code INTEGER)
NOT DETERMINISTIC
COMMENT 'Registers a user'
  BEGIN
    INSERT INTO user VALUES (username, password);
    SET code = 123;
  END //

问题是我如何从PHP(ZF2)访问此输出变量.我只能找到有关如何通过我正在使用的PDO直接进行操作的示例. 此页面上的示例4 显示了如何直接通过PDO进行操作.我担心的是，如果直接使用PDO对象，将会丢失一些抽象，因此我假设我将一直使用PDO.

The question is how I can access this output variable from PHP (ZF2). I have only been able to find examples of how to do it directly through PDO, which I am using. Example 4 on this page shows how to do it through PDO directly. My concern is that if I use the PDO object directly, I am losing some abstractions and I am thereby assuming that I will always be using PDO.

仍然，我试图使其直接与PDO一起使用，就像这样:

Still, I tried to make it work with PDO directly, like this:

$username = 'my_username';
$password = 'my_password';
$code = 0;

$stmt = $this->dbAdapter->createStatement();
$stmt->prepare('CALL sp_register_user(?, ?, ?)');
$stmt->getResource()->bindParam(1, $username);
$stmt->getResource()->bindParam(2, $password);
$stmt->getResource()->bindParam(3, $code, \PDO::PARAM_INT, 3);
$stmt->execute();

但是，我收到一条错误消息，说该语句无法执行.

However, I get an error saying that the statement could not be executed.

理想的解决方案是可以使用ZF2的抽象层的解决方案，但是任何有关如何访问输出参数的想法都值得欢迎和赞赏.

The ideal solution would be one where I could make use of ZF2's abstraction layer, but any ideas on how to access the output parameter are welcome and appreciated.

推荐答案

这必须可行，因为我正在使用它:

    $str = "DECLARE @Msgvar   varchar(100);DECLARE @last_id int;

    exec CallEntry_Ins $CallLoginId,".$this->usrId .",@Msg = @Msgvar OUTPUT,@LAST_ID = @last_id OUTPUT;

    SELECT  @Msgvar AS N'@Msg',@last_id AS '@LAST_ID'; ";


    $stmt = $db->prepare($str);
    $stmt->execute();
    $rtStatus = $stmt->fetchAll();



     $rtStatus[0]["@LAST_ID"] //accessing Op para

这篇关于在Zend Framework 2中访问MySQL存储过程输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！