Tim and others,
Sorry it has taken so long to reply properly to this. I wanted to go through
the ODBC case and see if anything did not fit, hit a few problems in DBD::ODBC
(see other email) and then didn't have time to come back to it until now.
In principle the more_results method you describe sounds OK but I have a few
comments and some new info.
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;
Yes and in the case of procedures, the last more_results will be the point
where any output parameters are available.
> 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.
Yes but see below re $sth->rows if a driver batches.
>
> 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.
hmm. My reading of the state transitions in ODBC would suggest
SQLFreeSTMT(SQL_CLOSE) will get rid of all the batches and the only way to get
to the next in a batch would be by calling SQLMoreResults. Wouldn't finish
having an option to discard only the current sql be the same as calling
more_result?
> 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?
Yes - see below.
> In other words can later sub-statements still
> execute after an error in a preceeding sub-statement?
yes - see below.
> Or, to put it another way: should more_results() always return
> true the same number of times as there are sub-statements
so long as you can detect one in the batch that fails some other way - yes.
> (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.
create table moretest1 (a int, b char(10))
insert into moretest1 values (1, 'aaa')
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/;
The above works fine in DBD::ODBC currently because all the sql is correct.
If you change the last insert to "insert into moretest1 values ('a', 'aaa')"
RaiseError comes in and PrintError shows:
DBD::ODBC::st fetchrow_array failed: [unixODBC][Microsoft][ODBC SQL Server
Driver][SQL Server]Syntax error converting the varchar value 'a' to a column of
data type int. (SQL-22018)(DBD: st_fetch/SQLMoreResults err=-1) at ./example.pl
line 100.
Turning RaiseError off here breaks the current DBD::ODBC which ends up in a
endless loop.
o in ODBC it is SQLMoreResults which returns an error. It was picked up in
fetchrow_array above because DBD::ODBC is attempting to ignore non-select
statements. So, some of your problems (open issues above) might get worse if
you are ignoring non-selects but they error - what do you do?
o according to the state transitions in ODBC, SQLMoreResults cannot return an
error (although the ODBC docs says it can). Obviously someone forgot to put
the error return in the state transitions so we cannot be sure what was
intended.
o in practise you can't close the statement until SQLMoreResults returns
SQL_NO_DATA. You can drop the statement at any time.
So I think more_results needs to return an error state (for the sql that just
errored) and two states for there is no more sql in the batch and the next sql
was run OK - or this is achievable some other way.
> 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().
There are 3 kinds of batches of sql statements according
to ODBC:
o explicit batches
insert into table values (1);
insert into table values (1);
insert into table values (1)
No semicolon is required at the end of the last one.
o procedures
if a procedure contains more than one sql statement then it is considered to
be a batch of sql.
e.g. create procedure fred (@p int) as
select * from table1 where column = @p
select * from table2 where column2 = @p
o arrays of parameters
arrays of parameters can be used with a parameterized sql statement as an
effective way of performing bulk operations.
e.g. insert into table values (?)
SQL_ATTR_PARAMSET_SIZE set to > 1
You can't mix the types in an interoperable way i.e. you could not mix an
explicit batch of sql statements where one or more were procedure calls in a
driver-independent way.
In ODBC:
use DBI::Const::GetInfoType;
my $multresults_support = $dbh->get_info($GetInfoType{SQL_MULT_RESULT_SETS});
returns 'Y' is the driver supports multiple result sets and 'N' otherwise. The
likes of MS SQL Server, Sybase and DB2 return 'Y'.
my $batch_support = $dbh->get_info($GetInfoType{SQL_BATCH_SUPPORT});
This returns a bitmask indicating the drivers support for batches:
o SQL_BS_SELECT_EXPLICIT
the driver supports explicit batches that can havce result-set generating
statements.
o SQL_BS_ROW_COUNT_EXPLICIT
the driver supports explicit batches that can have row-count generating
statements.
o SQL_BS_SELECT_PROC
As above for procedures
o SQL_BS_ROW_COUNT_PROC
As above for procedures
The likes of MS SQL Server sets all bits, DB2 sets SELECT_EXPLICT,
ROW_COUNT_EXPLICIT and SELECT_PROC but not COUNT_PROC.
my $batch_row_count = $dbh->get_info($GetInfoType{SQL_BATCH_ROW_COUNT});
There is one bit for row counts are rolled up or not (SQL_BRC_ROLLED_UP)and 1
bit each for procedures (SQL_BRC_PROCEDURES) and explicit batches
(SQL_BRC_EXPLICIT). What if explicit batches are rolled up but not rolled up in
procedures - how can that be specified?
DB2 seems to return rolled up bit set but neither of the others and SQL Server
returns only the explicit bit set.
I wasn't necessarily suggesting DBI or DBD:xxx needed to know the above to
operate correctly but a users perl might. The one problem that could occur is
if the driver batches rows counts, we don't spot that and attempt to use
$sth->rows as an indication of whether it was a select or not.
> selectall_* methods: could perhaps be extended to automatically
> call more_results() and loop.
except that may end up with different sized (number of columns) result-sets
needing to be combined in some way:
We'd need to handle
select a,b,c from table
select a,b,c,d,e from table
>
> 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.
Again, sorry for taking so long to reply.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
