On 02/02/2011 7:15 AM, Tim Bunce wrote:
On Tue, Feb 01, 2011 at 09:02:26PM +0000, Martin J. Evans wrote:
On 01/02/2011 20:50, Tim Bunce wrote:
On Tue, Feb 01, 2011 at 10:58:14AM -0500, John Scoles wrote:
My only concern is when it does error (no matter what the setting of
AutoCommit) you always get unef;
Umm, yes. Returning undef (or an empty list) if there was any kind of
error causes useful information (the total row count) to be discarded.
I don't see why when called in list context. Just because
execute_array partially succeeded (or failured) only means undef
needs to be returned for the first scalar and does not affect the
rows affected.
True for the total row count. I was thinking of the RaiseError case,
I think. Also, by that definition, this wouldn't work:
unless (($tuples, $rows) = $sth->execute_array(...)) {
...handle error...
}
That's not a big deal though. (Returning undef for the tuple count
isn't a big problem because you can determine that from the length of
ArrayTupleStatus.)
This brings us back to the question of whether a failure of a single
tuple should cause execute_array() itself to return an error.
ODBC doesn't do that. It treats that situation as SUCCESS_WITH_INFO
and that does seem very reasonable.
The funny thing is being an big user of ODBC, I don't (and others in
this thread expressed the same belief).
I'm (wobbling) on the fence on that one. I can see it both ways,
though I'm more sympathetic to your viewpoint :)
I did a few tests and OCI (Oracle) can do it both ways, It will always
return 'SUCCESS_WITH_INFO' if there
is an error in your exe it is all in the manner in which mode you call
the exe and how you handle the result.
You can stop at the first error or batch the errors.
Perhaps we should go down that road
BatchErrors=>1
no errors raised but the $tuples will be smaller than length
(@tuple_status) so we know there was an error or the list context can be
increased to three
($tuples,$rows,$errors)
all of the above we can get from present code.
BatchErrors=>0 (DEFAULT)
stops on first error with error
Commits are handled as they are now either commits after the exe
"autocommit" or you have to do it yourself after. Would not matter what
the error condition is it should commit any good ones.
For me, if I use execute_array (with AutoCommit on or not) my
principle issue is I want to know if anything failed so I do:
$dbh->{RaiseError} = 1;
my $ret = eval {
execute_array(...)
};
do_something_on_error() if ($@ || !$ret); # amended for DBD::Oracle behavior
Ok. How would you get the total row count when using RaiseError?
Why put the onus on people who cannot accept some tuples to fail to
check the ArrayTupleStatus instead of put it on those who can accept
some to fail to examine ArrayTupleStatus? My feeling is that it is
very serious to ignore a partial failure and so the default should
be to ensure you know about it with RaiseError. For those people who
can accept a tuple to fail, they can look at ArrayTupleStatus and
still commit the changes.
I agree.
but I can live with that.
Perhaps we could default to the SUCCESS_WITH_INFO model and add an
attribute for people to use if they want tutple errors to cause
execute_array() itself to return an error.
We can debate the name later, but for now let's call it:
ArrayPromoteTupleError => 1
which defaults to 1, I hope.
Probably ;-)
Incidentally, I just blogged about this a few hours ago as I think
it is a serious problem to ignore potential failures. Add that to
the fact that some versions of Oracle database (broken) return
success with info but don't then commit the ok tuples.
I have been looking into that for some time and it seems it it is very
version/patch/OS specific to 11g. Might even be a bug on the instant
client. I have yet to play with an 11g combination that exhibits the
bug. And oracle says there is no bug.
Cheers
Yes, that's clearly a serious bug.
Tim.
