112
Stephen R. van den Berg wrote:
> Maybe I'm dense here, but as far as described, apparently the stored
> procedures in MySQL return zero, one or more rows.
Sorry, I assumed anyone reading this would be familiar with what MySQL
can return from stored procedures, or had read my previous email (11th
December) which demonstrated it - both of which were unfounded.
Assuming there were no errors, MySQL will return (no results) or (2 or
more result-sets, the last of which will be empty, and will have
more_results set to FALSE)
In the event of an error, MySQL will return (no results) or (1 or more
result sets, the last of which will have more_results set to TRUE)
Each result-set is handled in the same way as a normal database result -
including fetch_row()
You must fetch all of the result-sets before you can make a new query,
and you must free each result before you fetch the next one. Failure to
comply with these requirements leads to the "Statements out of order" error.
> That happens to be exactly what all other SQL statements are
> providing, and which is what the fetch_row() result function is for.
no, all other SQL statements only provide one set of results like this:
+---------------------+
| cols = 1; rows = 2; |
+---------------+-----+
| result1 (int) |
+---------------+
| 1 |
| 2 |
+---------------+
; the return from stored procedure looks like
+------------------------------+
| cols = 1; rows = 2; more = 1 |
+---------------+--------------+
| result1 (int) |
+---------------+
| 1 |
| 2 |
+---------------+--------------+
| cols = 2; rows = 2; more = 1 |
+-----------------+------------+------+
| result2-a (int) | result2-b (string)|
+-----------------+-------------------+
| 3 | four |
| 5 | six |
+-----------------+------------+------+
| cols = 0; rows = 0; more = 0 |
+-+----------------------------+
+-+
+-+
I should have made my introduction to the example code more explicit.
Here is some example code which shows how the common cases can be
handled, and the error states that can exist. We write our stored
procedures in 3 styles, those that return no results, those that return
exactly one row of results, and the rest - multiple sets of multiple
lines of data. These routines provide a similar level of simplification
to these tasks as query() does to big_query()
> Why can the existing interface (i.e. fetch_row()) not be used
> to fetch the results from a MySQL stored procedure?
As you can see from the example, it is used. It's all the other hoops
you have to jump through that are being illustrated (or you could just
close the DB connection after each query as I've seen suggested on this
list in the past)
|