I have a simple MySQL stored procedure that takes two parameters and inserts a row into the table. I can execute it as best as possible from Zend Framework 2 as follows:
$result = $this->dbAdapter->query('CALL sp_register_user(?, ?)', array('username', 'password'));
I can also access any result sets returned from my stored procedure.
Now I want to have the output value from my stored procedure as the 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 //
The question is how can I access this output variable from PHP (ZF2). I was only able to find examples of how to do this directly through the PDO that I use. Example 4 on this page shows how to do this directly through PDO. My concern is that if I use the PDO object directly, I lose some abstractions, and so I assume that I will always use PDO.
However, I tried to get it to work with PDO directly, for example:
$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 that the statement could not be executed.
An ideal solution would be where I could use the ZF2 abstraction layer, but any ideas on how to access the output parameter are welcome and appreciated.