Re: Store Procedure (output --> cursor)?

[Roland Bouman]

Hi!

What do you want to do exactly?
1) Return a resultset from a mysql procedure and process the resultset in php or some other language at the receiving end
2) pass a resultset from a mysql procedure to another mysql procedure?

For 1) goto:

http://www.slideshare.net/dpc/dpc2007-mysql-stored-routines-for-php-developers-roland-bouman/

and look at that presentation. You can download it too by the way:

http://www.slideshare.net/signup?from=download&from_source=http%3A%2F%2Fwww.slideshare.net%2Fdpc%2Fdpc2007-mysql-stored-routines-for-php-developers-roland-bouman%2F

Now, what you need is to look carefully at slides 33, 34, and 35. Those (33) shows what the php mysql_% functions can't do, but what the mysqli_% functions can do.
35 shows how to process multiple resultsets in PHP.

for 2) I currently do not have a nice article, but the trick is to either store the results in a temporary table and re-read that in another procedure, or otherwise return the sql query text from your procedure and let the receiving procedure use the PREPARE syntax (dynamic sql) to create a view dynamically,
after which you can run a cursor over that view.

Ugly, but if you really need it this at least works for some cases.

regards,

Roland.

Roland.

Roland Bouman
http://rpbouman.blogspot.com/