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
