On Mon, Sep 04, 2006 at 05:21:17PM +0100, Martin J. Evans wrote:
>
> It did raise an issue where I could do with some clarification. The slides
> say:
>
> Execute a statement for multiple values
> $sth = $dbh->prepare("insert into table (foo,bar) values (?,?)");
> $tuples = $sth->execute_array(\%attr, [EMAIL PROTECTED], [EMAIL
> PROTECTED]);
>
> returns count of executions (even ones that failed) and not rows-affected.
>
> but the latest DBI pod says:
>
> The execute_array() method returns the number of tuples executed,
> or "undef" if an error occured. Like execute(), a successful exe-
> cute_array() always returns true regardless of the number of tuples
> executed, even if it's zero. See the "ArrayTupleStatus" attribute
> below for how to determine the execution status for each tuple.
>
> I read that latter as if undef is returned 1 or more of the executes failed
> but
> now I read in the slides they suggest I may get a true value when an error
> occurs. My experience with execute_array and Oracle shows I get:
>
> return 0, when none resulted in a change but no errors
> e.g.
> drop table mytest
> create table mytest (a int primary key, b char(20))
> insert into mytest values (1,'onetwothree')
> update mytest set b = ? where a = ?
> $sth->bind_param_array(2, [2])
> $sth->bind_param_array(1, ['two'])
> $sth->execute_array( { ArrayTupleStatus => [EMAIL PROTECTED] } )
>
> returns 0
>
> No, error, just no update occurred. It does not return 1 as the slide
> suggests and does seem to be the number of rows changed - none.
>
> returns undef when there is an error
> e.g.
> as per above example but the bind_param_array for p2 is invalid e.g. 'xxx'
>
> This seems to also contradict the slide but agrees with the DBI pod.
>
> lastly I get undef if there are multiple rows but the last one errors.
> e.g. as above except the bind_param_arrays are:
>
> $sth->bind_param_array(2, [1, 'xxx']);
> $sth->bind_param_array(1, ['one', 'two']);
>
> returns undef but executes the 1,'one'.
>
> I am now unclear as to what is correct. Can you clarify?
The pod is right and both the slides and DBD::Oracle are wrong,
though in different ways.
The behaviour should match that of the default execute_for_fetch
method (which execute_array calls) provided by the DBI - shown below.
Thanks! I've fixed my copy of the slide to say "returns count of
executions, not rows-affected, or undef if any failed".
Tim.
p.s. I'd be grateful if you could file a bug at rt.cpan.org for DBD::Oracle.
sub execute_for_fetch {
my ($sth, $fetch_tuple_sub, $tuple_status) = @_;
# start with empty status array
($tuple_status) ? @$tuple_status = () : $tuple_status = [];
my ($err_count, %errstr_cache);
while ( my $tuple = &$fetch_tuple_sub() ) {
if ( my $rc = $sth->execute(@$tuple) ) {
push @$tuple_status, $rc;
}
else {
$err_count++;
my $err = $sth->err;
push @$tuple_status, [ $err, $errstr_cache{$err} ||=
$sth->errstr, $sth->state ];
}
}
my $tuples = @$tuple_status;
return $sth->set_err(1, "executing $tuples generated $err_count errors")
if $err_count;
return scalar(@$tuple_status) || "0E0";
}
