Writing MySQL-Based Programs - MySQL - page 68

SERT or UPDATE that returns no result set, use the database handle do() method. It
executes the statement and returns the number of rows affected by it, or undef if an
error occurs. If Sybil gets a new cat, the following statement increments her cats count
by one:
my $count = $dbh -> do ( "UPDATE profile SET cats = cats+1
WHERE name = 'Sybil'" );
if ( $count ) # print row count if no error occurred
{
$count += 0 ;
print "Number of rows updated: $count\n" ;
}
If the statement executes successfully but affects no rows, do() returns a special value,
"0E0" (the value zero in scientific notation, expressed as a string). "0E0" can be used
for testing the execution status of a statement because it is true in Boolean contexts
(unlike undef ). For successful statements, it can also be used when counting how many
rows were affected because it is treated as the number zero in numeric contexts. Of
course, if you print that value as is, you'll print "0E0" , which might look odd to people
who use your program. The preceding example makes sure that doesn't happen by
adding zero to the value to coerce it to numeric form so that it displays as 0 . Alternatively,
use printf with a %d format specifier to cause an implicit numeric conversion:
if ( $count ) # print row count if no error occurred
{
printf "Number of rows updated: %d\n" , $count ;
}
If RaiseError is enabled, your script terminates automatically for DBI-related errors,
so you need not check $count to find out whether do() failed and consequently can
simplify the code:
my $count = $dbh -> do ( "UPDATE profile SET cats = cats+1
WHERE name = 'Sybil'" );
printf "Number of rows updated: %d\n" , $count ;
To process a statement such as SELECT that does return a result set, use a different
approach that involves these steps:
1. Specify the statement to be executed by calling prepare() using the database han‐
dle. prepare() returns a statement handle to use with all subsequent operations on
the statement. (If an error occurs, the script terminates if RaiseError is enabled;
otherwise, prepare() returns undef .)
2. Call execute() to execute the statement and generate the result set.
3. Loop to fetch the rows returned by the statement. DBI provides several methods
for this; we cover them shortly.