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
>>
>>
>>>
>>>> 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
>>>
>
