Re: [PHP-DB] Re: Using MySQLi (OOP) to call a stored procedure with INOUT and OUT parameters.
On 13 September 2011 04:21, Ross McKay ro...@zeta.org.au 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: [PHP-DB] 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
