On Tue, Sep 05, 2006 at 11:20:04AM +0100, Martin J. Evans wrote:
>
> 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.
You're spot on.
> 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.
The DBIs default behaviour is the official behaviour.
I take your point about the return value. I remember weighing up both
alternatives before settling on 'tuples executed' - but I can't now
remember why! I probably also didn't consider bulk updates as a likely
use case.
How about adding total rows affected as an extra return value in list
context?:
($tuples_executed, $rows_affected) = $sth->execute_array(...)
Tim.
