On Thu, Sep 07, 2006 at 04:06:05PM -0700, Dean Arnold wrote:
> Tim Bunce wrote:
> >
> >The default execute_array method calls execute_for_fetch. So drivers
> >only have to implement execute_for_fetch - and execute_for_fetch is
> >designed to allow drivers to decide batch sizes for themselves.
>
> Please excuse my ignorance, I've had severe cognitive turbulence
> wrt exec_for_fetch for some time now, so hopefully you can confirm
> or correct the following ad-hoc sequence diagram of the process ?
> (This is for the simplest case, no app-provided fetch_tuple_sub)
>
> App calls $sth->execute_array(\%attrs, @array_of_arrays);
>
> => DBI processes @array_of_arrays by calling
> DBD's bind_param_array()
>
> => DBD validates and binds each array
>
> => DBI retrieves the validated param arrays from
> DBD's ParamArray attribute
>
> => DBI creates $fetch_tuple_sub to simply iterate over
> the returned ParamArray values
>
> => DBI calls DBD's execute_for_fetch
>
> => DBD calls $fetch_tuple_sub to retrieve each
> parameter tuple
>
> => when DBD reaches batch limit, or no more tuples,
> DBD sends to DBMS, and reports status in
> [EMAIL PROTECTED]
The notional 'batch limit' here is just for 'chunking' inside execute_for_fetch.
The application need never be aware there is any kind of batch limit.
The DBI's default execute_for_fetch effectively has a batch limit of 1.
Here's the essence of DBD::Oracle's execute_for_fetch:
while (1) {
my @tuple_batch;
for (my $i = 0; $i < $batch_size; $i++) {
push @tuple_batch, [ @{$fetch_tuple_sub->() || last} ];
}
last unless @tuple_batch;
my $res = ora_execute_array($sth, [EMAIL PROTECTED],
scalar(@tuple_batch), $tuple_batch_status);
push @$tuple_status, @$tuple_batch_status;
}
Hopefully that'll help.
> However, that still leaves open the issue of unsent tuples, e.g.,
> a connection failure occurs after 10,000 tuples are sent and
> committed, and 10,000 tuples remain. How do we inform the app
> of the unsent tuples (for recovery purposes) ? (Which is one
> use of the suggested -2 status code).
The tuple status array just wouldn't have entries beyond the last tuple
sent - they'd effectively be undef.
> Using the above scenario,
> the DBD would just keep consuming tuples and marking their status
> as -2 (which might be a rather lengthy process unless the app limits
> itself to reasonable chunks of tuples per execute_array() call).
> E.g., since the default DBI exec_for_fetch() doesn't have a way
> to bust out of its loop until all tuples are consumed, the
> default execute() return code may pile up a lot of error msgs
> to the effect "No connection available" (short of some HandleError
> closure, I guess ?).
There's no reason execute_for_fetch can't return early if there's been
an error that means it can't continue.
> Whereas, using the simple status code, the app can just
> submit a batch of tuples via execute_array(), then
> scan the @tuple_status to identify any unsent tuples (presumably
> immediately preceded by a status with an error code).
I think undef makes most sense for tuples have have not been sent to
the server, and then all other error codes are available for the
recording errors for tuples that did get sent.
> Once the connection
> is recovered, the app can just resubmit the unsent tuples.
Those unsent plus any with error codes indicating a temporary problem.
> (This may be a difficult issue for $sth based fetch_tuple_sub unless some
> known ordering were applied in the source statement).
True.
> That said, I guess I'm free to return the -2 status and note it as
> driver-specific behavior.
I'd prefer undef for that.
> >In perl:
> >
> > $h->SUPER::execute_array(...)
> >
> >(but I'd recommend just implementing execute_for_fetch instead).
>
> Which would presumably just route each tuple to regular execute()
> for the legacy case ?
Yeap, see the source :)
Tim.
