7

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?

daxim
  • 39,270
  • 4
  • 65
  • 132
new_perl
  • 7,345
  • 11
  • 42
  • 72
  • 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? – matthias krull Aug 03 '11 at 07:59

3 Answers3

8

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.

daxim
  • 39,270
  • 4
  • 65
  • 132
e.dan
  • 7,275
  • 1
  • 26
  • 29
  • How do you know whether the statement has syntax error in the first place? – R__ Aug 03 '11 at 12:50
  • @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. – e.dan Aug 03 '11 at 13:08
  • 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. –  Apr 10 '12 at 20:28
7

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";
Paolo Rovelli
  • 9,396
  • 2
  • 58
  • 37
1

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.

סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Massimo P.
  • 11
  • 2