On 05-Sep-2006 Tim Bunce wrote:
> 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.
OK, will report these issue this afternoon.
>> 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.
That is fair enough. I wasn't really suggesting it was changed, it was more of
a pointing out the inconvenience in case it led to a better way for me to get
the rows affected...
> 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.
and it did. Yes, this would be ideal and second guessing you I even looked at
the code to implement it but was slightly flumaxed by the possible definition
changes to "Dynamically create the DBI Standard Interface" and the
problem that it can only be calculated if ArrayTupleStatus is specified and
that is optional now. Any pointers on these welcome and I'll attempt a patch.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
