Re: Clarification sought on execute_array

On 18/01/2011 19:24, John Scoles wrote:
>  On 18/01/2011 9:47 AM, Martin J. Evans wrote:
>> On 18/01/11 14:11, John Scoles wrote:
>>>   On 18/01/2011 8:35 AM, Martin J. Evans wrote:
>>>> John,
>>>>
>>>> I slightly reformatted you reply as you added comments on the end 
>>>> of lines I wrote which made it look like I said them.
>>>>
>>>> On 18/01/11 12:40, John Scoles wrote:
>>>>> On 17/01/2011 3:34 PM, Martin J. Evans wrote:
>>>>>> There appear to be differences between DBDs which do not handle
>>>>>> execute_array (so DBI does it for them) and DBDs which do handle
>>>>>> execute_array (e.g., DBD::Oracle). The main ones discussed on
>>>>>> #dbix-class which I investigated are whether the driver sets the
>>>>>> err and errstr or even raises an error. Some of the guys writing
>>>>>> DBIx::Class think execute_array should raise and error and fail on
>>>>>> the first error but I explained since execute_array may send the
>>>>>> entire batch to the server and it is server dependent when it stops
>>>>>> this is beyond definition by DBI. Never the less the following
>>>>>> script seems to show some large differences between DBI's
>>>>>> execute_array and DBD::Oracle's:
>>>>>>
>>>>> The first question is do any other DBIs utilize a native
>>>>> array_execute??
>>>>>
>>>>> Anyway
>>>> Not that I know of but if DBD::Oracle does not match what happens 
>>>> with a DBI execute_array then that is a problem for anyone writing 
>>>> DBD neutral code and it should be clearly documented so you can 
>>>> write DBD neutral code.
>>>>
>>>>> Well lets go back to DBI and see what it says
>>>>>
>>>>> When called in scalar context the execute_array() method returns the
>>>>> number of tuples executed, or |undef| if an error occurred.
>>>>> Like
>>>>> execute(), a successful execute_array() always returns true
>>>>> regardless of the number of tuples executed, even if it's zero.
>>>> I think you have misread this bit. It means (like execute) it is 
>>>> not an error to do nothing or something like;
>>>>
>>>> update mytable set mycol = 1 where mycol = 2
>>>>
>>>> where no mycol = 2 i.e., it will return success even though no 
>>>> change occurred.
>>>>
>>>> I don't think it means execute_array always returns success no 
>>>> matter what happens just because it is a batch.
>>>>
>>>>> If
>>>>> there were any errors the ArrayTupleStatus array can be used to
>>>>> discover which tuples failed and with what errors.
>>>>>
>>>>>
>>>>> In DBD::Oracle you will never get 'undef' returned as the execute
>>>>> will always be successful even though all of your tuples may fail.
>>>> and yet, you do get an undef back in my example so you we already 
>>>> have a contradiction.
>>>> See:
>>>>
>>>> Error from execute_array - ORA-24381: error(s) in array DML (DBD 
>>>> SUCCESS_WITH_INFO: OCIStmtExecute),0
>>>>
>>>> which is output because execute_array returned undef!
>>>>
>>>>       my (@tuple_status, $inserted);
>>>>       $inserted = 99;
>>>>       eval {
>>>>           $inserted = $sth->execute_array(
>>>>               { ArrayTupleStatus =>   \@tuple_status } );
>>>>       };
>>>>       if ($@) {
>>>>           print "Exception: $@\n";
>>>>       }
>>>>       print "inserted = ", DBI::neat($inserted), "\n";
>>>>       print "Error from execute_array - " . $sth->errstr . ",", 
>>>> $sth->err ."\n"
>>>>           if (!$inserted);
>>>>
>>>> outputs (for Oracle):
>>>>
>>>> The following is due to PrintWarn =>   1
>>>>     DBD::Oracle::st execute_array warning: ORA-24381: error(s) in 
>>>> array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement 
>>>> "insert into mytest values (?,?)"] at 
>>>> rt_data/execute_array/execute_array.pl line 44.
>>>>
>>>> The following is undef from execute_array:
>>>>     inserted = undef
>>>> The following is because execute_array returned undef:
>>>>     Error from execute_array - ORA-24381: error(s) in array DML 
>>>> (DBD SUCCESS_WITH_INFO: OCIStmtExecute),0
>>>>
>>>> Note the errstr value is set but not err (0) - that cannot be right 
>>>> surely.
>>>>
>>>>> So It agrees with the first para and works in scalar.
>>>> Funnily enough, it does agree with the first paragraph since an 
>>>> error occurred and it returned undef (unlike you reasoning) BUT it 
>>>> only set the error state to a warning and did not set "err".
>>>>
>>>> I have no issue it is a batch and executed in the server as one 
>>>> operation but DBD::Oracle does know something failed as it stands.
>>>>
>>>>> To get the extra info that comes out in a non-DBD specific
>>>>> array_execute we would have to build in an extra iteration over  the
>>>>> results to give a count of the Failed/Pass.  As some of my customers
>>>>> use this with batch loads of 5meg plus of inserts the iteration may
>>>>> take some time and sort of defeat the purpose of a quick way to do
>>>>> bulk inserts.
>>>> but John, DBD::Oracle already knows an error occurred.
>>>>
>>>>> I think (you will have to ask Tim to verify) that the Idea behind
>>>>> array_execute is a 'Batch' processor. ie send a Batch, to the server
>>>>> then figure out what to with what is returned.
>>>>>
>>>>> so with
>>>>>
>>>>> a) even though RaiseError was set, no error was raised although a
>>>>> warning was.
>>>> JS replied:
>>>>     We know there was a problem so we have to fail the batch or at 
>>>> lease report
>>>> on it is what the warning is telling us
>>>>
>>>> I'm in danger of repeating myself - an error did occur, DBD::Oracle 
>>>> knows this but it was not raised as an error.
>>>>
>>>>> b) execute_array returned undef (correct)
>>>> JS replied:
>>>>    Well at least that is a good thing
>>>>
>>>> You said "In DBD::Oracle you will never get 'undef' returned as the 
>>>> execute" but it did return undef.
>>>>
>>>>> c) errstr is set but err is not (0)
>>>>> d) the HandleError routine was not called - due to (a)?
>>>> JS replied:
>>>>    Which is correct as is did do exactly what was expected.  ie 
>>>> 'execute a bath
>>>> and report back'
>>>>
>>>> I fundamentally disagree here - my expectation was that all the 
>>>> rows in the batch succeed, some didn't, DBD::Oracle knows this and 
>>>> told me so by returning undef but failed to raise the error and set 
>>>> err.
>>>>
>>>>> e) the count of rows affected is -1 for all rows which worked - I
>>>>> believe this is permissible I will have to check on that.
>>>>>
>>>>> In the end I do not think this should ever error
>>>>>
>>>>> eval { $inserted = $sth->execute_array( { ArrayTupleStatus =>
>>>>> \@tuple_status } ); };
>>>>>
>>>>> It is the wrong way to process a batch job. JMHO though
>>>> What is wrong with the above?
>>> More just a personal thing I guess.
>>>
>>> The execute_array will always be 'successful' however sometimes it 
>>> will be more 'successful' than others so the above should never 
>>> throw an error.
>>>
>>> Warn yes but never error.
>> That is part of what I am saying is an inconsistency - I don't see 
>> why it is not an error as a) 1 or more rows failed and b) I cannot 
>> capture it in HandleError.
>>
>>> I guess I am looking at it from a Database 'Batch' (Bath for Merijn) 
>>> perspective.
>>>
>>> 1) Run batch
>>> 2) commit good inserts
>>> 3) fix bad inserts
>>> 4) rerun bad inserts
>> So what changes if it raises an error and sets err?
>>
>>> Perhaps the spec and the DBI code should be cleaned up a bit to 
>>> reflect the true nature of a Batch Job (fat chance though).
>> A very good chance of the docs being updated since once clarified I 
>> will write it up if no one else does.
>>
>>> If the above does send out an error than which one????
>> The one associated with the string you wrote into errstr.
>>
>>> There is only 'Success_with_Info' which is not an error as such this 
>>> can be found in all sorts of other contexts (lob truncate, end of 
>>> file etc, end of cursor edtc) you certainly do not want those others 
>>> to error out??  does tell us the info of course 'ORA-24381' but the 
>>> exe was succesful
>> We can argue the meaning of batch here. If the db executes the entire 
>> set as a batch and either all are committed or not then I'd expect an 
>> error if any failed since none are committed.
>>
>> If the batch is executed individually (or a status for each one is 
>> available, and they are committed individually) then 
>> SUCCESS_WITH_INFO might be more reasonable, I agree.
>>
>> However DBD::Oracle sits between the 2 since as soon as any fails 
>> none of the rest of the batch are even run:
>>
>> I tried to insert 4 rows and the second one failed:
>>
>> inserted = undef # execute_array = undef indicating an error
>> Error from execute_array - ORA-24381: error(s) in array DML (DBD 
>> SUCCESS_WITH_INFO: OCIStmtExecute),0 # error string set but not err 
>> number
>> $VAR1 = -1; # we don't know affected rows for first row but it worked
>>
>> $VAR1 = [
>>            1,
>>            'ORA-00001: unique constraint (BET.SYS_C0096218) violated 
>> (DBD SUCCESS_WITH_INFO)'
>>          ]; # this row failed and BTW, since when is ORA-00001 
>> success with info!
>>
>> $VAR1 = -1; # row 3 didn't seem fail but was it executed?
>>
>> $VAR1 = -1; # row 4 didn't seem to fail but was it execute?
>>
>> $VAR1 = [
>>            [
>>              '1',
>>              'onetwothree         '
>>            ]
>>          ];
>>
>> oh, but look at the database - only 1 row inserted. So, now, tell me 
>> how I know that row 3 and 4 were not executed - if this is the way 
>> Oracle works (as opposed to DBD::Oracle) then I'd say this has to be 
>> an error and not success with info especially since I've no idea what 
>> happened now AND the error for row 2 is an error.
>>
>>> We are talking some very old code here for DBD::Oracles exec_array.  
>>> I just resurrected it from a patch that was never applied in I think 
>>> 1.16 or 1.15 so it is well over 10 years old and most likely never 
>>> made to spec to begin with.
>> ah, are you saying you've applied an old patch recently?
>> I only ask because I submitted a patch in 2006 to change 
>> execute_array - see 
>> http://www.nntp.perl.org/group/perl.dbi.dev/2006/09/msg4634.html 
>> which certainly got in to change execute_array.
>>
>>> Lets see if there are any DBDs that do implement their own.
>> DBD::ODBC does not implement execute_array because a) it is fairly 
>> hard b) no one has asked for it. However, if I did then individual 
>> statuses are available for each row in the batch and if one fails but 
>> you did not provide a status array it is an error else it is success 
>> with info, but unlike DBD::Oracle/Oracle it does not stop processing 
>> on the first error.
>>
>>>>> I guess the real sort of problem is that in the normal DBI array
>>>>> fetch it is just iterating over array and doing the insert one at a
>>>>> time so you get your good and error counts as you go.  As well as
>>>>> each iteration is a separate execute you will get a raise_error with
>>>>> it which is think is suppressed but I would have to look at the
>>>>> code.
>>>> I appreciate the mechanics of DBI's execute_array are different but 
>>>> I was not asking for the DBI output which says 1 or 4 failed.
>>>>
>>>>> I think you are right that the the chaps at DBIx have it wrong.  It
>>>>> should be a batch job and they would have to handle in that way.
>>>>>
>>>>> 1) bind 2) exe 3) commit if all successful or  process if an error is
>>>>> returned.
>>>> and here is the point - "if an error is returned".
>>> Yes but the 'exe' itself did not error it only warns you something 
>>> is not right in the batch.  The '3' part above is one way to do it.  
>>> The more common DB way is to 'commit' what is good then 'fix' what 
>>> is bad.
>>>>> Anyway lets see what Tim has to say.
>>>>>
>>>>> We could add in the list context for DBD::Oracle and do some of this
>>>>> processing with the caveat that it will take longer than the scalar
>>>>> context
>>>> I don't think that is necessary.
>>>>
>>> Not sure about that I might be one way to have them all work the same.
>>>
>>> Agree that something is missing from DBD::Oracle or it needs to be 
>>> tweaked a bit and it should be more DBI neutral Like to hear what 
>>> 'Merijn' has to say about it as he does a great deal of work on 
>>> having DBD neutral code.
>>>
>>> Do you ever use exe_array Merijin??
>> he doesn't - he already said on #dbi
>>
>> Martin
>>
>>> Cheers
>>> John
>>>>> Cheers John
>>>> Martin
>>>>
>
> I had a quick look over the code in OCI which seems to be doing it own 
> little sort of thing that is outside the DBI spec.
>
I don't necessarily mind that if it makes sense and is workable via the 
docs and through a db neutral execute_array method.

> Checking a really old OCI (8) doc I have I can see where the original 
> array_exe spec comes from we will have to ask Tim about that though as 
> that is well before my time.
>
>
> Will have to dig a little deeper to see what is going on here.
>
> One thing that I will have to clarified is how the Batch is suppose to 
> work.  Seems according to the OCI doc the proper behaviour is to error 
> and stop at first error unless OCI_BATCH is set.
>
My understanding of OCI_BATCH_ERROR was that it continued but it doesn't 
right now for me and I cannot commit the row before that worked.

> Will have to do a careful read though of all the docs and see.
>
>
> So the plan for now is
>
> 1) Get a handle on what DBI expects
> 2) Get a handle on how OCI wants to work
> 3) try to reconcile the two.
> 4) I also noticed that the OCI code is using a call back which I think 
> was standard in earlier OCI version but I think is obsolete now.  But 
> I will have to look into it.
>
May be this (4) is why it is changed.

> More work for Johny I'm afraid.  At least this time round I am a 
> little more familuar with the code than I was 6 years ago. :)
>
> Your code is a good start but I might change it a bit so we have both 
> list and scalar implementations.
>
go ahead though if it makes a difference that is a bug in itself.

> Cheers
> John Scoles

Thanks John.

I'd be happy with a warning if some of the rows actually got into the 
database (with autocommit or an explicit commit) as it would be a 
partial success but as it stands it is a complete failure and hence I'd 
expect an error.

Martin

>>>>>> use DBI; use strict; use Data::Dumper;
>>>>>>
>>>>>> sub fred { print "Error Handler called\n"; print Dumper(\@_); my
>>>>>> ($msg, $handle, $val) = @_;
>>>>>>
>>>>>> print "handle_error: $msg\nhandle: $handle\nval=$val\n"; 0; }
>>>>>>
>>>>>> my $dbh = DBI->connect( 'DBI:Oracle:host=xxx;sid=devel', 'xxx',
>>>>>> 'xxx', { RaiseError =>   1, PrintError =>   0, HandleError =>   
>>>>>> \&fred
>>>>>> }); do_it($dbh);
>>>>>>
>>>>>> my $dbh = DBI->connect( 'DBI:ODBC:DSN=xxx', 'xxx', 'xxx', {
>>>>>> RaiseError =>   1, PrintError =>   0, HandleError =>   \&fred });
>>>>>>
>>>>>> do_it($dbh);
>>>>>>
>>>>>> sub do_it { my $dbh = shift;
>>>>>>
>>>>>> eval {$dbh->do(q/drop table mytest/);}; $dbh->do(q/create table
>>>>>> mytest (a int primary key, b char(20))/);
>>>>>>
>>>>>> my $sth = $dbh->prepare(q/insert into mytest values (?,?)/);
>>>>>> $sth->bind_param(1, 1); $sth->bind_param(2, 'onetwothree');
>>>>>> $sth->execute;
>>>>>>
>>>>>> $sth->bind_param_array(1, [51,1,52,53]); $sth->bind_param_array(2,
>>>>>> ['fiftyone', 'fiftytwo', 'fiftythree', 'one']); my (@tuple_status,
>>>>>> $inserted); eval { $inserted = $sth->execute_array( {
>>>>>> ArrayTupleStatus =>   \@tuple_status } ); }; if ($@) { print
>>>>>> "Exception: $@\n"; } print "Error from execute_array - " .
>>>>>> $sth->errstr . ",", $sth->err ."\n" if (!$inserted); for
>>>>>> (@tuple_status) { print Dumper($_), "\n"; } }
>>>>>>
>>>>>> which outputs for the DBD::Oracle part:
>>>>>>
>>>>>> $ perl execute_array/execute_array.pl DBD::Oracle::st execute_array
>>>>>> warning: ORA-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO:
>>>>>> OCIStmtExecute) [for Statement "insert into mytest values (?,?)"]
>>>>>> at execute_array/execute_array.pl line 43. Error from execute_array
>>>>>> - ORA-24381: error(s) in array DML (DBD SUCCESS_WITH_INFO:
>>>>>> OCIStmtExecute),0 $VAR1 = -1;
>>>>>>
>>>>>> $VAR1 = [ 1, 'ORA-00001: unique constraint (BET.SYS_C0096150)
>>>>>> violated (DBD SUCCESS_WITH_INFO)' ];
>>>>>>
>>>>>> $VAR1 = -1;
>>>>>>
>>>>>> $VAR1 = -1;
>>>>>>
>>>>>> Notable from this is that:
>>>>>>
>>>>>> a) even though RaiseError was set, no error was raised although a
>>>>>> warning was. b) execute_array returned undef (correct) c) errstr is
>>>>>> set but err is not (0) d) the HandleError routine was not called -
>>>>>> due to (a)? e) the count of rows affected is -1 for all rows which
>>>>>> worked - I believe this is permissible
>>>>>>
>>>>>> For the DBD::ODBC run which does not do execute_array itself you
>>>>>> get:
>>>>>>
>>>>>> Error Handler called $VAR1 = [ 'DBD::ODBC::st execute_array failed:
>>>>>> executing 4 generated 1 errors', bless( {}, 'DBI::st' ), undef ];
>>>>>> handle_error: DBD::ODBC::st execute_array failed: executing 4
>>>>>> generated 1 errors handle: DBI::st=HASH(0xa071d00) val=Exception:
>>>>>> DBD::ODBC::st execute_array failed: executing 4 generated 1 errors
>>>>>> at execute_array/execute_array.pl line 43.
>>>>>>
>>>>>> Error from execute_array - executing 4 generated 1
>>>>>> errors,2000000000 $VAR1 = 1;
>>>>>>
>>>>>> $VAR1 = [ 1, '[unixODBC][Easysoft][SQL Server Driver][SQL
>>>>>> Server]Violation of PRIMARY KEY constraint
>>>>>> \'PK__mytest__3661ABE9\'. Cannot insert duplicate key in object
>>>>>> \'dbo.mytest\'. (SQL-23000) [state was 23000 now 01000]
>>>>>> [unixODBC][Easysoft][SQL Server Driver][SQL Server]The statement
>>>>>> has been terminated. (SQL-01000)', '01000' ];
>>>>>>
>>>>>> $VAR1 = 1;
>>>>>>
>>>>>> $VAR1 = 1;
>>>>>>
>>>>>> Notice the difference:
>>>>>>
>>>>>> a) an error was raised (different from DBD::Oracle) saying 1 of 4
>>>>>> failed b) execute_array returned undef (the same) c) both errstr
>>>>>> and err are set although where 2000000000 comes from I'm not sure
>>>>>> d) the HandleError routine was called (different from DBD::Oracle)
>>>>>> e) the count of rows affected is 1 for all the rows which worked
>>>>>>
>>>>>> For anyone using execute_array this represents somewhat of a
>>>>>> problem unless they write substantial code per DBD. The
>>>>>> clarification required is:
>>>>>>
>>>>>> a) if execute_array fails on any row should that raise an error?
>>>>>> Obviously, if it does, then HandleError comes in to it b) if
>>>>>> execute_array fails should that set errstr AND err
>>>>>>
>>>>>> I believe the count per row of affected is driver dependent so I'll
>>>>>> ignore that but there is a lot of code out there (perhaps doing
>>>>>> things wrong) which examines "err" (like DBIx::Class) which is not
>>>>>> set in DBD::Oracle's case. The strict interpretation of the pod for
>>>>>> execute_array suggests execute_array will return undef on any
>>>>>> failure (which it does in both cases) but not whether any row is an
>>>>>> error/warning and whether "err" and "errstr" are set.
>>>>>>
>>>>>> BTW, please keep Peter (ribasushi) on the cc list as he is not
>>>>>> subscribed to dbi-dev but is an interested party.
>>>>>>
>>>>>> Martin
>