On 18/01/2011 9:47 AM, Martin J. Evans wrote:Just as a Side note seems execute_array was added well after the first DBI spec was written.
DBI 1.24, 4th June 2002 seems to be the correct date rather a late addition.
Cheers John
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?? AnywayNot 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 thoughWhat 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 insertsSo 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 succesfulWe 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 contextI 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 MartinCheers JohnCheers JohnMartinuse 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
