Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Clarification required: multiple data sets from MySQL stored procedure

by smiffy (Pilgrim)
on Jan 03, 2009 at 00:45 UTC ( #733850=perlquestion: print w/ replies, xml ) Need Help??
smiffy has asked for the wisdom of the Perl Monks concerning the following question:

I have been reading through Multiple data sets in MySQL stored procedures and am seeking clarification of exactly what is going on.

The example uses a loop whilst $sth->more_results. This suggests a situation where the number of result sets is unknown. The procedure that I have in mind would return a fixed number of result sets so I was wondering if I could simplify to this (assumes three result sets returned):

my $sth=prepare->('call my_sp($some_number);'); $sth->execute(); my $foo=$sth->fetchall_arrayref(); my $bar=$sth->fetchall_arrayref(); my $baz=$sth->fetchall_arrayref();

...or whether I have got the wrong end of the stick entirely.

Update

Having played around a bit, I have found that that $sth->more_results() must be called before the next result set appears so the code above does not work. This works:

my $sth=prepare->('call my_sp($some_number);'); $sth->execute(); my $foo=$sth->fetchall_arrayref(); $sth->more_results(); my $bar=$sth->fetchall_arrayref(); $sth->more_results(); my $baz=$sth->fetchall_arrayref();

I don't have time to write up a full example at present but can summarise that the only difference between retrieving multiple results sets is that you have to call $sth->more_results() between them.

There is now a node as a response to the original providing a cross-reference to this one.

Comment on Clarification required: multiple data sets from MySQL stored procedure
Select or Download Code
Re: Clarification required: multiple data sets from MySQL stored procedure
by Narveson (Chaplain) on Jan 03, 2009 at 04:31 UTC

    This is a great question. If you have access to a MySQL database, please go ahead and test this and share your results with us, perhaps in the form of a reply to Multiple data sets in MySQL stored procedures.

    The sample stored procedure in that node would work fine with your fetchall_arrayref investigation.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://733850]
Approved by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (9)
As of 2014-12-27 20:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (177 votes), past polls