Learning MySQL

Using Queries That Don’t Return Answer Sets

For queries such as INSERT, UPDATE, REPLACE, and DELETE that do not return a result set, we can use the do() function to perform the query without the need for a prior call to prepare().

The do() function returns the number of rows affected by the query; if the query could not be performed successfully, the function returns zero. If the query was performed successfully but no rows were affected, the function returns the value 0E0, which is 0 times 10 to the power 0 (for instance, 1E3 is 1000). Perl treats 0E0 as having the numerical value zero but the Boolean value true, so if the query returns this value, we know that the operation succeeded (true) but that zero rows were affected:

my $rowsaffected=$dbh->do($Query);
if($rowsaffected && $rowsaffected==0E0)
{
 print "Operation was successful but no rows were affected\n";
}
elsif($rowsaffected)
{
 print "Operation was successful ($rowsaffected rows)\n";
}
else
{
 print "Operation failed: $DBI::errstr\n";
}

Let’s explore this by looking at four possibilities. Imagine you have a database called perltest and it includes a table called testtable with two string columns; these could have been created from the monitor with the following statements:

mysql> CREATE DATABASE perltest;
Query OK, 1 row affected (0.23 sec)

mysql> USE perltest;
Database changed
mysql> CREATE TABLE testtable (col1 CHAR(40), col2 CHAR(40));
Query OK, 0 rows affected (0.01 sec)

If the query is:

my $Query="INSERT INTO testtable ...