Re: more on execute_array not complying with the specification

On 30/01/2011 18:02, John Scoles wrote:
> One other thing I was picking up on is the case if you have
>
> autocommit on and RaiseError off
>
> I this case it if there was a an error while running in list mode it
> will allways return undef.  Where looking at the spec one might expect
> to see undef and XX which would be the # ot records effected?
>
> Am I reading that wrong??
>
> ie with 20 items in your tuples
>
> ($tuples, $rows) = $sth->execute_array(
>
> and there is are two errors one might expect
>
> $tuples=undef and $rows=18?
>
> I think right now you are just getting
>
> $tuples=undef   and nothing is being sent back for $rows
>
> Cheers
> John
>
The current spec says that in array context the $tuples is exactly as if 
you had called execute_array in scalar context and $rows (in your 
example) is the sum of the rows affected. For DBD::Oracle (and I imagine 
most DBDs [if not all] that implement execute_array themselves) the rows 
affected will be -1 per execute (as the driver does not know affected 
rows) and so I'd expect $rows to be -1 and not 18. DBI gets away with 
this as it does an execute for each row and hence knows the rows 
affected as the return from each execute. The statement saying sum of 
affected rows should not sum a load of -1s affected to be -N. So for 
DBD::Oracle and array context $rows (in your example) should be -1 as 
DBD::Oracle/Oracle does not return the number of affected rows for the 
batch or even individually.

Martin

> On Sun, Jan 30, 2011 at 12:46 PM, Martin J. Evans
> <[email protected]>  wrote:
>> Hi,
>>
>> I'm still working on and off with execute_array problems hoping to get DBI and the spec to match and also investigating if the current implementation covers all possibilities. I've now written a simple test to see if it complies with the specification (and will include it in DBD::ODBC) and I don't think it does. The main point is:
>>
>> the pod says:
>>
>> "The ArrayTupleStatus attribute can be used to specify a reference to an array which will receive the execute status of each executed parameter tuple. Note the ArrayTupleStatus attribute was mandatory until DBI 1.38.
>>
>> For tuples which are successfully executed, the element at the same ordinal position in the status array is the resulting rowcount. If the execution of a tuple causes an error, then the corresponding status array element will be set to a reference to an array containing the error code and error string set by the failed execution."
>>
>> but the test shows the ArrayTupleStatus contains 3 elements on an error and not "an array containing the error code and error string set by the failed execution".
>>
>> e.g.,:
>>
>> # $VAR1 = [
>> #           1,
>> #           1,
>> #           1,
>> #           [
>> #             1,
>> #             '[unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]Violation
>>   of PRIMARY KEY constraint \'PK__PERL_DBD__3BD0198E526429B0\'. Cannot insert dup
>> licate key in object \'dbo.PERL_DBD_execute_array\'. (SQL-23000) [state was 2300
>> 0 now 01000]
>> # [unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]The statement has been
>>   terminated. (SQL-01000)',
>> #             '01000'
>> #           ],
>> #           1
>> #         ];
>>
>> It is down to the following line of code:
>>
>> push @$tuple_status, [ $sth->err, $sth->errstr, $sth->state ];
>>
>>
>> so I guess the pod should say:
>>
>> "If the execution of a tuple causes an error, then the corresponding status array element will be set to a reference to an array containing the err, errstr and state set by the failed execution. If that is the case let me know and I'll amend the pod. Otherwise, I'll need to know what was intended.
>>
>> Attached is my current test code but I'm still working on it. In particular, I've not found an ODBC driver which aborts on the first insert failure yet.
>>
>> Martin
> --
> The best compliment you could give Pythian for our service is a referral.
>
>