On 04-Sep-2006 Tim Bunce wrote:
> 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";
> }
Thanks for the clarification.
I'll report the problems just as soon as I'm certain what they are. Here is
my summary:
1. insert into table values (?,?) where all values are valid and 3 rows of
values
returns: 3
(correct as 3 tuples executed)
ArrayTupleStatus = (-1, -1, -1)
(incorrect - should be (1,1,1) as DBD::Oracle does know a row was
inserted each time as evidenced by execute itself which returns rows
affected)
2. insert into table values (?,?) where there are 2 rows of data but second one
is invalid
returns: undef
(correct)
ArrayTupleStatus = (-1, [1772, 'ORA-01722.......'])
(incorrect as this should be (1, [1772, 'ORA-01722.......'])
probably same issue as 1).
3. update table where the update does not affect any rows
returns: 0
(incorrect - should be 1 for 1 tuple executed)
ArrayTupleStatus = (-1)
(incorrect - should be (0) for no rows affected)
If my conclusions look right to you I'll report them on rt.
As an aside, I am finding execute_array returning the number of tuples executed
rather than the number of rows affected a bit of a pain. It seems to make it
easy to ascertain how many tuples were executed but hard to find out how
many rows were affected when I would have thought the former is less useful
and easily calculated as [EMAIL PROTECTED]). What I normally do on a
single insert/update execute is:
$affected = $sth->execute(@params)
ERROR if ($affected != $expected_inserts_or_updates);
as this catches errors in the execute and error in the program logic (not
inserting/updating what I expected). execute_array does not map easily to being
n * execute as far as I can see since the equivalent test for execute_array is:
$tuples_executed = $sth->execute_array
if (!$tuples_executed) {
# there was an error
} else {
# here $tuples_executed has no meaning for me since I know what it is going
# to be before calling execute_array - it is going to be the number of tuples
# I passed to execute_array
my $affected = 0;
$affected += $_ foreach (@array_tuple_status);
ERROR if ($affected != $expected_inserts_or_updates)
}
It's possible I've misunderstood what is returned by execute_array but it is
difficult to actually try it out when I don't seem to have a driver that does
it properly.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
