On Tue, May 04, 2004 at 01:41:56PM -0400, Jeff Urlwin wrote:
>
> > =head2 Skipping Non-SELECT Results
> >
> > When executing stored procedures, for example, applications
> > are sometimes only interested in the results of SELECT
> > statements executed by the procedure and not in any INSERTs,
> > DELETEs, or UPDATEs that it may also have executed. So it
> > would be helpful to make it easy to skip those non-SELECT
> > results (as DBD::ODBC currently does).
>
> Which, in hindsight, I think I should never have done ;)
>
> However, it does have the affect of making complex stored procedure calls
> more "transparent" as to what they do, if they simply return a result at the
> end. (i.e. call some_vendor_supplied_complex_proc() and only get the output
> results, rather than the intermediate results -- which may vary per call).
>
> It also made the end-user's life easier, retrieving the values from stored
> procedures, since they are not returned until after the last statement has
> returned it's result set.
Yes. I appreciate the value of it and I tried hard to find a way to
make it work efficiently. (See below.)
> > A complete implementation of skipping non-SELECT results
> > would require that execute() should call more_results()
> > whenever NUM_OF_FIELDS was 0. (It can't be limited to apply
> > only to "batches" because many drivers can't tell if there
> > are multiple results until they try to fetch them.)
> >
> > So, at least for now, this feature won't be part of the DBI
> > but, as always, drivers are free to implement their own
> > mechanisms under the control of driver-specific attributes.
>
> I may decide to "break" current behavior in, hiding behind the DBI
> specification...
> Note that adding the "auto-skip" support, made DBD::ODBC more difficult, I
> think -- that and, I believe I get varying results based upon NUM_OF_FIELDS
> == 0. I have to recheck that code a bit.
I'm not sure what you're saying there.
> [snip]
>
> > I have left a $sth->{SkipNonSelect} attribute in this example
> > but, as discussed above, I'm not proposing to include it in the spec.
>
> Hmm...I hope you leave it in so that I can use it to attempt to keep
> compatible with what I have mangled already. Can you make it an optional
> attribute for drivers to implement? (I.e. SkipNonSelect defaults to undef
> or 0. If you set SkipNonSelect to 1, and the driver supports it, it will
> stay as 1, however, if the driver doesn't support it, it will remain undef
> or 0.)? Just a thought to give the end-user feedback if it's supported or
> not.
Making it an optional feature would work, especially as batch support
varies so much between databases anyway.
I was thinking in terms of having the Driver.xst code check for the
SkipNonSelect flag and NUM_OF_FIELDS==0 and then call more_results()
for you. So the driver internals whouldn't need to handle it directly.
But I figured many apps would want to leave SkipNonSelect enabled but
that would make execute() calls more expensive.
If, instead, I just document the SkipNonSelect flag but leave drivers
to handle it internally, if they want to, then drivers can do so in
a more efficient way.
Umm, but now it dawns on me that I can have Driver.xst call a
dbd_more_results() C function and skip the method call overhead.
> [more snipping]
> >
> > =head2 Active
> >
> > The interaction between the Active attribute, the finish
> > method, and row fetch errors needs to be reconsidered in
> > relation to batches.
> >
> > The finish() method will discard I<all> pending result sets.
> > The more_results() method automatically discards any
> > unfetched rows from the current sub-statement.
> >
> > The statement handle Active attribute is used to indicate
> > that there is active state on the database server associated
> > with this handle. In other words that there is more data to
> > be fetched, and that
> > finish() should be called before the statement handle is destroyed.
> >
> > The principles described at the start mean that
> > $sth->{Active} I<should> go false at the end of I<each> set
> > of SELECT results. However, that goes against the definition
> > of what Active means because in a batch there I<is> still
> > active state on the server.
> >
> > So Active will remain true while there is active state on the
> > server. This looser definition gives more flexibility to the
> > driver and should discourage applications from making
> > inappropriate assumptions.
> >
> > The original principle still applies: Active should
> > automatically go false after results from a statement handle
> > are fully consumed, and $sth->finish can be used to force it
> > to false if required.
> >
> > This means:
> >
> > Active should be false after execute() of a single non-SELECT
> > statement, or a stored procedure that returns a single non-SELECT
> > statement row count. Applications shouldn't need to call finish()
> > in these cases.
> >
> > Active can be true for a non-SELECT sub-statement (within a batch),
> > which isn't the case currently. But no code should be
> > relying on that.
>
> Hmmm...I'm glad you say "Can be true" in this paragraph above, but I'm
> concerned it would be mis-read as "should be" or "shall be". I believe we
> have to treat Active differently in batches. It should have a definition
> for the current 'sub-statement'. That's all it can be without getting into
> trouble -- unless I'm missing something.
I'm not quite sure what you're saying so I'll reword that last paragraph
and see what you make of it :)
Active I<will> be true for a non-SELECT sub-statement I<if> it is
not the last sub-statement in the batch.
Tim.
