On Fri, Mar 19, 2004 at 10:32:56PM -0500, Jeff Urlwin wrote:
> >
> > 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.
> > 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.
So the issue here is that when a simple while($sth->fetchrow_arrayref)
loop ends you wouldn't have updated any bind_param_inout() values
because you wouldn't know if there are any output params.
So you *have* to call SQLMoreResults to find out, and SQLMoreResults
changes the state of the handle. Okay.
We can get away with that because the DBI spec already says that
statement handle metadata may not be available after all rows have
been fetched. It's a relatively small step to extend that to say
that after fetching all rows the statement handle metadata may
change to be that of the next statement. But my preference
would be to set a flag internally so that requests for new metadata
return undef until more_results() is called. That's safer.
Any metadata attributes like NAME already fetched will be cached
and can remain cached until more_results() is called. In fact the
DBI's default more_results() method can hold the code to clear out
the attribute cache and reset the statement handle. Then drivers
just need to call $sth->SUPER::more_results() at the start of their
own more_results() code.
> > [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?
No, it's just an idea for a toy to experiment with. Not serious.
On the other hand I *am* interested in providing a way for the DBI to
support compound statements natively for all drivers, but that
wouldn't be done via a subclass. It'll have to wait until two other
features fall into place: preparse() to find where to split the statements,
and method to swap the inner handles of two outer handles. There's
no rush but I'd like to be sure the spec we end up with is implementable
buy the DBI itself for drivers that don't offer direct support.
> 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.
It is purely a performance issue. The driver ought to always present the
right metadata after an execute. I'd have thought it would be relatively
cheap to just *check* that on the second and subsequent execute()s that
the names and datatypes match the "information related to the result set"
and only change it if it's not the same. Let's defer this to a separate
thread after the dust has settled on this one.
> > 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?
Eh? I don't understand the question. That's not really related to
more_results(), only to a single result set and how to tell if you
can keep fetching more results from *that* result set. Either way
the application can call more_results() to move on to the next result set.
> Might force us to
> call more_results after the end of a result set internally
I think you have to call SQLMoreResults at the end of each result set,
as discussed above, but only because of the way stored procedure
output params are handled by Sybase/MSSQL. Other drivers can probably
do it the more natural way. Either way it doesn't make a difference
to the application.
> or during an error condition...
No, I can't see a need to call SQLMoreResults in an error condition.
> > 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)
I doubt it would be enabled by default. An application would only use the
mechanism if it made sense. Often it wouldn't, but when it did it would be
very helpful to be able to use the one-method-does-it-all select*_* methods.
> > 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!
Ah, yes, thanks for reminding me: Many thanks to Michael Peppler!
Tim.
