Re: Multiple result sets with a prepared statement in mysqli

On Fri, Apr 6, 2012 at 2:02 PM, tamouse mailing lists
<[email protected]> wrote:
> On Fri, Apr 6, 2012 at 12:33 PM, Ken Keefe <[email protected]> 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