Re: Clarification sought on execute_array
On 18/01/2011 19:24, John Scoles wrote:
On 18/01/2011 9:47 AM, Martin J. Evans wrote:
On 18/01/11 14:11, John Scoles wrote:
On 18/01/2011 8:35 AM, Martin J. Evans wrote:
John,
I slightly reformatted you reply as you added comments on the end
of lines I wrote which made it look like I said them.
On 18/01/11 12:40, John Scoles wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the
err and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on
the first error but I explained since execute_array may send the
entire batch to the server and it is server dependent when it stops
this is beyond definition by DBI. Never the less the following
script seems to show some large differences between DBI's
execute_array and DBD::Oracle's:
The first question is do any other DBIs utilize a native
array_execute??
Anyway
Not that I know of but if DBD::Oracle does not match what happens
with a DBI execute_array then that is a problem for anyone writing
DBD neutral code and it should be clearly documented so you can
write DBD neutral code.
Well lets go back to DBI and see what it says
When called in scalar context the execute_array() method returns the
number of tuples executed, or |undef| if an error occurred.
Like
execute(), a successful execute_array() always returns true
regardless of the number of tuples executed, even if it's zero.
I think you have misread this bit. It means (like execute) it is
not an error to do nothing or something like;
update mytable set mycol = 1 where mycol = 2
where no mycol = 2 i.e., it will return success even though no
change occurred.
I don't think it means execute_array always returns success no
matter what happens just because it is a batch.
If
there were any errors the ArrayTupleStatus array can be used to
discover which tuples failed and with what errors.
In DBD::Oracle you will never get 'undef' returned as the execute
will always be successful even though all of your tuples may fail.
and yet, you do get an undef back in my example so you we already
have a contradiction.
See:
Error from execute_array - ORA-24381: error(s) in array DML (DBD
SUCCESS_WITH_INFO: OCIStmtExecute),0
which is output because execute_array returned undef!
my (@tuple_status, $inserted);
$inserted = 99;
eval {
$inserted = $sth->execute_array(
{ ArrayTupleStatus => \@tuple_status } );
};
if ($@) {
print "Exception: $@\n";
}
print "inserted = ", DBI::neat($inserted), "\n";
print "Error from execute_array - " . $sth->errstr . ",",
$sth->err ."\n"
if (!$inserted);
outputs (for Oracle):
The following is due to PrintWarn => 1
DBD::Oracle::st execute_array warning: ORA-24381: error(s) in
array DML (DBD SUCCESS_WITH_INFO: OCIStmtExecute) [for Statement
"insert into mytest values (?,?)"] at
rt_data/execute_array/execute_array.pl line 44.
The following is undef from execute_array:
inserted = undef
The following is because execute_array returned undef:
Error from execute_array - ORA-24381: error(s) in array DML
(DBD SUCCESS_WITH_INFO: OCIStmtExecute),0
Note the errstr value is set but not err (0) - that cannot be right
surely.
So It agrees with the first para and works in scalar.
Funnily enough, it does agree with the first paragraph since an
error occurred and it returned undef (unlike you reasoning) BUT it
only set the error state to a warning and did not set "err".
I have no issue it is a batch and executed in the server as one
operation but DBD::Oracle does know something failed as it stands.
To get the extra info that comes out in a non-DBD specific
array_execute we would have to build in an extra iteration over the
results to give a count of the Failed/Pass. As some of my customers
use this with batch loads of 5meg plus of inserts the iteration may
take some time and sort of defeat the purpose of a quick way to do
bulk inserts.
but John, DBD::Oracle already knows an error occurred.
I think (you will have to ask Tim to verify) that the Idea behind
array_execute is a 'Batch' processor. ie send a Batch, to the server
then figure out what to with what is returned.
so with
a) even though RaiseError was set, no error was raised although a
warning was.
JS replied:
We know there was a problem so we have to fail the batch or at
lease report
on it is what the warning is telling us
I'm in danger of repeating myself - an error did occur, DBD::Oracle
knows this but it was not raised as an error.
b) execute_array returned undef (correct)
JS replied:
Well at least that is a good thing
You said "In DBD::Oracle you will never
Re: Clarification sought on execute_array
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 un
Re: Clarification sought on execute_array
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
Re: Clarification sought on execute_array
On 18/01/2011 9:47 AM, Martin J. Evans wrote:
Just as a Side note seems execute_array was added well after the first
DBI spec was written.
DBI 1.24,4th June 2002 seems to be the correct date rather a late
addition.
Cheers
John
On 18/01/11 14:11, John Scoles wrote:
On 18/01/2011 8:35 AM, Martin J. Evans wrote:
John,
I slightly reformatted you reply as you added comments on the end of lines I
wrote which made it look like I said them.
On 18/01/11 12:40, John Scoles wrote:
On 17/01/2011 3:34 PM, Martin J. Evans wrote:
There appear to be differences between DBDs which do not handle
execute_array (so DBI does it for them) and DBDs which do handle
execute_array (e.g., DBD::Oracle). The main ones discussed on
#dbix-class which I investigated are whether the driver sets the
err and errstr or even raises an error. Some of the guys writing
DBIx::Class think execute_array should raise and error and fail on
the first error but I explained since execute_array may send the
entire batch to the server and it is server dependent when it stops
this is beyond definition by DBI. Never the less the following
script seems to show some large differences between DBI's
execute_array and DBD::Oracle's:
The first question is do any other DBIs utilize a native
array_execute??
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.
Re: Clarification sought on execute_array
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 un
Re: Clarification sought on execute_array
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
>>
Re: Clarification sought on execute_array
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 r
Re: Clarification sought on execute_array
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 rout
Re: Clarification sought on execute_array
On 18/01/2011 7:52 AM, H.Merijn Brand wrote:
On Tue, 18 Jan 2011 07:40:25 -0500, 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??
DBD::Unify and DBD::CSV do not
Looking more and more that only DBD::Oracle has a native exe_array
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? :)
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
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
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);
$
Re: Clarification sought on execute_array
On Tue, 18 Jan 2011 07:40:25 -0500, 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??
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? :)
> 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
>
> > 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_
Re: Clarification sought on execute_array
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
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
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_
Re: DBD::Oracle - credentials
On Fri, Jan 14, 2011 at 04:45:52PM +0100, H.Merijn Brand wrote: > > Yes, the *DBD::Oracle* README. I know, I found it there, but I was more > looking for guides from DBI. Does DBI document that DBU_USER/DBI_PASS > would somehow overrule other (default) settings? DBI_USER and DBI_PASS are applied as defaults in DBI->connect() so, if set, they'll be applied before the driver gets a chance to consider what to do. It's the $drh->default_user() method that looks up the env vars. That method isn't documented, which is a bug in itself. > I think the DBD::Oracle README does exactly what it said. So it is not > wrong. Agreed. There may be room for practical improvements if there are clear problems with the current behavior. Tim.
