Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
| From: | Richard Quadling | Date: | Mon, 12 Sep 2011 20:18:34 +0000 |
| Subject: | Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters. | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-48005@lists.php.net to get a copy of this message | ||
Hi.
I'm just trying to get PHP to talk to a stored procedure which has IN,
INOUT and OUT parameters.
The procedure is really dumb as I'm just trying to get things to work.
The procedure is (taken from NaviCat's DDL view of the procedure) ...
CREATE DEFINER=
root@localhost PROCEDURE testInOuts(IN
anInputInt int,INOUT anInputOutputInt int,OUT
anOutputDateTime
datetime)
BEGIN
SET anInputOutputInt = anInputInt + anInputOutputInt;
SET anOutputDateTime = NOW();
END
I've proven that the procedure work by ...
CREATE DEFINER=root@localhost PROCEDURE tester()
BEGIN
DECLARE myNewInt INT;
DECLARE myNewDT DateTime;
SET myNewInt = 5;
CALL testInOuts(10, myNewInt, myNewDT);
SELECT myNewInt, myNewDT;
END
And the output is 15 and today's datetime.
But I'm getting nowhere fast with PHP and mysqli.
I've got stored procedures returning result sets - all happy with
that. But not via OUT or INOUT.
<?php
$o_Conn = new mysqli('localhost', 'root', 'LocalRoot');
if ($o_Conn->connect_errno) {
die('Error #' . $o_Conn->connect_errno . ' : ' . $o_Conn->connect_error);
}
$o_Stmt = $o_Conn->prepare('CALL test.testInOuts(?,?,?)') or
die('Error #' . $o_Conn->errno . ' : ' . $o_Conn->error);
$ten = 10;
$five = 5;
$when = Null;
$o_Stmt->bind_param('iid', $ten, $five, $when) or die('Error #' .
$o_Conn->errno . ' : ' . $o_Conn->error);
$o_Stmt->execute() or die('Error #' . $o_Conn->errno . ' : ' .
$o_Conn->error);
echo
'$ten = ', $ten, PHP_EOL,
'$five = ', $five, PHP_EOL,
'$when = ', $when, PHP_EOL;
?>
outputs ...
$ten = 10
$five = 5
$when =
No errors and no changes to the params.
They are supplied by reference to the bind_params() method, so I would
assume that they would be returned by calling execute().
But they aren't so I'm missing something obvious.
Any help please?
Regards,
Richard.
P.S. I'm coming from MSSQL using the MS SQL Server Driver for PHP via
PDO, so a very different experience.
--
Richard Quadling
Twitter : EE : Zend : PHPDoc
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea