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?

In reply to DBD::MYSQL rows updated by zacc

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.