On Wed, Mar 03, 2004 at 09:16:37AM +0100, Kristian Nielsen wrote:
> [When I first sent this it bounced with the message:
>
> <[EMAIL PROTECTED]>:
> ezmlm-send: fatal: Sorry, after removing unacceptable MIME parts from your message I
> was left with nothing (#5.7.0)
>
> Not sure what that means, but I'll try again with the patch inline
> instead of as an attachment.]
Make sure the attachment is "text/plain" MIME type.
> Tim Bunce <[EMAIL PROTECTED]> writes:
>
> > How about this... Don't implement execute_array, just use the DBI default.
>
> Ok, I have merged my DBD::Oracle execute_array() patch into a subversion
> checkout and simplified it to implement execute_for_fetch() instead of
> execute_array() (included below).
>
> I get segfaults in make test t/30long.t, but I get that both with and
> without the patch.
>
> I will
>
> - Add docs for the problem with Oracle < 8.1.5.
> - Add some tests in t/20select.t.
I'd prefer a new test file, say, t/45execary.t
> - Check up on the ArrayTupleStatus elements; it seems a third 'state'
> element has been added to every error tuple.
Can be left undef.
> Anything else you would like before including it?
>
> And finally for some meaningless timings. With the patch I ran the
> following in less than one second:
>
> $s = $dbh->prepare("INSERT INTO mytable VALUES(?)");
> $s->execute_array(undef, [0..9999]);
>
> while this ran in 7 seconds:
>
> $s->execute($_) for (0..9999);
>
> While real-life speedups may be more modest, this is still encouraging!
Very. Thanks!
> -----------------------------------------------------------------------
> Index: oci8.c
> ===================================================================
> --- oci8.c (revision 174)
> +++ oci8.c (working copy)
> @@ -383,7 +407,7 @@
> PerlIO_printf(DBILOGFP, " in '%s' [%lu,%lu]: len %2lu, ind %d%s\n",
> phs->name, ul_t(iter), ul_t(index), ul_t(phs->alen), phs->indp,
> (phs->desc_h) ? " via descriptor" : "");
> - if (index > 0 || iter > 0)
> + if (!tuples_av && (index > 0 || iter > 0))
> croak("Arrays and multiple iterations not currently supported by DBD::Oracle
> (in %d/%d)", index,iter);
That message needs rewording now.
> Index: Oracle.pm
> ===================================================================
> --- Oracle.pm (revision 174)
> +++ Oracle.pm (working copy)
> @@ -841,7 +841,36 @@
>
> { package DBD::Oracle::st; # ====== STATEMENT ======
>
> - # all done in XS
> + sub execute_for_fetch {
> + my ($sth, $fetch_tuple_sub, $tuple_status) = @_;
> + my $row_count = 0;
> + my $batch_size = ($sth->{ora_array_chunk_size} ||= 1000);
> + my $tuple_batch_status;
> +
> + if(defined($tuple_status)) {
> + @$tuple_status = ();
> + $tuple_batch_status = [ ];
> + }
> + 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);
> + if(defined($res) && defined($row_count)) {
> + $row_count += $res;
> + } else {
> + $row_count = undef;
> + }
> + push @$tuple_status, @$tuple_batch_status
> + if defined($tuple_status);
> + }
> + return $row_count;
I think the return value isn't right. I'd there's an error then do ++$err_count,
then on return do: return ($err_count) ? undef : scalar @$tuple_status;
The docs say:
: The number of tuples executed is returned I<only if> there were no errors.
: If there were any errors then C<undef> is returned and the @tuple_status
: array can be used to discover which tuples failed and with what errors.
> Index: dbdimp.c
> ===================================================================
> --- dbdimp.c (revision 174)
> +++ dbdimp.c (working copy)
> @@ -853,8 +854,10 @@
> if (imp_sth->all_params_hv == NULL)
> imp_sth->all_params_hv = newHV();
> phs_sv = newSVpv((char*)&phs_tpl, sizeof(phs_tpl)+namelen+1);
> + phs = (phs_t*)(void*)SvPVX(phs_sv);
> hv_store(imp_sth->all_params_hv, start, namelen, phs_sv, 0);
> - strcpy( ((phs_t*)(void*)SvPVX(phs_sv))->name, start);
> + phs->idx = idx-1; /* Will be 0 for :1, -1 for :foo. */
> + strcpy(phs->name, start);
> }
Is that phs->idx = idx-1 needed?
Or, looking at it a different way, does this work:
$sth = $dbh->prepare("INSERT INTO mytable VALUES( :foo )");
$sth->bind_param_array(":foo", [0..9999]);
$s->execute_array(undef);
It ought to.
> +static int
> +do_bind_array_exec(sth, imp_sth, phs)
Beyond here it looks like you've done more work than needed. I was
expecting the dbd_phs_in() callback function to be extended (slightly)
so it knew how to use the oracle supplied index to index into the array.
Ought to be just a few lines in dbd_phs_in() and a few lines elsewhere
to tell Oracle the size of the array being bound.
Similarly a few lines in the dbd_phs_out() callback would let
bind_param_inout_array() work.
Why couldn't it be some that way?
I think if it was, all the fiddling around in ora_st_bind_for_array_exec()
wouldn't be needed. Oracle would look after all that for you based on
what dbd_phs_in() returned.
Tim.
