I've been thinking a bit more about this.
It *may* be a "reasonable" thing, for now, to:
- "silently" eat result sets without columns (i.e. count of rows
inserted/deleted/updated)
- provide multiple result sets to the user via the odbc_more_results
attributed
- automatically, if multiple result sets are detected, reset the columns
every execute
(works around problem with multiple calls of multi-result set)
- provide for a method for users to force re-setting result columns every
execute
(for those stored procs which may only return one result set, but
potentially a differently "shaped" result set (i.e. 3 columns one time, 4
another, etc)
I think this is what DBD::ODBC does now (in my test version).
I think if this is not stable enough or if I make the time to rework the
whole thing to:
- force odbc_more_results to actually call SQLMoreResults (instead of
automatically calling it and setting an attribute. This will prevent the
user from having to call fetch_xxx right now, which is the only *current*
way to trigger the call to SQLMoreResults and one of the problems I'm
encountering.
- have odbc_more_results get the column count and row count and setup the
result set
- if the column count is 0, then the user can detect this rowcount only
situation with $sth->{NUM_OF_FIELDS} and *not* call fetch_xxxx again, but
get $sth->rows, since it will not be an active statement (no columns/rows to
fetch)
- if the column count is non-zero, then they can safely call fetch_xxxx get
the rows
- then, add the attribute odbc_ignore_non_select_result_sets (or something
similarly horribly named)
- I think, then, for normal selects, I can keep the notion that the $sth
marks itself inactive when the result set has completed, but
odbc_more_results will "reactivate" it... That may work and may not, I'm
just thinking out loud here....
Thus, a multiple result set loop could look like the following (without the
attribute to "eat" the empty rowsets:
do {
if ($sth->{NUM_OF_FIELDS}) {
while (@data = $sth->fetchrow_array) {
do something;
}
} else {
# save the rows or print
}
} while ($sth->odbc_more_results};
if the attribute is set, the more simple loop below should work:
do {
while (@data = $sth->fetchrow_array) {
do something;
} while ($sth->odbc_more_results};
I'd make the attribute odbc_ignore_non_select_result_sets default to "on",
so that people using current code would not have to change unless they
wanted to...
Thoughts/comments welcome!
Regards,
Jeff
>
> On Tue, Aug 20, 2002 at 09:26:17AM +0100, [EMAIL PROTECTED] wrote:
> > PHP gets around this with odbc_close() which I believe
> $stmt->finish() could be
> > used for. The main problem would then be requiring scripts to
> call finish which
> > I guess would be undesirable.
> >
> > I'm not sure but I'd guess the number of poeple returning
> multiple result-sets
> > from procedures is few (partly based on the fact it was not
> working properly).
> >
> > It may be seen as a bit of a hack, but how about an attribute
> to say "we are
> > potentially fetching multiple result-sets" and in this case the
> statement is
> > marked inactive when [a] SQLMoreResults says there are no more
> result-sets or
> > [b] finish() is called (but this implies calling
> SQLFreeStmt(SQL_CLOSE)). If
> > this new attribute is not set (the default) it works as before.
> Anyone not
> > setting this new attribute but returning multiple result-sets
> and not consuming
> > them (no automatic call to SQLMoreResults now) will get an
> error with MS SQL
> > Server something like "busy with result-set on an another statement).
>
> Seems reasonable.
>
> Would it also be worth having an attribute to say the application
> is interested (or not) in non-select row counts? That way the
> application need not both with having to skip them because the
> driver will.
>
> Tim [who's not been following in detail]
>
