On 20-Aug-2002 Jeff Urlwin wrote:
> 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)
Presumably, you mean here ignore a result if there are no columns. Does this
mean you cannot actually see $stmt->rows? like in this case:
my $stmt = $dbh->prepare("delete from table where col1 = 987654");
$stmt->execute();
print $stmt->rows, "\n";
$stmt = $dbh->prepare("insert into table (col1) values(987654)");
$stmt->execute();
print $stmt->rows, "\n";
which in 0.45_15 works fine, outputting 0 and 1 the first run (no col1=987654)
and 1 1 in the second case. I'm guessing this is still OK.
> - provide multiple result sets to the user via the odbc_more_results
> attributed
So this works something like:
prepare(sql)
execute
while(fetch) {
;operate on rows/columns in result-set 1
}
if (odbc_more_results set in statement)
while(fetch) {
;operate on rows/columns in result-set 2
}
Yes?
If so I think this is OK as it does not change anything for scripts already out
there but adds ability to use drivers which batch sql (like procedures in MS
SQL Server).
> - automatically, if multiple result sets are detected, reset the columns
> every execute
> (works around problem with multiple calls of multi-result set)
So, you are detecting the multiple result-sets by calling SQLMoreResults after
an Execute/Fetch has returned SQL_NO_DATA and setting odbc_more_results
statement attribute.
Yes?
> - 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)
Is this for the case in your test suite which does this sort of thing?
procedure fred(int i)
{
if (i = 1)
select col1,col2 from table1
else
select col1 from table1
}
> I think this is what DBD::ODBC does now (in my test version).
Is this something changed since 0.45_15? The procedure I mailed in this thread
which generated 2 results-sets with different numbers of columns did not work
in 0.45_15. If you have any of the above changes I am as always happy
to try it out here.
> 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!
I think the current set of changes (in your "what my test version already
does") will work for most situations. This second proposal is slightly more
flexible and closer to ODBC but also seems to have the added advantage of
perhaps being tidier internally in DBD::ODBC.
Just a few getinfos you might find useful if you get to the latter proposal
SQLGetInfo(SQL_BATCH_ROW_COUNT) - does the server batch up the
inserts/updates/deletes into one count or allow them to be obtained
individually. See bitmask SQL_BRC_PROCEDURES and SQL_BRC_ROLLED_UP.
SQLGetInfo(SQL_BATCH_SUPPORT) - support for batches.
SQLGetInfo(SQL_MULT_RESULT_SETS) - does the driver support multiple results.
Any help I can be let me know.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
