How do I get the number of affected rows when I use DBI's prepare/execute for non-select statement?

Question

According to the DBI documentation, it seems I can only get the number of affected rows by the do method.

$rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");

How can I get the same result if I use prepare/execute?

Contradiction

execute usually returns the number of rows affected for non-SELECT statements. So in your example it should do. Did you try it? What was the result? – Crick

Answer 1

From the documentation about the execute method in DBI:

For a non-"SELECT" statement, "execute" returns the number of rows affected, if known. If no rows were affected, then "execute" returns "0E0", which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known, then "execute" returns -1.

Valarievalda

How do you know whether the statement has syntax error in the first place? – Iwo

@R__: prepare returns undef. The DBI docs are admittedly a little less than explicit about this, though they do give the example $sth = $dbh->prepare($statement) or die $dbh->errstr; which hints to this. – Valarievalda

This doesn't seem to be correct any longer, at least in version 4.007. execute is returning the number of matched rows, not the number of affected rows. – Pentarchy

Answer 2

If your query is a non-SELECT one (e.g. UPDATE or DELETE), then you can take advantage of rows:

my $query = "...";  # your query
my $sth = $dbh->prepare($query);
$sth->execute();
print "Number of rows affected: " . $sth->rows . "\n";

rows returns the number of rows affected by the last query or -1 in case of error. However, by design, you cannot rely on rows for SELECT statement.

Note that, for non-SELECT queries, also execute returns the number of rows affected. However, if no row is affected, then execute returns "0E0" (which Perl should anyway treat as 0).

my $query = "...";  # your query
my $sth = $dbh->prepare($query);
my $numrows = $sth->execute();
print "Number of rows affected: " . $numrows . "\n";

If, instead, your query is a SELECT, then you cannot rely on rows.

However, you can do either:

my $query = "SELECT COUNT(*) AS rows FROM ... WHERE ...";
my $numrows = $dbh->selectrow_array($query, undef);
print "Number of rows: " . $numrows . "\n";

Or, similarly:

my $query = "SELECT COUNT(*) AS rows FROM ... WHERE ...";
my $numrows = $dbh->selectall_arrayref($query, { Slice => {} });
print "Number of rows: " . @$numrows[0]->{rows} . "\n";

Ellingson

Answer 3

As user153275 said:

"This doesn't seem to be correct any longer, at least in version 4.007.
Execute is returning the number of matched rows, not the number of affected rows."

I found a useful solution in this link, adding the where clause AND (columnName <> newValue):
https://www.perlmonks.org/?node_id=1141381

In this way the query will find only the rows to change.

Displease