On 18/01/11 15:29, 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.
>>
> My point is the exe did not error it was successful it also will return
> 'Success with Info' is the inserts are delayed or if a lob is truncated for
> example not an error just info returned
>>> 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?
>
> Like I said there may not be an error 'inserting' I could see it warn on a
> truncation
>>
>>> 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.
>>
> Yeah!!
>>> If the above does send out an error than which one????
>> The one associated with the string you wrote into errstr.
>>
> Yes that is true but the 'error' on the exe is the one I talking about. I
> little distinction which is important in my view
>>> 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.
>
> I should of been more clear here. There is never an implicit commit at the
> end of an exe_array one still has to do an 'commit' statement at the end.
>
> So the batch should be
> 1) process
> 2) report back
> 3) commit the good ones
> 4) process the bad ones (if any)
>
>> 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:
>
> Are you sure that is correct??
>
> I should process all then you will have to do the commit oneself the bad ones
> are not committed??
>
> Will have to give that a go today.
>
>> 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.
>>
> No I did that patch in 1.18 the first one I ever did myself so it was a
> little rough to say the least.
>>> 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.
>>
> DBD::Oracle should not stop processing maybe it is because you have
> RaiseError??
>
>>>>> 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??
> Use I did not say implement in a DBD. I mean call it in a script.
>> he doesn't - he already said on #dbi
>>
>> Martin
>>
>
> I have some cycles this week so I will spend a little time on it and work out
> what it should or should not do according to the Spec
>
> Give us a few days I will get back to you by Thursday.
>
> Cheers
> John
>
>>> 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
>
In my example below:
1) execute_array returns undef and DBI says this means an ERROR, not a warning.
If you want to argue that execute_array did not fail then why does DBD::Oracle
return undef here instead of true.
2) as execute_array returns undef (an error - see (1)) I expect an error and my
error handler to be called.
3) as execute_array returns undef (an error - see (1)) I expect $dbh->err to be
an error number but it is 0 (meaning warning) and yet $dbh->errstr is an error
string containing ora-00001 (which is an error yet again). DBI
4) DBD::Oracle or Oracle appears to be stopping after the first row which fails
(or at least not committing any successful rows) which suggests that despite
all your arguments that this is a batch that it is in fact a batch but
"executed separately". Worse still, since Oracle cannot return a rows affected
for each row all the rows which were never executed contain -1 (as do those
ones which did execute) so other than from experience there is no way to know
which rows were executed if execute_array failed. Update, I forgot about array
context for execute_array (which is laughable as I added it) which would allow
you to work out which rows were executed so long as they are executed in order.
I see the code says:
if(autocommit)
oci_mode |= OCI_COMMIT_ON_SUCCESS;
You say "The more common DB way is to 'commit' what is good then 'fix' what is
bad." but how do you do that as a) you cannot call commit when AutoCommit is
enabled and b) if you start a txn and call commit after execute_array the rows
which were good are still not seen. I amended the test code to do both.
5) I would not mind a warning instead of an error if a lob was truncated or
whatever but in this case some rows were not inserted and one row returned an
Oracle error. Just because something may happen which warrants a warning does
not mean anything which happens must be a warning.
6) execute_array in array context is supposed to return the sum of all rows
affected or -1 if not known - it returns undef here and yet 4 (correctly) if
all 4 rows were executed. Ignore the bit in the DBI docs which says "Some
drivers may not yet support list context" as DBD::Oracle was changed years ago.
Here is the code:
use DBI;
use strict;
use Data::Dumper;
my ($captured_warning, $captured_error);
$SIG{__WARN__} = sub { $captured_warning = @_[0]; warn @_[0]};
sub error_handler
{
print "Error Handler called\n";
print Data::Dumper->Dump([\@_], [qw(captured_error_in_handler)]);
my ($msg, $handle, $val) = @_;
$captured_error = "$msg";
0; # pass errors on
}
my $dbh = DBI->connect(
'DBI:Oracle:host=betoracle.easysoft.local;sid=devel', 'bet', 'b3t',
{ RaiseError => 1, PrintError => 0,
#PrintWarn => 0,
HandleError => \&error_handler
});
do_it($dbh, 0);
do_it($dbh, 1);
my $dbh = DBI->connect(
'DBI:ODBC:DSN=baugi', 'sa', 'easysoft',
{ RaiseError => 1, PrintError => 0,
#PrintWarn => 0,
HandleError => \&error_handler
});
do_it($dbh);
sub do_it {
my ($dbh, $txn) = @_;
$dbh->begin_work if $txn;
$captured_error = undef;
$captured_warning = undef;
print "********** do_it **********\n";
print "AutoCommit = ", $dbh->{AutoCommit}, "\n";
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, $sts, $total_affected);
$sts = 99;
eval {
($sts, $total_affected) = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
1;
};
if ($@) {
print "execute_array raised error: $@\n";
}
print "execute_array = ", DBI::neat($sts), "\n";
print "total affected rows = ", DBI::neat($total_affected), "\n";
print "Error from execute_array - errstr=", $sth->errstr, ", err=",
$sth->err, ", state=", DBI::neat($sth->state), "\n";
print Data::Dumper->Dump([\@tuple_status], [qw(tuple_status)]), "\n";
print "Error captured in handler: ",
DBI::neat($captured_error), "\n";
print "Warning captured in SIGWARN handler: ",
DBI::neat($captured_warning), "\n";
$dbh->commit if $txn;
my $res = $dbh->selectall_arrayref(q/select * from mytest/);
print Data::Dumper->Dump([$res], ['select * from mytest']), "\n";
}
and the output for DBD::Oracle (AutoCommit on):
********** do_it **********
AutoCommit = 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
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array.pl line 61.
execute_array = undef
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (BET.SYS_C0096257) violated
(DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: '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
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array.pl line 61.
'
$select * from mytest = [
[
'1',
'onetwothree '
]
];
and the output for DBD::Oracle (explicit commit):
********** do_it **********
AutoCommit = 0
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
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array.pl line 61.
execute_array = undef
total affected rows = undef
Error from execute_array - errstr=ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute), err=0, state=''
$tuple_status = [
-1,
[
1,
'ORA-00001: unique constraint (BET.SYS_C0096258) violated
(DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: '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
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array.pl line 61.
'
$select * from mytest = [
[
'1',
'onetwothree '
]
];
and DBI's execute_array with DBD::ODBC for comparison:
********** do_it **********
AutoCommit = 1
Error Handler called
$captured_error_in_handler = [
'DBD::ODBC::st execute_array failed: executing 4
generated 1 errors',
bless( {}, 'DBI::st' ),
undef
];
execute_array raised error: DBD::ODBC::st execute_array failed: executing 4
generated 1 errors at
/home/martin/svn/dbd-odbc/trunk/rt_data/execute_array/execute_array.pl line 61.
execute_array = 99
total affected rows = undef
Error from execute_array - errstr=executing 4 generated 1 errors,
err=2000000000, state='S1000'
$tuple_status = [
1,
[
1,
'[unixODBC][Easysoft][SQL Server Driver 10.0][SQL
Server]Violation of PRIMARY KEY constraint \'PK__mytest__3BD0198E247D636F\'.
Cannot insert duplicate key in object \'dbo.mytest\'. (SQL-23000) [state was
23000 now 01000]
[unixODBC][Easysoft][SQL Server Driver 10.0][SQL Server]The statement has been
terminated. (SQL-01000)',
'01000'
],
1,
1
];
Error captured in handler: 'DBD::ODBC::st execute_array failed: executing 4
generated 1 errors'
Warning captured in SIGWARN handler: undef
$select * from mytest = [
[
'1',
'onetwothree '
],
[
'51',
'fiftyone '
],
[
'52',
'fiftythree '
],
[
'53',
'one '
]
];
I don't know how to illustrate this any better.
BTW, if you change oci_mode = OCI_BATCH_ERRORS to oci_mode = 0 it seems to work
nearer to DBI's execute_array but only commits the rows up to the failure and
is missing the element in arraystatus for the failed row. Note that I'm not
saying changing the oci_mode is the right answer, I'm simply illustrating a
difference.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
