Tim Bunce wrote:
On Fri, Sep 22, 2006 at 05:40:53PM -0700, Dean Arnold wrote:
Just so I'm clear:
"DBI's default execute_array()/execute_for_fetch() requires the use of
positional (i.e., '?') placeholders. Drivers which B<require> named
placeholders must implement their own execute_array()/execute_for_fetch()
methods to properly sequence bound parameter arrays."
"... or they can emulate positional placeholders (DBD::Oracle does this)".
I'd guess that emulating positional placeholders would be both simpler
and more useful for general script portability.
Could you update the docs? (If you don't have write access to the dbi
repository yet, send me your auth.perl.org username and I'll give it to
you.)
Time to get this out of my TODO queue...
I noticed that DBI::DBD was silent on the whole area of
array binding, so here's what I've added.
- Dean
=head4 The execute_array(), execute_for_fetch() and bind_param_array() methods
In general, DBD's only need to implement C<execute_for_fetch()> and
C<bind_param_array>. DBI's default C<execute_array()> will invoke the
DBD's C<execute_for_fetch()> as needed.
The following sequence describes the interaction between
DBI C<execute_array> and a DBD's C<execute_for_fetch>:
=over
=item 1
App calls C<$sth-E<gt>execute_array(\%attrs, @array_of_arrays)>
=item 2
If C<@array_of_arrays> was specified, DBI processes C<@array_of_arrays> by
calling
DBD's C<bind_param_array()>. Alternately, App may have directly called
C<bind_param_array()>
=item 3
DBD validates and binds each array
=item 4
DBI retrieves the validated param arrays from DBD's ParamArray attribute
=item 5
DBI calls DBD's C<execute_for_fetch($fetch_tuple_sub, [EMAIL PROTECTED])>,
where C<&$fetch_tuple_sub> is a closure to iterate over the
returned ParamArray values, and C<[EMAIL PROTECTED]> is an array to receive
the disposition status of each tuple.
=item 6
DBD iteratively calls C<&$fetch_tuple_sub> to retrieve parameter tuples
to be added to its bulk database operation/request.
=item 7
when DBD reaches the limit of tuples it can handle in a single database
operation/request, or the C<&$fetch_tuple_sub> indicates no more
tuples by returning undef, the DBD executes the bulk operation, and
reports the disposition of each tuple in [EMAIL PROTECTED]
=item 8
DBD repeats steps 6 and 7 until all tuples are processed.
=back
E.g., here's the essence of L<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;
}
Note that DBI's default execute_array()/execute_for_fetch() implementation
requires the use of positional (i.e., '?') placeholders. Drivers
which B<require> named placeholders must either emulate positional
placeholders (e.g., see L<DBD::Oracle>), or must implement their own
execute_array()/execute_for_fetch() methods to properly sequence bound
parameter arrays.
