On Tue, Jun 10, 2003 at 10:03:46PM +0200, Kristian Nielsen wrote: > Tim Bunce <[EMAIL PROTECTED]> writes: > > > Seeing your message has prompted me to add something I'd been meaning > > to do for a while that will simplify this somewhat: > > > > =item C<execute_for_fetch> > > > > $rv = $sth->execute_for_fetch($fetch_tuple_sub); > > $rv = $sth->execute_for_fetch($fetch_tuple_sub, [EMAIL PROTECTED]); > > > So now drivers wanting to support bulk operations do not need to > > implement the full execute_array API, they just need to implement > > execute_for_fetch(). > > > > For DBD::Oracle you need to setup a callback so *Oracle* will call > > the $fetch_tuple_sub code ref (via a C wrapper) to get the next row :) > > Sounds great! However, after reading OCI docs a bit, I am not sure this > will work. The problem is that, as far as I can tell, OCIStmtExecute() > expects to be told the number of bind values up front;
Umm... [...rummage...] http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a96584/oci05bnd.htm#420999 says to use OCI_DATA_AT_EXEC and then call OCIBindDynamic() to register the callback for each placeholder that'll provide the value for each execute. http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a96584/oci15r31.htm#444016 says the callbacks must "return OCI_CONTINUE if it the call is successful. Any return code other than OCI_CONTINUE signals that the client wishes to abort processing immediately." So it seems that the callbacks can't just signal 'no more data'. Phooey. Without that you'd need to 'pre-fetch' a batch of rows from $fetch_tuple_sub and then execute that batch. I was hoping to avoid that as it means the batch of rows has to be stored somewhere first. Still, I think it's not too bad. Just needs one more level of code... write a execute_for_fetch in perl to batch up chunks for processing. Something like: sub execute_for_fetch { my ($fetch_tuple_sub, $tuple_status) = @_; while (1) { my @tuple_batch; for (my $batch_size = 10000; $batch_size-- > 0; ) { push @tuple_batch, $fetch_tuple_sub->() || last; } last unless @tuple_batch; $sth->ora_execute_array([EMAIL PROTECTED], \my @tuple_batch_status); push @$tuple_status, @tuple_batch_status; } } Then you implement ora_execute_array to work with the ref to an array of tuple array refs. You know how many there are, and the storage can be used directly. The control flow through $sth->execute_array to $sth->execute_for_fetch to $sth->ora_execute_array isn't a problem. Each is simplifying the task for the next. Taking a step back for a moment... The switch from column-wise arrays in execute_array() to row-wise arrays in ora_execute_array() is interesting. Perhaps there's scope for the DBI to add a new execute_* method to do what ora_execute_array is doing here. Take a simple set of tuples (as array of tuple arrays) plus a status array. Then I could change the default execute_for_fetch method in the DBI to call that one. Driver authors would then have more options in what they override. Comments anyone? > If not, two immediate options spring to mind: > > 1. Change the execute_for_fetch() interface, passing the count: > > $rv = $sth->execute_for_fetch($count, > $fetch_tuple_sub, > [EMAIL PROTECTED]); I could add an optional count to execute_for_fetch to help some drivers which could use it, but it wouldn't help people who want to do: $sth1->execute_array({ ArrayTupleFetch => $sth2 }); Tim.
