Recipe 9.1. Obtaining the Number of Rows Affected by a Statement


Problem

You want to know how many rows were changed by an SQL statement.

Solution

Sometimes the row count is the return value of the function that issues the statement. Other times the count is returned by a separate function that you call after issuing the statement.

Discussion

For statements that affect rows (UPDATE, DELETE, INSERT, REPLACE), each API provides a way to determine the number of rows involved. For MySQL, the default meaning of "affected by" is "changed by," not "matched by." That is, rows that are not changed by a statement are not counted, even if they match the conditions specified in the statement. For example, the following UPDATE statement results in an "affected by" value of zero because it does not change any columns from their current values, no matter how many rows the WHERE clause matches:

UPDATE limbs SET arms = 0 WHERE arms = 0; 

The MySQL server allows a client to set a flag when it connects to indicate that it wants rows-matched counts, not rows-changed counts. In this case, the row count for the preceding statement would be equal to the number of rows with an arms value of 0, even though the statement results in no net change to the table. However, not all MySQL APIs expose this flag. The following discussion indicates which APIs enable you to select the type of count you want and which use the rows-matched count by default rather than the rows-changed count.

Perl

In Perl DBI scripts, the row count for statements that modify rows is returned by do⁠(⁠ ⁠ ⁠):

my $count = $dbh->do ($stmt); # report 0 rows if an error occurred printf "Number of rows affected: %d\n", (defined ($count) ? $count : 0); 

If you prepare a statement first and then execute it, execute⁠(⁠ ⁠ ⁠) returns the row count:

my $sth = $dbh->prepare ($stmt); my $count = $sth->execute (); printf "Number of rows affected: %d\n", (defined ($count) ? $count : 0); 

You can tell MySQL whether to return rows-changed or rows-matched counts by specifying mysql_client_found_rows in the options part of the data source name argument of the connect⁠(⁠ ⁠ ⁠) call when you connect to the MySQL server. Set the option to 0 for rows-changed counts and 1 for rows-matched counts. Here's an example:

my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); my $dsn = "DBI:mysql:cookbook:localhost;mysql_client_found_rows=1"; my $dbh = DBI->connect ($dsn, "cbuser", "cbpass", \%conn_attrs); 

mysql_client_found_rows changes the row-reporting behavior for the duration of the connection.

Although the default behavior for MySQL itself is to return rows-changed counts, recent versions of the Perl DBI driver for MySQL automatically request rows-matched counts unless you specify otherwise. For applications that depend on a particular behavior, it's best to explicitly set the mysql_client_found_rows option in the DSN to the appropriate value.

Ruby

For statements that modify rows, Ruby DBI returns row counts similarly to Perl DBI scripts for the do method. That is, do itself returns the count:

count = dbh.do(stmt) puts "Number of rows affected: #{count}" 

If you use execute to execute a statement, execute does not return the row count. Instead, use the statement handle rows method to get the count after executing the statement:

sth = dbh.execute(stmt) puts "Number of rows affected: #{sth.rows}" 

The Ruby DBI driver for MySQL returns rows-changed counts by default, but the driver supports a mysql_client_found_rows option that enables you to control whether the server returns rows-changed or rows-matched counts. Its use is analogous to Perl DBI. For example, to request rows-matched counts, do this:

dsn = "DBI:Mysql:database=cookbook;host=localhost;mysql_client_found_rows=1" dbh = DBI.connect(dsn, "cbuser", "cbpass") 

PHP

In PHP, invoke the connection object's affectedRows⁠(⁠ ⁠ ⁠) method to find out how many rows a statement changed:

$result =& $conn->query ($stmt); # report 0 rows if the statement failed $count = (PEAR::isError ($result) ? 0 : $conn->affectedRows ()); print ("Number of rows affected: $count\n"); 

Python

Python's DB-API makes the rows-changed count available as the value of the statement cursor's rowcount attribute:

cursor = conn.cursor () cursor.execute (stmt) print "Number of rows affected: %d" % cursor.rowcount 

To obtain rows-matched counts instead, import the MySQLdb client constants and pass the FOUND_ROWS flag in the client_flag parameter of the connect⁠(⁠ ⁠ ⁠) method:

import MySQLdb.constants.CLIENT conn = MySQLdb.connect (db = "cookbook",                         host = "localhost",                         user = "cbuser",                         passwd = "cbpass",                         client_flag = MySQLdb.constants.CLIENT.FOUND_ROWS) 

Java

For statements that modify rows, the MySQL Connector/J JDBC driver provides rows-matched counts rather than rows-changed counts. This is done for conformance with the JDBC specification.

The Java JDBC interface provides row counts two different ways, depending on the method you invoke to execute the statement. If you use executeUpdate⁠(⁠ ⁠ ⁠), the row count is its return value:

Statement s = conn.createStatement (); int count = s.executeUpdate (stmt); s.close (); System.out.println ("Number of rows affected: " + count); 

If you use execute⁠(⁠ ⁠ ⁠), that method returns true or false to indicate whether the statement produces a result set. For statements such as UPDATE or DELETE that return no result set, execute⁠(⁠ ⁠ ⁠) returns false and the row count is available by calling the getUpdateCount⁠(⁠ ⁠ ⁠) method:

Statement s = conn.createStatement (); if (!s.execute (stmt)) {   // there is no result set, print the row count   System.out.println ("Number of rows affected: " + s.getUpdateCount ()); } s.close (); 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net