Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.

[Richard Quadling <rquadling@xxxxxxxxx>]

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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php