Mighty monks, please - I seek wisdom (either that or a kick up the pants to tell me where I've gone wrong).

I'm writing a batch processing routine that updates a database. To keep things simple (ha ha) I don't want to check the database for changes and then call an update, I just want to simply issue an "update".

OK, so far so good - the code works a treat, but I'd also like to capture whether or not the update was actually needed, ie look at the number of rows affected by the update call. Unfortunately, the number of rows returned is always the number of rows CHECKED and not the number of rows UPDATED.


Consider a simple MYSQL table with just two columns, ID (autoincrement) and ACTIVE (integer). The table is populated with 100 rows, with ACTIVE set to 1 for all rows.

The following snippet

my $oh_update = $dbh->prepare("update sample_table set active = ? wher
+e id < 10");

my $rv1  = $oh_update->execute(1);
my $rv11 = $oh_update->rows();
print "1 - Should be none .... $rv1 $rv11\n";

my $rv5 = $oh_update->execute(1);
my $rv51 = $oh_update->rows();
print "5 - Should be none .... $rv5 $rv51\n";

my $rv2 = $oh_update->execute(0);
my $rv21 = $oh_update->rows();
print "2 - Should be nine .... $rv2 $rv21\n";

my $rv3 = $oh_update->execute(1);
my $rv31 = $oh_update->rows();
print "3 - Should be nine .... $rv3 $rv31\n";

my $rv4 = $oh_update->execute(1);
my $rv41 = $oh_update->rows();
print "4 - Should be none .... $rv4 $rv41\n";
[download]

returns

1 - Should be none .... 9 9
5 - Should be none .... 9 9
2 - Should be nine .... 9 9
3 - Should be nine .... 9 9
4 - Should be none .... 9 9
[download]

When I execute the statements in MYSQL query browser, I receive the correct response - 0, then 0, then 9, then 9, then 0 rows updated.

So am I being daft or is something broke inside DBI/DBD?