On Thu, Mar 25, 2004 at 11:53:53AM -0000, Martin J. Evans wrote:
> >
> > 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.
Yeap.
> > 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.
> > 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?
Good point. It's redundant. I'll leave finish() as 'finish all'.
> > 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?
Ignoring is too strong a word. It would be more accurate to say
we're skipping the results. But that still leaves open the issue of
whether we should also skip errors. I suspect not, at least not be default.
(Is it possible to rewrite the procedure so such errors are caught
and handled internally and not visible to the caller?)
> 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.
The docs I have say:
If one of the statements in a batch fails, SQLMoreResults will
return either SQL_ERROR or SQL_SUCCESS_WITH_INFO. If the batch was
aborted when the statement failed or the failed statement was the
last statement in the batch, SQLMoreResults will return SQL_ERROR.
If the batch was not aborted when the statement failed and the
failed statement was not the last statement in the batch, SQLMoreResults
will return SQL_SUCCESS_WITH_INFO. SQL_SUCCESS_WITH_INFO indicates
that at least one result set or count was generated and that the
batch was not aborted.
(I think the last sentance should be: s/least one result/least one more result/.)
> 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.
Something like:
more_results() = 1 Next statement executed okay
more_results() = 0 No more statements in batch
more_results() = undef Error executing next statement (and RaiseError not set)
I think it would also be handy to be able to ask more_results() to
ignore errors 'executing the next statement'. Probably via a parameter.
E.g. for ODBC SQL_SUCCESS_WITH_INFO would be treated as SQL_SUCCESS.
The application can then use $sth->err at the top of the loop to
handle the error. With that enabled more_results() would return
true the same number of times as there are statements in the batch,
(assuming the batch doesn't get aborted or network error etc.)
> > 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
> o procedures
> o arrays of parameters
> 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
> o SQL_BS_ROW_COUNT_EXPLICIT
> o SQL_BS_SELECT_PROC
> o SQL_BS_ROW_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.
Thanks for all that.
> I wasn't necessarily suggesting DBI or DBD:xxx needed to know the above to
> operate correctly but a users perl might.
The DBI & more_results() spec needs to take all the above into account
because that's what's "out there" and applications have to live
with it. There's little the DBI or drivers can do to hide these
database specific behaviours.
> 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.
People shouldn't do that but use $sth->{NUM_OF_FIELDS} instead.
> > 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
Sure. But I don't see that as a problem so long as the looping isn't
enabled by default.
> > 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.
No problem. Your detailed replies are greatly appreciated.
I'll try to consolidate this thread into an updated RFC within a few days.
(More comments still welcome of course.)
Tim.
