>
> 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;
Ok - I'm with you ;)
> 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.
Ok, currently DBD::ODBC can throw an error (depending upon how the
underlying driver returns when checked) when NUM_OF_FIELDS is fetched and
it's a non-select statement. (It's microsoft's driver that comes to mind,
actually). I can mask that with a 0 return or an undef return.
>
> 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:
Yes, but -- I think that (or me) has made problems where, maybe, DBD::Sybase
doesn't have them. I end up checking SQLMoreResults at the end of one query
and I think my issue was the fact that parameter returns for stored
procedures in SQL Server don't get returned until after the last set. The
more I think of the *mess* that is in there to support strange cases, the
more I think I need to do it better. A pseudo example, here, for those who
are unaware:
Create procedure foo (@i int, @result int OUTPUT) as
Begin
set @result = @i + 1;
update blah set val = @result where index = @i;
select val, index from blah where index = @result;
delete from blah where index = @result;
select val, index, val2, val3, charval from blah where index = @i;
End;
-- Obviously, not useful, but would return:
- empty result set with for update (possibly #rows affected,
however, if I recall correctly, DBD::ODBC skips this result)
- returns a two column result set
- empty result set, again rows affected may be available
- returns a 5 column row set
- when finished fetching, and the last result set is done, then the
output result is available for retrieval.
I *think* DBD::ODBC would skip the # rows affecte, by default, right now --
but it's been a while.
>
> 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).
Yes, I agree with this concept now.
>
> [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) ]
Is the intent of this subclass to [eventually] hide the details of multiple
statements from those drivers which do not handle more_results? For
example, with DBD::ODBC, I have to check if SQLMoreResults is supported by
the driver. Would this type of subclass 'fit in' where more_results is not
supported? Or, is this to simply flush-out the concepts first...???
>
> 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.
Er, yes, and I think DBD::ODBC keeps Active alive at the moment, but I'll
have to check. What it does now is to check for more results at the end of
a valid rowset (if SQLMoreResults is supported). The more I think about it,
it's for two purposes.
1) flush out the queue for the next statement, so that if we are
ignoring non-selects, then we don't get "false positive" more_results (could
be handled in a much different way).
2) Ensure that bound output parameters are delivered at the end of
the last statement, as you would/should expect.
Again, I think I can simplify this and make it better, but I do remember
some pathalogical situations such as (forgive my syntax):
Create procedure foo (@i integer) as
if (@i = 1)
select foo, bar, bletch from table;
else
select bletch, sleepy, long from table;
end if;
End;
Thus
$sth = $dbh->prepare("{ call foo(?); }");
while (some condition) {
$sth->execute(some value);
fetch rows
}
That, in fact, turned into a performance issue and I believe I stopped short
of supporting that. My reasoning at the time was that it would slow down
all other users. I'd basically have to nearly re-prepare the query
(certainly re-build the information related to the result set) every
execute. Right now, I end up doing it on every call to
$sth->{odbc_more_results}, which I believe is more than acceptable, but not
so much on the code above, where someone isn't expecting more_results ..
Just different results on every execute.
>
> finish: $sth->finish will discard all pending result sets
> by default, but take an option to only apply to the current set.
That might be interesting ;) I haven't tried that.
>
> 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.
Ok -- before or after a call to more_results(), ideally? Might force us to
call more_results after the end of a result set internally or during an
error condition... Could get ugly, but I haven't thought much about it.
>
> 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.
Err...right. Have to look at how each driver which supports this handles
it.
>
> 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().
Internally, DBD::ODBC uses SQLGetFunctions(SQL_API_SQLMORERESULTS) to
determine if SQLMoreResults is supported, but get_info(36) will return
either Y or N (so far in my testing). Also, DB2 returns yes, but it seems
that it may only work for stored procedures which have multiple result sets,
not multiple queries to be executed 'plainly'.
>
> selectall_* methods: could perhaps be extended to
> automatically call more_results() and loop.
Yuck. I don't like that idea, really -- but I see your point. What about
result sets of different "shapes" (number or type of columns)...I guess
select-all doesn't care, but how to delineate between the result sets if
they only differ in, say, the specific column returned and it happens to be
of the same type? (i.e. not fool-proof)
>
> p.s. Many thanks to Jeff Urlwin and Martin Evans for their
> original exploratory work with DBD::ODBC which raised and
> resolved many issues.
Err, you're welcome, but you ought to thank Michael Peppler too, as I copied
much of the code and concepts from him.
And, yes, Martin has been extremely valuable to me in getting this working
and, in general!
Regards,
Jeff
