On Fri, Apr 6, 2012 at 2:02 PM, tamouse mailing lists <tamouse.lists@xxxxxxxxx> wrote: > On Fri, Apr 6, 2012 at 12:33 PM, Ken Keefe <kjkeefe@xxxxxxxxxxxx> wrote: >> I am trying to use mysqli's prepared statements to execute a stored >> procedure that returns multiple result sets. I've been struggling with this >> for some time now. I have created a short example test script that >> demonstrates the problem. The script can be found here: >> >> http://pastebin.com/YZtDCNMF >> >> This code is how I expect mysqli to handle this scenario. However, I get a >> warning on line 31 that the number of bind variables doesn't match the >> number of fields in the prepared statement. This means that the second >> result was not fetched by the call on line 29. >> >> Any help on this issue would be greatly appreciated! Also, I'm using the >> packaged version of PHP that comes on Ubuntu 10.04 (5.3.2). >> >> Thanks, >> Ken > > Thinking off-the-cuff here, but I'm not sure multiple selects work > that way in a stored procedure. Don't stored procedures just return > the last set? Maybe someone with more familiarity with them can > answer. A little digging, since I was also interested, reveal the following: At the bottom of [1]: Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1. The client should also specify the CLIENT_MULTI_RESULTS option when it connects. For C programs, this can be done with the mysql_real_connect() C API function. See Section 20.8.3.52, “mysql_real_connect()”, and Section 20.8.12, “C API Support for Multiple Statement Execution”. And digging around [2]: Handling result sets Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query(). The mysqli_query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query() to fail returning the user expected result sets. Result sets returned from a stored procedure are fetched using mysqli_real_query() or mysqli_multi_query(). Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error. Example #3 on page [2] shows the use of retrieving multiple sets from a stored procedure. [1]: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html [2]: http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
