[PHP-DB] Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
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
Re: [PHP-DB] Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
Hi Richard,
For your mysql, found this link, which I'm sure you've probably found,
but hth.
http://php.net/manual/en/pdo.prepared-statements.php
Googled:
stored procedure with INOUT and OUT parameters mysqli
For your bind statement, found this link, hth.
http://stackoverflow.com/questions/805828/using-mysqli-bind-param-with-date-and-time-columns
Googled:
bind_param php
Best,
Karl
On Sep 12, 2011, at 3:18 PM, Richard Quadling wrote:
$o_Stmt-bind_param('iid', $ten, $five, $when) or die('Error #' .
Karl DeSaulniers
Design Drumm
http://designdrumm.com
[PHP-DB] Re: Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
On Mon, 12 Sep 2011 21:18:34 +0100, Richard Quadling wrote: I'm just trying to get PHP to talk to a stored procedure which has IN, INOUT and OUT parameters. [...] You'll probably need to trick it into returning a row with your output params, like using this multi-statement query: CALL testInOuts(10, @myNewInt, @myNewDT);SELECT @myNewInt, @myNewDT see TFM for multi-query usage: http://au2.php.net/manual/en/mysqli.multi-query.php P.S. I'm coming from MSSQL using the MS SQL Server Driver for PHP via PDO, so a very different experience. Indeed, PDO is probably what you should be using if you want to use output parameters and MySQL in PHP. -- Ross McKay, Toronto, NSW Australia The documentation and sample application having failed me, I resort to thinking. This desperate tactic works, and I resolve that problem and go on to the next - Michael Swaine, Programming Paradigms, Dr Dobb's Journal -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] #@__ appeared in the sql statements
Studying a cms recently ,got a sql statement like this : Select * From `#@__homepageset` i knew the '#@__' means the table prefix but how it like this ? Did defined in the mysql or in the php code .i could't find its definition at present.Any phper's comments thanks in advance . All you best What we are struggling for ? The life or the life ?
