Number of rows affected by an update command

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to find out the number of rows affected by an update statement and I'm not getting the right answer. My code loops through lots of data and performs an insert statement for each line. The insert statement contains a where clause which sometimes the data in the line matches and sometimes not. I am counting my inserts performed like so:

$rows_affected = $dbh->do($insertstr, undef, ...parameter)
if ($rows_affected ne "0E0") {
    $updates++;
}
[download]

The value of $updates is less than the number of rows modified when i look in the database directly. I originally tried tested $rows_affected = 0 but I found that the do method returned 0E0 when nothing is inserted into the database. Any insight welcomed

thanks

Comment on Number of rows affected by an update command Download Code

Replies are listed 'Best First'.
Re: Number of rows affected by an update command by codeacrobat (Chaplain) on Nov 03, 2010 at 06:35 UTC
To compare numbers use "==" "!=" instead of "eq" "ne". `print+qq(\L@{[ref\&@]}@{['@'x7^'!#2/"!4']});`	[reply] [d/l]
Re^2: Number of rows affected by an update command by ambrus (Abbot) on Nov 03, 2010 at 08:36 UTC
Tee hee hee (xkcd://effect).	[reply]
Re: Number of rows affected by an update command by Tux (Canon) on Nov 03, 2010 at 08:50 UTC
From the DBI docs: `The default "do" method is logically similar to: sub do { my ($dbh, $statement, $attr, @bind_values) = @_; my $sth = $dbh->prepare ($statement, $attr) or return undef; $sth->execute (@bind_values) or return undef; my $rows = $sth->rows; ($rows == 0) ? "0E0" : $rows; # always return true if no error } # do` [download] Note the word logically in there. I'm not sure if all DBD drivers follow this exactly as documented. You might try to see if changing the `do ()` to above sequence changes the returned expectation. If it does, please inform the DBD author at hand that the return code from `do ()` is not as documented by the DBI. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^2: Number of rows affected by an update command by Anonymous Monk on Nov 03, 2010 at 20:48 UTC
Thanks for your reply. How would you spot a failed update then?	[reply]
Re^3: Number of rows affected by an update command by Tux (Canon) on Nov 04, 2010 at 07:36 UTC
Define "failed". If an update affects no records at all (the where clause doesn't match any records), is it a FAIL or is it a PASS? For me it is a PASS. A FAIL is when the database returns an error. Personally I ALWAYS use the `RaiseError` database handle attribute, and enclose code that expectedly can fail inside an `eval { }`. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re: Number of rows affected by an update command by JavaFan (Canon) on Nov 03, 2010 at 13:33 UTC
`$updates += $dbh->do($insertstr, undef, ...parameter);` [download] should do. Testing for "0E0" is only useful if you want to distingish between a failed statement, and a succesful statement that just happens to not modify anything. But you don't seem to be interested in that (and you don't need to). Your $update is the sum of the number of updates that modified at least one row, and the number of failed updates.	[reply] [d/l]
Re: Number of rows affected by an update command by Anonymous Monk on Nov 03, 2010 at 01:05 UTC
Shouldn't that be `$updates += $rows_affected;` inside the if block? I'm guessing that "0E0" is specific to the db driver you're using, so it would be documented there (eg, if it's SQLite, at the SQLite project's site), rather than in the DBI module.	[reply] [d/l]