Here's what I'm thinking of with respect to $sth->more_results:
Principles:
1: At any point in time the state of a statement handle corresponds
with having executed one 'sub-statement' within the 'compound-statement'.
2: The $sth->more_results() method appears to have the effect of
preparing and executing the next sub-statement within the
compound-statement;
The first principle means that $sth->{NUM_OF_FIELDS} attribute can
be used to tell if the current sub-statement was a SELECT.
$sth->rows can be used to get the row count if it wasn't.
Another way of looking at it is to imagine a DBI subclass that
did something like this:
sub prepare {
my ($dbh, $Statement) = @_;
my @statements = split /;/, $Statement; # split statements
my $sth = $dbh->SUPER::prepare(shift @statements); # prepare the first
$sth->{statements} = [EMAIL PROTECTED]; # store the rest
return $sth;
}
sub more_results {
my ($sth) = @_;
my $statements = $sth->{statements} or return; # got multiple?
my $Statement = shift @$statements or return; # got more?
my $next_sth = $sth->{Database}->prepare($Statement) or return;
$next_sth->execute or return;
$sth->replace_guts($next_sth); # in effect
return 1;
}
Taking it a little further, following the lead of DBD::ODBC, it would be
convienient to be able to automatically skip pass non-SELECT statements
if an attribute is set. Something like:
sub more_results {
my ($sth) = @_;
my $statements = $sth->{statements} or return;
+ more_results:
my $Statement = shift @$statements or return;
my $next_sth = $sth->{Database}->prepare($Statement)
or return; # XXX
$next_sth->execute
or return; # XXX
+ goto more_results
+ if $sth->{NUM_OF_FIELDS} == 0
+ && $sth->{ignore_non_select_result_sets}; # eg
$sth->replace_guts($next_sth); # in effect
return 1;
}
Note that SELECT statements which return no rows are never skipped,
only non-SELECT statements. We can discuss the name and default setting
of that attribute later (not now, please).
[Someone with more time than they know what to do with is very welcome to
create such a subclass to experiment with. split /;\n/ should suffice.
And more_results() would need to return the $new_sth so the application
would do { ... } while ($sth = $sth->more_results) ]
Application Examples:
So the generic loop to fetch multiple result sets would be like this:
do {
if ($sth->{NUM_OF_FIELDS}) {
while (@data = $sth->fetchrow_array) {
...
}
} else {
printf "Non-SELECT: %d rows\n", $sth->rows;
}
} while ($sth->more_results};
and if $sth->{ignore_non_select_result_sets} (whatever it gets called)
is set then that simplifies down to:
do {
while (@data = $sth->fetchrow_array) {
...
}
} while ($sth->more_results};
or even, for the do-loop-phobic, just:
while ($data = $sth->fetchrow_arrayref
or $sth->more_results && $data = $sth->fetchrow_arrayref
) {
...
}
Open issues:
Active: the principles above mean that $sth->{Active} goes false
at the end of each set of results. That goes against the definition
of what Active means but I think that's a small issue compared with
the greater consistency elsewhere. A new attribute could be added
to fill the gap if need be.
finish: $sth->finish will discard all pending result sets
by default, but take an option to only apply to the current set.
row errors: row fetch errors (where a row has an error, like a
truncation, but more rows can still be fetched) could be distinguished
previously by the fetch*() method returning false but Active still
being true. That will still be true.
err: Obviously some errors will be detected and reported by the
original prepare() and execute() but others will not be detected
till later. As far as possible more_results() is the place to
report any errors that would have been detected and reported by a
corresponding prepare() and execute() of the individual sub-statement.
Sounds good, but...
more err: can more_results() return true after returning false
due to an error? In other words can later sub-statements still
execute after an error in a preceeding sub-statement?
Or, to put it another way: should more_results() always return
true the same number of times as there are sub-statements
(assuming no network errors etc). Then the generic loop becomes:
do {
if ($sth->err) {
...deal with error in this sub-statement...
}
...
} while ($sth->more_results};
In this case the two returns marked XXX in the second sub more_results
above should return true.
get_info: Martin mentioned SQL_BATCH_ROW_COUNT, SQL_BATCH_SUPPORT
and SQL_MULT_RESULT_SETS. I need to look into those (I don't have
my book handy) but certainly there will be some get_info() items
designated as mandatory for drivers which support more_results().
selectall_* methods: could perhaps be extended to automatically
call more_results() and loop.
Comments?
Tim.
p.s. Many thanks to Jeff Urlwin and Martin Evans for their original
exploratory work with DBD::ODBC which raised and resolved many issues.
