On Sat, 1 May 2004 18:53:30 +0100, Tim Bunce typed:
> =head2 Example Implementation via DBI Subclass
>
> Another way to think about the behaviour of more_results() 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
> if @statements;
> return $sth;
> }
>
> sub execute {
> my $sth = shift;
> if ($sth->{statements}) { # this is a batch
> $sth->{parameters} = [ @_ ]; # store the parameters
> return $sth->_more_results_execute();
> }
> return $sth->SUPER::execute(@_);
> }
>
> sub more_results {
> my ($sth) = @_;
> $sth->_more_results_prepare() or return;
> return $sth->_more_results_execute();
> }
>
> sub _more_results_prepare {
> my ($sth) = @_;
> my $statements = $sth->{statements}
> or return undef; # doesn't have multiple statements
> next_results:
> my $Statement = shift @$statements
> or return undef; # no more statements left
> $sth->prepare($Statement)
> or return 0; # prepare failure
> return $sth;
> }
>
> sub _more_results_execute {
> my ($sth) = @_;
> execute_next:
> my @params = splice @{ $sth->{parameters} }, 0, $sth-
> >{NUM_OF_PARAMS};
> my $rv = $sth->execute(@params)
> or return 0; # execute failure
> if ($sth->{BatchOnlySelect} && $sth->{NUM_OF_FIELDS} == 0) {
> # skip this non-select sub-statement
> $sth->_more_results_prepare or return;
> goto execute_next;
> }
> return $rv; # must be true but is otherwise undefined
> }
>
> sub finish {
> my ($sth) = @_;
> $sth->SUPER::finish; # discard any unfetched from current sub-
> statement
> $sth->{statements} = undef; # discard any unexecuted sub-statements
> $sth->{parameters} = undef; # discard any unused parameters
> return 1;
> }
>From what I understand, I'm not sure this maps onto the likes of Sybase and MSSQL.
>From what I understand from reading (and I may be woefully wrong here) a ; would have
>to separate each sub-statement in a batch. A corresponding Sybase batch would look
>like:
declare @foo int, @bar varchar
;
update biz set blarf = 'yomama' where bleech = 'yopapa'
;
select @foo = max(id) from biz where bleech = 'yopapa'
;
select @foo
;
But in sybase what you really need is
declare @foo int, @bar varchar
update biz set blarf = 'yomama' where bleech = 'yopapa'
select @foo = max(id) from biz where bleech = 'yopapa'
select @foo
;
or else the @foo and @bar are not seen after the first ; assuming ; maps to 'go' in
Sybase
So, in oracle, a ; would have to come after each statement for you (wearing your
DBD;;Oracle hat) to be able to execute that batch, but in Sybase, not so.
To be consistent across all DBs, you really need a statement terminator (;) and a
batch terminator (;; for this discussion), OR sybase and ilk simply ignore the ; since
they can already handle the batches - they just have to map existing functionality
onto your new functions and attribs.
Given that you've been in contact with M. Peppler, and you know Oracle a LOT better
than I do, I'm going to assume you have this covered and I am still missing something.
And that something may be the treatment of local variables in Oracle.
--
Matthew O. Persico
