| Subject: | incorrect return values from execute_array method and ArrayTupleStatus |
Using Perl 5.8.8, DBI 1.52 and DBD::Oracle 1.18a.
uname:
Linux xxx.easysoft.local 2.6.16-1.2122_FC5smp #1 SMP Sun May 21 15:18:32
EDT 2006 i686 i686 i386 GNU/Linux
According to the DBI pod (and discussed with Tim on dbi-users list)
execute_array should return either undef (if there were errors) or the
number of tuples executed. Also, ArrayTupleStatus is incorrectly set.
Here are some examples of what is right and wrong:
1. insert into table values (?,?) where all values are valid and 3 rows
of values
returns: 3
(correct as 3 tuples executed)
ArrayTupleStatus = (-1, -1, -1)
(incorrect - should be (1,1,1) as DBD::Oracle does know a row was
inserted each time as evidenced by execute itself which returns
rows affected correctly)
2. insert into table values (?,?) where there are 2 rows of data but
second one is invalid
returns: undef
(correct)
ArrayTupleStatus = (-1, [1772, 'ORA-01722.......'])
(incorrect as this should be (1, [1772, 'ORA-01722.......'])
probably same issue as 1).
3. update table with one row where the update does not affect any rows
returns: 0
(incorrect - should be 1 for 1 tuple executed)
ArrayTupleStatus = (-1)
(incorrect - should be (0) for no rows affected)
The following snippets of code were used to demonstrate this:
1.
==
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');
print "execute for one row returned ", $sth->execute, "\n";
$sth->bind_param_array(1, [51,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree']);
my @tuple_status;
my $inserted = $sth->execute_array( { ArrayTupleStatus => \@tuple_status
} );
print "execute_array returns ", neat($inserted), "\n";
if (!$inserted) {
print "Error from execute_array - (" . $sth->errstr . ") " .
$inserted . "\n"
} else {
print " and it is 0E0\n" if ($inserted eq "0E0");
}
print Dumper(\@tuple_status),"\n";
2.
==
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 = $dbh->prepare(q/update mytest set b = ? where a = ?/);
$sth->bind_param_array(2, [1, 'xxx']);
$sth->bind_param_array(1, ['one', 'two']);
my @tuple_status;
my $inserted = $sth->execute_array( { ArrayTupleStatus => \@tuple_status
} );
print "execute_array returns ", neat($inserted), "\n";
if (!$inserted) {
print "Error from execute_array - " . $sth->errstr . " " .
$inserted . "\n"
} else {
print " and it is 0E0\n" if ($inserted eq "0E0");
}
print Dumper(\@tuple_status),"\n";
3.
==
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 = $dbh->prepare(q/update mytest set b = ? where a = ?/);
$sth->bind_param_array(2, [2]);
$sth->bind_param_array(1, ['two']);
my @tuple_status;
my $inserted = $sth->execute_array( { ArrayTupleStatus => \@tuple_status
} );
print "execute_array returns ", neat($inserted), "\n";
if (!$inserted) {
print "Error from execute_array - (" . $sth->errstr . ") " .
$inserted . "\n"
} else {
print " and it is 0E0\n" if ($inserted eq "0E0");
}
print Dumper(\@tuple_status),"\n";