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.

$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: Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.

--Apple-Mail-3-420019291
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

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


--Apple-Mail-3-420019291--

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

Re: Re: Using MySQLi (OOP) to call a stored procedure withINOUT and OUT parameters.

On 13 September 2011 04:21, Ross McKay wrote:
> 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.

I've seen this "return a row" mechanism. But that allows me to define
IN parameters and to capture OUT params, but does nothing for INOUTs
(as far as I can tell).

I'm in the process of de-coupling the code from SQL in PHP to use
prepared statements with stored procedures.

A large number of the SPs return 2 or 3 values and so OUT params was ideal.

I want to use PDO. There is no php_myslqi extension, just php_mysql.

I don't think I can use multi_query AND prepared statements.

I don't want to be going back a decade in development and having to
manually prepare SQL statements and escape/test parameters when there
is a nearly perfect mechanism already available.

I think moving from OUT and INOUT to a normal result set would be the
fastest way to move forward.


Thanks.

Richard.


--
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: Re: Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.

G'day Richard,

>I've seen this "return a row" mechanism. But that allows me to define
>IN parameters and to capture OUT params, but does nothing for INOUTs
>(as far as I can tell).

Possibly, but could be worth a try except... you want to be able to use
prepared statements, and I don't know whether you can combine
multi-query with prepared statements (I skipped over mysqli_* and went
to PDO instead so have little actual experience with the former).

>I'm in the process of de-coupling the code from SQL in PHP to use
>prepared statements with stored procedures.
>
>A large number of the SPs return 2 or 3 values and so OUT params was ideal.
>
>I want to use PDO. There is no php_myslqi extension, just php_mysql.
>
>I don't think I can use multi_query AND prepared statements.

Are multi-statement queries the only reason you want to use mysqli_*
over PDO? If you are using fairly static multi-statement calls, perhaps
you can wrap the multiple statements in another SP and just execute
that. It means adding more SPs to the DB especially for supporting PHP,
but it might be your best compromise here.

>I don't want to be going back a decade in development and having to
>manually prepare SQL statements and escape/test parameters when there
>is a nearly perfect mechanism already available.

Indeed, yuk!

>I think moving from OUT and INOUT to a normal result set would be the
>fastest way to move forward.

That's another approach. You could even write wrapper SPs to let you do
that without modifying your existing SPs if that helps maintain
consistency between SP codebases (but see above for a different SP
wrapper approach).
--
Ross McKay, Toronto, NSW Australia
"Let the laddie play wi the knife - he'll learn"
- The Wee Book of Calvin

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