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"; }