I have only just seen this and am thinking about it. In the mean time:
> 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:
The code in DBD::ODBC 1.09 appears to keep calling SQLMoreResults whilst
SQLNumResultCols = 0. I don't see anything to turn off this behavior - have I
missed something - it would be useful to be able to get at the row count on an
insert, update, delete in a batch SQL statement (procedure).
I think I've spotted a bug in the above automatic skipping. If the last SQL in
the batch is an update/delete/insert the code appears to miss SQLMoreResults
returning SQL_NO_DATA and goes on to call SQLFetch. i.e.
my $sql = q /create procedure more_resultsp @i integer, @result integer output
as
set @result = @i + 1
select @i
select * from moretest1
update moretest1 set b = 'zzz' where a = @i
select * from moretest1
insert into moretest1 values (2, 'aaa')
select * from moretest1/;
do {
while(@row = $sth->fetchrow_array()) {
print "\t", join(", ", @row), "\n";
}
} while ($sth->{odbc_more_results});
happily works but adding "delete from moretest1 where a = 2" as the last line
of the procedure gives:
DBD::ODBC::st fetchrow_array failed: [unixODBC][Driver Manager]Function
sequence error (SQL-HY010)(DBD: st_fetch/SQLFetch err=-1) at ./example.pl line
99.
and an ODBC driver manager log shows:
[ODBC][4551][SQLMoreResults.c][155]
Entry:
Statement = 0x82b5700
[ODBC][4551][SQLMoreResults.c][311]
Exit:[SQL_NO_DATA]
[ODBC][4551][SQLNumResultCols.c][149]
Entry:
Statement = 0x82b5700
Column Count = 0xbfffef2a
[ODBC][4551][SQLNumResultCols.c][234]
Exit:[SQL_SUCCESS]
Count = 0xbfffef2a -> 1
<snipped some SQLDescribeCol, SQLBindCol stuff>
[ODBC][4551][SQLFetch.c][158]
Entry:
Statement = 0x82b5700
[ODBC][4551][SQLFetch.c][175]Error: HY010
[ODBC][4551][SQLError.c][342]
Entry:
Statement = 0x82b5700
SQLState = 0xbffff364
Native = 0xbffff150
Message Text = 0xbffff164
Buffer Length = 511
Text Len Ptr = 0xbffff156
[ODBC][4551][SQLError.c][379]
Exit:[SQL_SUCCESS]
SQLState = HY010
Native = 0xbffff150 -> 0
Message Text = [[unixODBC][Driver Manager]Function seque
nce error]
The DBI/DBD::ODBC trace for the part that went wrong was:
Getting more results:
MORE Results!
dbd_describe 137068768 getting num fields
Numfields == 0, SQLMoreResults == 100
dbd_describe sql 137068768: num_fields=0
dbd_describe skipped (no result cols) (sql f137068768)
<- fetchrow_array= ( ) [0 items] row2 at ./example.pl line 100
>> FETCH DISPATCH (DBI::st=HASH(0x81d3294) rc2/1 @2 g0 ima404
pid#5098) at ./example.pl line 94
-> FETCH for DBD::ODBC::st (DBI::st=HASH(0x81d3294)~INNER
'odbc_more_results')
dbd_describe 137068768 getting num fields
dbd_describe sql 137068768: num_fields=1
colname 1 = a len = 1 (2)
col 1: INTEGER (4) len= 4 disp= 12, prec= 10 scale=0
colname buffer size = 3
pre SQLDescribeCol 0: fbh[0]=82ba560 fbh=82ba560, cbuf_ptr=82b6b20
post SQLDescribeCol col 0: 'a' (82b6b20)
colname 1 = a, len = 1 (sp = 0)
pre SQLBindCol col 0: 'a', 82b6b20, 82b6c28, 82b6b20
post SQLBindCol col 0: 'a', 82b6b20, 82b6c28, 82b6b20
col 1: 'a' sqltype=INTEGER, ctype=SQL_C_CHAR, maxlen=12, (dp = 0, cp = 0)
DEBUG col 0: 'a'
<- FETCH= 1 at ./example.pl line 94
>> rows DISPATCH (DBI::st=HASH(0x81d31f8) rc1/1 @1 g1 ima4 pid#5098)
at ./example.pl line 95
-> rows for DBD::ODBC::st (DBI::st=HASH(0x81d31f8)~0x81d3294)
<- rows= ( 5 ) [1 items] at ./example.pl line 95
>> FETCH DISPATCH (DBI::st=HASH(0x81d3294) rc3/1 @2 g0 ima404
pid#5098) at ./example.pl line 97
-> FETCH for DBD::ODBC::st (DBI::st=HASH(0x81d3294)~INNER 'NUM_OF_FIELDS')
dbd_st_FETCH_attrib NUM_OF_FIELDS 1
<- FETCH= 1 at ./example.pl line 97
>> fetchrow_array DISPATCH (DBI::st=HASH(0x81d31f8) rc1/1 @1 g1 ima0
pid#5098) at ./example.pl line 99
-> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x81d31f8)~0x81d3294)
SQLFetch rc -1
I'm also getting an issue with $sth->rows returning increasing values after
each pdbc_more_results but I'll come back to that.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 20-Mar-2004 Tim Bunce 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.
>
>
> 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:
>
> 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).
>
> [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) ]
>
>
> 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.
>
> finish: $sth->finish will discard all pending result sets
> by default, but take an option to only apply to the current set.
>
> 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.
>
> 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.
>
> 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().
>
> selectall_* methods: could perhaps be extended to automatically
> call more_results() and loop.
>
>
> Comments?
>
> Tim.
>
> p.s. Many thanks to Jeff Urlwin and Martin Evans for their original
> exploratory work with DBD::ODBC which raised and resolved many issues.
