Retrieving number of updated rows in a database

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

Hi, I would like to know how can I retrieve the number of rows that were updated in a database using the handle. For example :

$updateSql = "UPDATE...";
$stHandle = $dbHandle->prepare($updateSql);
$stHandle->execute;
[download]

Thanks

Comment on Retrieving number of updated rows in a database Download Code

Replies are listed 'Best First'.
Re: Retrieving number of updated rows in a database by castaway (Parson) on Feb 02, 2003 at 13:56 UTC
Two ways: Either call `my $numberofrows = $stHandle->rows();` after executing your statement, or bundle it all in to one statement with: `my $numberofrows = $dbh->do("UPDATE .. ");` (Which works if you don't need to bind parameters) Or even: `my $numberofrows = $dbh->do($stHandle);` instead of execute. C. (Its all in the perldoc for DBI)	[reply] [d/l] [select]
Re: Re: Retrieving number of updated rows in a database by Cabrion (Friar) on Feb 02, 2003 at 14:16 UTC
Just to add my two cents. . . $numberofrows will be '0E0' in the event that no rows were updated, but the query was sucessful. It will be '0' if the query failed (i.e. bad SQL). '0E0' is a value that evaluates to true in a boolean context, but acts as the number 0 in a numeric context. I always thought that was quite interesting. Updated: Typos fixed.	[reply]
Re: Re: Retrieving number of updated rows in a database by rdfield (Priest) on Feb 02, 2003 at 18:22 UTC
It also works with bind parameters: `$numberofrows = $dbh->do("update...",undef,@bind_params);` [download] (where undef is a placeholder for any additional attributes, e.g. AutoCommit, RaiseError) rdfield	[reply] [d/l]
Re: Retrieving number of updated rows in a database by steves (Curate) on Feb 02, 2003 at 17:34 UTC
You don't need to change your code to use do instead of execute. execute also returns the number of rows for non-SELECT statments. The 0E0 caveat applies here as well.	[reply]