Clarification sought on execute_array
Martin J. Evans
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:
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
John Scoles
> 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
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. 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.
So It agrees with the first para and works in scalar.
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.
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.
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
b) execute_array returned undef (correct)
Well at least that is a good thing
c) errstr is set but err is not (0)
d) the HandleError routine was not called - due to (a)?
Which is correct as is did do exactly what was expected. ie 'execute a
bath and report back'
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
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 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.
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
Cheers
John
H.Merijn Brand
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??
DBD::Unify and DBD::CSV do not
> Anyway
>
> 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. 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.
>
> So It agrees with the first para and works in scalar.
>
> 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.
>
> 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.
> We know there was a problem so we have to fail the batch or at
> least report on it is what the warning is telling us
>
> b) execute_array returned undef (correct)
> Well at least that is a good thing
>
> c) errstr is set but err is not (0)
>
> d) the HandleError routine was not called - due to (a)?
> Which is correct as is did do exactly what was expected. ie
> 'execute a bath and report back'
^^^^
The batch will take long enough to have a comfortable bath? :)
--
H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23 and 11.31, OpenSuSE 10.1, 11.0 .. 11.3 and AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/
http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
John Scoles
> On Tue, 18 Jan 2011 07:40:25 -0500, John Scoles<sco...@pythian.com>
> 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??
> DBD::Unify and DBD::CSV do not
Looking more and more that only DBD::Oracle has a native exe_array
Dislexia stick again?
You see we do not believe in Dog!
As a side point one client who uses the exe_array takes 2h and 45m to
run so just enough time for a good bath :). To put it in perspective it
use to take 19~25 hours (DBD::Oracle 1.17) and she now has almost twice
the number of records than at the 29 hour mark.
cheers
Martin J. Evans
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?
>
> 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".
> 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.
> Cheers John
Martin
John Scoles
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.
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
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).
If the above does send out an error than which one???? 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 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.
Lets see if there are any DBDs that do implement their own.
>> 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??
Cheers
John
Martin J. Evans
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
John Scoles
'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
John Scoles
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
Martin J. Evans
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
John Scoles
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.
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.
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.
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.
Cheers
John Scoles
Martin J. Evans
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
David Nicol
> 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.
An extra pass is not required. Counts can be maintained as they occur
(or maintained, then summed together as workers report their
completion status, when parallel.)
John Scoles
As usual very sage advice Tim.
> I'm sorry to arrive here late. Seems I've been missing all the fun!
>
> Some observations:
>
> - I think it's reasonable for execute_array() and execute_for_fetch()
> to return an error (ie err() true and so trigger RaiseError etc.)
> if execution of any of the tuples encountered an error.
It looking more and more that this should of been the case in
DBD::Oracle still have some reading to do on it though.
> - That'll need a spec clarification and a clear warning in Changes.
>
> - We should avoid breaking existing batch apps that use DBD::Oracle.
>
> - I'm hopeful that the above change wouldn't. (John?)
>
> - We should review other database APIs that provide batch execution
> in order to spec a reasonable common subset behaviour for the DBI.
I have yet to find one but I haven't look very hard
> - Clearly we should bring DBD::Oracle, the DBI default behaviour, and the
> DBI spec into agreement with each other.
>
The question being is it the default behaviour from 1999 Oracle 8 or
today's 11g ? ;)
> - We *really* need a way to share tests across drivers.
> Perhaps something like a separate DBI::TestSuite distro that the DBI
> and drivers could have as a prerequisite. That would contain tests in
> modules. The DBI and DBDs would have a test file that uses the
> DBI::TestSuite module and calls a function that runs the tests.
> This issue could provide the first test.
>
> Tim.
Martin J. Evans
> I'm sorry to arrive here late. Seems I've been missing all the fun!
>
been a lot of fun ;-)
> Some observations:
>
> - I think it's reasonable for execute_array() and execute_for_fetch()
> to return an error (ie err() true and so trigger RaiseError etc.)
> if execution of any of the tuples encountered an error.
>
The way I see it is that if some of the rows in the batch end up in the
table and some don't I'd expect a warning. However, if 1 or more rows
fail and no rows end up in the table I don't see any success so I'd
expect an error. In my example code, 1 row fails but no rows end up
successful so it is the latter.
It may be a change/bug in the Oracle libraries as I thought when you
execute a batch all the rows are executed no matter if some fail and all
successful ones end up in the table.
> - That'll need a spec clarification and a clear warning in Changes.
>
> - We should avoid breaking existing batch apps that use DBD::Oracle.
>
Agreed, but we've not got any working examples of anyone using
execute_array other than the one which started this off (and mine) which
was DBIx::Class and that was broken by DBD::Oracle/Oracle's current
behaviour. The big problem with the DBIx::Class code was checking
$sth->err which was 0 in this case because 0 indicates a warning.
However, DBD::Oracle contradicted itself since execute_array returned
undef (error) but then set a warning on err.
> - I'm hopeful that the above change wouldn't. (John?)
>
So am I. If we could get the examples John has seen via his DBD::Oracle
maintainership or via Pythian customers I would be happy to create test
cases. We are running blind at the moment as we've not got those solid
examples of supposedly working code.
> - We should review other database APIs that provide batch execution
> in order to spec a reasonable common subset behaviour for the DBI.
>
Obviously ODBC and JDBC do batched statements. I might provide a JDBC
example but for now I've done an ODBC example (slightly more familiar to
me) - see below.
> - Clearly we should bring DBD::Oracle, the DBI default behaviour, and the
> DBI spec into agreement with each other.
>
Exactly. This is really my main point. As it stands (and given it is not
a bug in Oracle) I see a nightmare for anyone trying to use
execute_array in a database neutral way as with DBI, all successful rows
are inserted and we know which ones failed and with DBD::Oracle no rows
are inserted (including the ones where there is no error) and you cannot
commit the good ones and it is difficult to know (if not impossible)
what really happened. This is not a dig at anyone in particular as I
added the array context execute_array to DBI/DBD::Oracle but this is
also why I suspect something has changed in DBD::Oracle/Oracle.
> - We *really* need a way to share tests across drivers.
> Perhaps something like a separate DBI::TestSuite distro that the DBI
> and drivers could have as a prerequisite. That would contain tests in
> modules. The DBI and DBDs would have a test file that uses the
> DBI::TestSuite module and calls a function that runs the tests.
> This issue could provide the first test.
>
> Tim.
I agree and I seem to remember a project to do something like this - was
it perhaps a google summer of code suggestion? But it is pretty
difficult and I think that puts a lot of people off. I briefly looked at
Test::Database so I could get more realistic test results for DBD::ODBC
but I ran in to a load of problems as Test::Database needs some DBD
methods writing and expects to be able to create a database and in ODBC
(via dozens of ODBC drivers) there is not single way to do this. The
gain was just not worth the pain for me. I'd be happy to help someone do
this but only in a minor way as right now I cannot find the time to
satisfy even half of my OS commitments (as an example, I REALLY want to
be able to set handle attributes on methods in DBI [post from a week
back] but I just cannot find time to do it - something else is always
cropping up).
Attached is a very rough and ready bit of C code (with little error
checking) that does batch inserts. You run it with something like:
./a.out 'DSN=mydsn;UID=username;PWD=password'
and it does batch inserts into a table called xtest that is defined as:
create table xtest(ky integer primary key, txt varchar(20))
It has 4 tests:
1. insert a batch successfully reading the parameter status array
(ArrayTupleStatus) to see what worked (autocommit)
2. insert a batch where 2 rows cannot be inserted because of a duplicate
key and with a parameter status array (autocommit)
3. insert a batch where 2 rows cannot be inserted because of a duplicate
key and without a parameter status array (autocommit)
4. insert a batch where 2 rows cannot be inserted with an explicit txn
and with a parameter status array
The code was run against the Easysoft ODBC Driver for MS SQL Server but
the same results are obtained when using the MS SQL Server driver on
Windows. No other ODBC attributes were changed (other than the ones
mentioned).
What it tells us is:
o SQL_SUCCESS is always returned when all the rows are inserted
o SQL_SUCCESS_WITH_INFO is returned if some of the rows were successful
but some were not
o it does not matter whether we provide a parameter status array
(ArrayTupleStatus) or not - the 2 results above stand i.e. even if the
ODBC driver cannot tell you which ones failed (because you did not give
a parameter status array) it still does the successful rows and only
returns SQL_SUCCESS_WITH_INFO if some failed.
o AutoCommit makes no difference - i.e., if auto commit is on or off the
end result is the same IF we commit afterwards.
The output from running the code is below. I apologise for the length of
the C code but this is just another example of what you can do in C code
you can do in 1/10 (or thereabouts) of the code in Perl.
$ ./a.out 'DSN=baugi;UID=sa;PWD=easysoft'
Successful batch AUTOCOMMIT with PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameter Status Array Ptr
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SUCCESS
RowCount=10
Param Status Array 0 = 0
Param Status Array 1 = 0
Param Status Array 2 = 0
Param Status Array 3 = 0
Param Status Array 4 = 0
Param Status Array 5 = 0
Param Status Array 6 = 0
Param Status Array 7 = 0
Param Status Array 8 = 0
Param Status Array 9 = 0
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
2, this is row 2
3, this is row 3
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
Partially successful batch AUTOCOMMIT with PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameter Status Array Ptr
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SQL_SUCCESS_WITH_INFO
** Error from SQLExecute **
4 diagnostics found
** error: 23000:1:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 23000:2:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 01000:3:3621:[Easysoft][SQL Server Driver 10.0][SQL Server]The
stateme
nt has been terminated. **
** error: 01000:4:3621:[Easysoft][SQL Server Driver 10.0][SQL Server]The
stateme
nt has been terminated. **
** RowCount=8, expected 10 **
Param Status Array 0 = 0
Param Status Array 1 = 0
** Row 3 not executed, status=5**
** Row 4 not executed, status=5**
Param Status Array 4 = 0
Param Status Array 5 = 0
Param Status Array 6 = 0
Param Status Array 7 = 0
Param Status Array 8 = 0
Param Status Array 9 = 0
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
Partially successful batch AUTOCOMMIT without PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SQL_SUCCESS_WITH_INFO
** Error from SQLExecute **
4 diagnostics found
** error: 23000:1:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 23000:2:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 01000:3:3621:[Easysoft][SQL Server Driver 10.0][SQL Server]The
stateme
nt has been terminated. **
** error: 01000:4:3621:[Easysoft][SQL Server Driver 10.0][SQL Server]The
stateme
nt has been terminated. **
** RowCount=8, expected 10 **
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
Partially successful batch no AUTOCOMMIT WITH PARAMSTATUSARRAY
"delete from "xtest""
Setting bind by column
Setting Parameter Status Array Ptr
Setting Parameters Processed Ptr
Setting PARAMSETSIZE to 10
"insert into "xtest" (ky,txt) values(?,?)"
Inserting rows into table
SQLExecute=SQL_SUCCESS_WITH_INFO
** Error from SQLExecute **
4 diagnostics found
** error: 23000:1:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 23000:2:2627:[Easysoft][SQL Server Driver 10.0][SQL
Server]Violation o
f PRIMARY KEY constraint 'PK__xtest__3213D96D46D27B73'. Cannot insert
duplicate
key in object 'dbo.xtest'. **
** error: 01000:3:3621:[Easysoft][SQL Server Driver 10.0][SQL Server]The
stateme
nt has been terminated. **
** error: 01000:4:3621:[Easysoft][SQL Server Driver 10.0][SQL Server]The
stateme
nt has been terminated. **
** RowCount=8, expected 10 **
Param Status Array 0 = 0
Param Status Array 1 = 0
** Row 3 not executed, status=5**
** Row 4 not executed, status=5**
Param Status Array 4 = 0
Param Status Array 5 = 0
Param Status Array 6 = 0
Param Status Array 7 = 0
Param Status Array 8 = 0
Param Status Array 9 = 0
Params processed = 10
.
Resetting parameters
Closing statement
Clearing Parameter Status Array Ptr
Clearing Parameters Processed Ptr
0, this is row 0
1, this is row 1
4, this is row 4
5, this is row 5
6, this is row 6
7, this is row 7
8, this is row 8
9, this is row 9
Dropping Statement
I'm not in a rush to provide a JDBC example as my experience is that it
will be pretty similar - I might if pushed hard.
What is not clear to me is what effect oci_mode = OCI_BATCH_ERRORS is
supposed to have. Also my current Oracle example seems to return a
SUCCESS_WITH_INFO when OCIExecute is called even though no rows are
committed. John seems to have a recollection that you can commit the
successful rows but I cannot duplicate it. We need real example usage of
execute_array for DBD::Oracle which worked as a test case.
Martin
John Scoles
Great stuff Martin I am sure we can clear this up.
One thing I did notice was you never set the 'ora_array_chunk_size'
which is defaulting to 1000
I wonder if running this with this value set to say 2 or 3 will see a
change in behaviour.
No doubt there is some thing awry in there That we will have to get to
the bottom of.
Cheers
John
Martin J. Evans
> On 19/01/2011 5:25 PM, Martin J. Evans wrote:
> Great stuff Martin I am sure we can clear this up.
>
> One thing I did notice was you never set the 'ora_array_chunk_size' which is defaulting to 1000
> I wonder if running this with this value set to say 2 or 3 will see a change in behaviour.
Doesn't seem to make any difference which I'm quite pleased about really since I wouldn't want another factor introduced.
Have you reproduced my result John?
I am using InstantClient 11.2 and Oracle 11.1.0.6.0
> No doubt there is some thing awry in there That we will have to get to the bottom of.
>
> Cheers
> John
I will try to step back a number of DBD::Oracle and Instant Client versions to see if the behaviour changes.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
>> On 19/01/2011 11:47, Tim Bunce wrote:
Charles Jardine
> On Wed, Jan 19, 2011 at 10:25:05PM +0000, Martin J. Evans wrote:
>> On 19/01/2011 11:47, Tim Bunce wrote:
> Umm. I take that back. I suggest we change the spec from:
>
> 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.
> If there were any errors the ArrayTupleStatus array can be used to
> discover which tuples failed and with what errors.
I have always found the above wording confusing. I think I read 'tuple'
as referring to a list of bind variables/values. Someone else might
thing 'tuple' referred to a row in the database.
> to either (new or changed text in capitals):
>
> a: When called in scalar context the execute_array() method returns the
> number of tuples executed INCLUDING ANY TUPLES WHICH FAILED,
> or undef if an error occurred THAT PREVENTED NORMAL OPERATION OF THE
> METHOD. THE FAILURE OF SOME OR ALL TUPLES IS NOT REGARDED AS AN ERROR.
> Like execute(), a successful execute_array() always returns true
> regardless of the number of tuples executed, even if it's zero.
> If there were any errors the ArrayTupleStatus array can be used to
> discover which tuples failed and with what errors.
>
> or
>
> b: When called in scalar context the execute_array() method returns the
> number of tuples executed IF ALL ARE SUCCESSFUL. IF ANY TUPLES
> ENCOUNTER AN ERROR THEN execute_array() RETURNS UNDEF.
> Like execute(), a successful execute_array() always returns true
> regardless of the number of tuples executed, even if it's zero.
> If there were any errors the ArrayTupleStatus array can be used to
> discover which tuples failed and with what errors.
>
> I prefer (a) but (b) is how the DBI's default execute_array() and
> execute_for_fetch() behave, which is obviously significant.
I strongly prefer (b). I am sure that, if I am using RaiseError or
HandleError, I want the exception route to be taken if *any* of the
individual executes fail.
>> The way I see it is that if some of the rows in the batch end up in
>> the table and some don't I'd expect a warning. However, if 1 or more
>> rows fail and no rows end up in the table I don't see any success so
>> I'd expect an error. In my example code, 1 row fails but no rows end
>> up successful so it is the latter.
>
> Shouldn't the number of rows "failing" and the number not ending up in
> the database be the same? Anything else seems like a bug. Or are you
> refering to a batch being aborted early?
execute_array can be used for any non-SELECT DML statement. In general,
a single successful execute can affect any number of rows, including
zero. It is only in the case of a simple INSERT statement (with a
VALUES clause) that there is a direct relationship between the
number of successful executes and the number or rows affected.
In some places in the current thread, the number of successful
executes has been confused with the number of rows affected.
--
Charles Jardine - Computing Service, University of Cambridge
cj...@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
Martin J. Evans
> On Wed, Jan 19, 2011 at 10:25:05PM +0000, Martin J. Evans wrote:
>>>
>>> - I think it's reasonable for execute_array() and
>>> execute_for_fetch() to return an error (ie err() true and so
>>> trigger RaiseError etc.) if execution of any of the tuples
>>> encountered an error.
Stepping back a minute and ignoring what the DBI docs say, what DBI's execute_array and DBD::Oracle's execute_array does right now, what does someone using it want? Take the simple case of a bulk insert of N rows as an example, what could happen:
1 all executed and all succeed
we want a success status and it would not hurt to get a count back (although for what that count is see later)
2 all are executed but some fail
we want to know all were executed but that some failed and we want to be able to identify the failed rows
3 the initial rows are successful but one errors and the remainder are not executed
we want to know which ones were executed and which ones failed and the remainder were never executed. For DBI's execute_array this never happens.
In ODBC, we know almost all of this because:
a) SQLExecute returns SQL_SUCCESS if everything worked and SQL_SUCCESS_WITH_INFO is some failed.
b) the Params Processed Array contains values for each row which may be:
SQL_PARAM_SUCCESS - executed and successful
SQL_PARAM_SUCCESS_WITH_INFO - executed but there are warnings (e.g., data truncated)
SQL_PARAM_ERROR - executed and errored
SQL_PARAM_UNUSED - this set of parameters was never used - perhaps because the DBMS aborted due to an error on a previous set of parameters
SQL_PARAM_DIAG_UNAVAILABLE - information not available per parameter set as the DBMS treats the entire batch as one - never seen this myself.
In ODBC we don't know rows affected per row of parameters as SQLRowCount only returns a total and there is no attribute to use per row of parameters for rows affected.
The count is a tricky one as for execute it is the number of rows affected and not a count of statements executed (I know Oracle does not know this on a per row basis in a batch but some other APIs/DBs might - although not ODBC to my knowledge). When array context was added to DBI and DBD::Oracle the first value returned is the normal $tuples
(rows executed) but the second value is supposed to be the sum of the rows affected for each row in the batch (I added that as I wanted to avoid counting them all up in the ArrayTupleStatus). It appears that even in the DBI version of this if some of the rows in the batch fail the sum of affected rows is returned as undef (oops).
So where are we now with DBI? :
>
> Umm. I take that back. I suggest we change the spec from:
>
> 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. If
> there were any errors the ArrayTupleStatus array can be used to
> discover which tuples failed and with what errors.
Good:
we know if all succeed or some/all fail (from return, N or undef)
we know which rows in the batch failed from ArrayTupleStatus
Because it errors if any row errors we can also use HandleError
Bad:
We don't really know the total number of tuples executed because if some fail execute_array will return undef. We know the ones marked in ArrayTupleStatus as failed were executed and we know the ones up to the last failure were executed but we don't know about the ones after the last failure :-(
> to either (new or changed text in capitals):
>
> a: When called in scalar context the execute_array() method returns
> the number of tuples executed INCLUDING ANY TUPLES WHICH FAILED, or
> undef if an error occurred THAT PREVENTED NORMAL OPERATION OF THE
> METHOD. THE FAILURE OF SOME OR ALL TUPLES IS NOT REGARDED AS AN
> ERROR. Like execute(), a successful execute_array() always returns
> true regardless of the number of tuples executed, even if it's zero.
> If there were any errors the ArrayTupleStatus array can be used to
> discover which tuples failed and with what errors.
Good:
We know how many were executed (including failed ones)
We know which rows in the batch failed from ArrayTupleStatus
Bad:
We don't know if all succeed or some failed without checking ArrayTupleStatus
If some fail the HandleError function is not called
> or
>
> b: When called in scalar context the execute_array() method returns
> the number of tuples executed IF ALL ARE SUCCESSFUL. IF ANY TUPLES
> ENCOUNTER AN ERROR THEN execute_array() RETURNS UNDEF. Like
> execute(), a successful execute_array() always returns true
> regardless of the number of tuples executed, even if it's zero. If
> there were any errors the ArrayTupleStatus array can be used to
> discover which tuples failed and with what errors.
Good:
we know if all succeed or some/all fail (from return, N or undef)
We know which rows in the batch failed from ArrayTupleStatus
Because it errors if any row errors we can also use HandleError.
For DBI's execute_array we know all were executed because that is what it does.
Bad:
For DBD::Oracle's execute_array we don't know which rows were executed if some failed
> I prefer (a) but (b) is how the DBI's default execute_array() and
> execute_for_fetch() behave, which is obviously significant.
I don't like (a) because to find out if all were successful I have to look into ArrayTupleStatus and it does not call HandleError.
I don't mind (b) so long as it is DBI's execute_array as I know all rows are executed. For DBD::Oracle (b) is a problem because we don't know which rows were executed if 1 or more fail.
To me, scalar context needs to tell me if execute_array was 100% successful or not so I can decide what to do and I'd prefer it to error if not 100% successful so HandleError is called. Most people are going to want it all to succeed so it should be obvious it did and if it fails, many people will simply rollback (which they can do without resorting to searching ArrayTupleStatus).
So we are left with those people who might expect to see a row fail and want to do something about it. Well, as it stands with DBI's execute_array they know all are executed and which ones failed - no problem. For DBD::Oracle they are stuck now anyway as they don't know which rows were executed (other than the failed ones and if the driver stopped on the first failure). This is where the array context comes in as we can leave the return value as it is (all N succeeded or undef) and the second value is rows executed :-)
I think DBD::Oracle's execute_array (as shown by my test script - which could be a new Oracle or DBD::Oracle bug) is totally flawed and hugely dangerous as code typically does this:
RaiseError is set
execute_array - oops never raises an error EVER even if some rows fail!
RaiseError is set (as above, totally meaningless)
my $ret;
eval {
$ret = execute_array;
1;
}
die "error" if ($@ or !$ret); # ok will die if total error or partial error
but you only know the error rows and not which ones succeeded
DBIx::Class expected an error Raised (which did not happen) but also their code looked at the value of err and that is never set with DBD::Oracle - oops.
>> The way I see it is that if some of the rows in the batch end up
>> in the table and some don't I'd expect a warning. However, if 1 or
>> more rows fail and no rows end up in the table I don't see any
>> success so I'd expect an error. In my example code, 1 row fails but
>> no rows end up successful so it is the latter.
>
> Shouldn't the number of rows "failing" and the number not ending up
> in the database be the same? Anything else seems like a bug. Or are
> you refering to a batch being aborted early?
you'd expect so but:
From my current test script, they either all work (and appear in the table) or some fail and none end up in the database. It is not even aborting on the first failure as my error row is row 2 and row 1 doesn't appear.
> Regarding the batch being aborted early, how about this new spec?:
>
> Some databases may abort the batch early after one or more errors. In
> which case the number of elements in ArrayTupleStatus, would be less
> than expected.
Ah, interesting, didn't think of that. I think I still prefer array context to give you state and executed but I'd have no problem with this as well. What I don't want to have to do is count elements in ArrayTupleStatus or examine them all to work out if everything succeeded.
> (That's assuming we go with (b) above. If (a) then execute_array()
> would return the reduced tuple count.)
See above for my arguments for (b) with a twist.
>> It may be a change/bug in the Oracle libraries as I thought when
>> you execute a batch all the rows are executed no matter if some
>> fail and all successful ones end up in the table.
>
> I wonder if we could run an older DBD::Oracle against an Oracle 9
> (say) database to recreate the (presumably) original behaviour.
I've only got Oracle 11 and may be a 10 around somewhere but I will try stepping back DBD::Oracle and oracle instant client.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
>>> - That'll need a spec clarification and a clear warning in
>>> Changes.
>>>
>>> - We should avoid breaking existing batch apps that use
>>> DBD::Oracle.
>
>>> - I'm hopeful that the above change wouldn't. (John?)
>>>
>> So am I. If we could get the examples John has seen via his
>> DBD::Oracle maintainership or via Pythian customers I would be
>> happy to create test cases. We are running blind at the moment as
>> we've not got those solid examples of supposedly working code.
>
> Agreed. John, can you get some working examples so we can see what
> error handling is used and what the impact of any changes above would
> be?
>
>>> - We should review other database APIs that provide batch
>>> execution in order to spec a reasonable common subset behaviour
>>> for the DBI.
>>>
>> Obviously ODBC and JDBC do batched statements. I might provide a
>> JDBC example but for now I've done an ODBC example (slightly more
>> familiar to me) - see below.
>
> ODBC is handy, thanks. I'd also like to see how more native database
> APIs handle these issues. Anyone know what other native database
> APIs support batches?
>
>>> - Clearly we should bring DBD::Oracle, the DBI default behaviour,
>>> and the DBI spec into agreement with each other.
>>>
>> Exactly. This is really my main point. As it stands (and given it
>> is not a bug in Oracle) I see a nightmare for anyone trying to use
>> execute_array in a database neutral way as with DBI, all
>> successful rows are inserted and we know which ones failed and with
>> DBD::Oracle no rows are inserted (including the ones where there is
>> no error) and you cannot commit the good ones and it is difficult
>> to know (if not impossible) what really happened. This is not a dig
>> at anyone in particular as I added the array context execute_array
>> to DBI/DBD::Oracle but this is also why I suspect something has
>> changed in DBD::Oracle/Oracle.
>
> Not being able to tell what happened is clearly a bug in
> DBD::Oracle.
>
>>> - We *really* need a way to share tests across drivers. Perhaps
>>> something like a separate DBI::TestSuite distro that the DBI and
>>> drivers could have as a prerequisite. That would contain tests
>>> in modules. The DBI and DBDs would have a test file that uses
>>> the DBI::TestSuite module and calls a function that runs the
>>> tests. This issue could provide the first test.
>>>
>>> Tim.
>>
>> I agree and I seem to remember a project to do something like this
>> - was it perhaps a google summer of code suggestion? But it is
>> pretty difficult and I think that puts a lot of people off. I
>> briefly looked at Test::Database so I could get more realistic test
>> results for DBD::ODBC but I ran in to a load of problems as
>> Test::Database needs some DBD methods writing and expects to be
>> able to create a database and in ODBC (via dozens of ODBC drivers)
>> there is not single way to do this. The gain was just not worth the
>> pain for me. I'd be happy to help someone do this but only in a
>> minor way as right now I cannot find the time to satisfy even half
>> of my OS commitments (as an example, I REALLY want to be able to
>> set handle attributes on methods in DBI [post from a week back] but
>> I just cannot find time to do it - something else is always
>> cropping up).
>
> Having Test::Database know how to work with only a very small set of
> common ODBC drivers would still be a big help.
>
>> It has 4 tests:
>>
>> 1. insert a batch successfully reading the parameter status array
>> (ArrayTupleStatus) to see what worked (autocommit) 2. insert a
>> batch where 2 rows cannot be inserted because of a duplicate key
>> and with a parameter status array (autocommit) 3. insert a batch
>> where 2 rows cannot be inserted because of a duplicate key and
>> without a parameter status array (autocommit) 4. insert a batch
>> where 2 rows cannot be inserted with an explicit txn and with a
>> parameter status array
>
>> What it tells us is:
>>
>> o SQL_SUCCESS is always returned when all the rows are inserted o
>> SQL_SUCCESS_WITH_INFO is returned if some of the rows were
>> successful but some were not
>
> Which corresponds with (a) above, but doesn't match existing DBI
> default behaviour (b).
>
>> o it does not matter whether we provide a parameter status array
>> (ArrayTupleStatus) or not - the 2 results above stand i.e. even if
>> the ODBC driver cannot tell you which ones failed (because you did
>> not give a parameter status array) it still does the successful
>> rows and only returns SQL_SUCCESS_WITH_INFO if some failed. o
>> AutoCommit makes no difference - i.e., if auto commit is on or off
>> the end result is the same IF we commit afterwards.
>
> Tim.
Martin J. Evans
> Great stuff Martin I am sure we can clear this up.
>
> One thing I did notice was you never set the 'ora_array_chunk_size' which is defaulting to 1000
>
> I wonder if running this with this value set to say 2 or 3 will see a change in behaviour.
>
> No doubt there is some thing awry in there That we will have to get to the bottom of.
>
> Cheers
> John
Just to jog memories.
John, you document a bug in 11g and execute_array in your blog:
http://www.pythian.com/news/1125/bug-in-dbdoracles-execute_array-with-11g/
"If you tell DBD::Oracle to autocommit, it seems that in 11g this commit will not take place when an error occurs during the processing of one of the tuples that you passed into execute_array"
"The workaround is to set AutoCommit => 0 and then use $dbh-->commit() after the statement." which does not work for me by the way.
and a link to the forum you sought advice on is here http://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832
There is no answer.
Martin
John Scoles
> On 20/01/11 12:31, John Scoles wrote:
>> On 19/01/2011 5:25 PM, Martin J. Evans wrote:
>> Great stuff Martin I am sure we can clear this up.
>>
>> One thing I did notice was you never set the 'ora_array_chunk_size' which is defaulting to 1000
>>
>> I wonder if running this with this value set to say 2 or 3 will see a change in behaviour.
>>
>> No doubt there is some thing awry in there That we will have to get to the bottom of.
>>
>> Cheers
>> John
> Just to jog memories.
>
> John, you document a bug in 11g and execute_array in your blog:
>
> http://www.pythian.com/news/1125/bug-in-dbdoracles-execute_array-with-11g/
>
> "If you tell DBD::Oracle to autocommit, it seems that in 11g this commit will not take place when an error occurs during the processing of one of the tuples that you passed into execute_array"
>
> "The workaround is to set AutoCommit => 0 and then use $dbh-->commit() after the statement." which does not work for me by the way.
>
> and a link to the forum you sought advice on is here http://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832
>
> There is no answer.
>
> Martin
>
do with the comparing of two integers. One was coming back something
like 6.9999 while it was looking for 7 when working with 11g on 64bit
hardware and 32bit perl.
I will check tomorrow to see if I can recreate it again. Who knows it
might illustrate a change in expected behaviour between the differing
ORacle platforms and clients?
I remember I have a fix for it someplace at home which was on my list to
look at tomorrow
Cheers
John
Martin J. Evans
> I wonder if we could run an older DBD::Oracle against an Oracle 9 (say)
> database to recreate the (presumably) original behaviour.
I kept my Oracle 11.1.0.6.0 database and tried various combinations of DBD::Oracle (back to 1.19) and instant clients - non work as documented.
I then tried instant client 10.2.0.5.0 to an oracle database 10.2.0.5.0 (with DBD::Oracle 1.19) and it works:
DB Version: 10.2.0.1.0
********** do_it **********
AutoCommit = 1
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 (SYSTEM.SYS_C005544) violated (DBD SUCCESS_WITH_INFO)'
],
-1,
-1
];
Error captured in handler: undef
Warning captured in SIGWARN handler: undef
$select * from mytest = [
[
'1',
'onetwothree '
],
[
'51',
'fiftyone '
],
[
'52',
'fiftythree '
],
[
'53',
'one '
]
];
Notice, the successful rows are committed and the unsuccessful row is not.
After trying various other combinations it seems any version of instant client or DBD::Oracle works so long as Oracle database is less than 11. I cannot say as yet if it is a bug in Oracle or some change DBD::Oracle didn't follow.
So it would appear DBD::Oracle/Oracle has always issued a warning and not an error when a tuple fails. I think this is really dangerous if someone is relying on RaiseError so I've changed the DBD::Oracle we use internally to raise an error.
John Scoles
patch sets show it other do not.I will have an answer for if later today
Did you try it with the full 11 client.
Cheers
John
On Fri, Jan 21, 2011 at 6:02 AM, Martin J. Evans
<martin...@easysoft.com>wrote:
--
The best compliment you could give Pythian for our service is a referral.
Martin J. Evans
> This behaviour is also inconsistent with versions of Oracle 11. Some patch sets show it other do not.I will have an answer for if later today
That I'd love to know.
> Did you try it with the full 11 client.
No - and I've not time today for that - sorry.