Wednesday, January 28, 2009

Perl Mysql DBI get deleted row count

Trying to capture the number of deleted rows when executing a delete statement was returning a "0E0". See below for the script details. What I found was that it was DBI's way of saying zero rows were deleted. The thing to know about DBI is that do() needs to return distinct values to distinguish the occurrence of an error from the case that no rows were affected, so it uses “undef” and “0E0”. The value “undef” indicates an error; it evaluates to false in a Boolean context. The string “0E0” indicates that no rows were affected; it evaluates to true in Boolean contexts but is treated as zero in numeric contexts. If do() returned 0 to indicate no rows were affected, that evaluates to false in a Booelan context and would be difficult from an error. You can do the following to display the row count correctly

Error:

my $deleted = $conn->do("delete from employee where year between 1901 and 1910");
print "deleted $deleted rows.\n";

The above would print "deleted 0E0 rows"


Solution:

 
my $deleted = $conn->do("delete from employee where year between 1901 and 1910");
printf "deleted %d rows.\n", $deleted;

(OR)

my $deleted += $conn->do("delete from employee where year between 1901 and 1910");
print "deleted $deleted rows.\n";

No comments:

Post a Comment