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