Queries That Return No Resultset

Now that you've mastered SELECT queries, you should find that queries to update, delete, and insert data and even queries to create tables are straightforward. However, they have the characteristic that they return no resultset. As you'll see, you need to look carefully at the result returned so that you know whether your query failed with an error, or whether it succeeded, and if so what difference it made to the database.

In the next simple example, we'll write a query that creates a temporary table. Let's assume that we may want to do an UPDATE and get some feedback that the query has worked. This feedback comes via the statement handle:

my $sql = "UPDATE subscribers
           SET name='New name',
               email='New@email'
           WHERE subscriber_id=4";  ## id 4 doesn't exist
my $sth = $dbh->prepare ($sql)
   or die "Couldn't prepare statement $sql.
";
my $aff = $sth->execute ()
  or die "Couldn't execute statement $sql.
";
print "$aff rows were affected.
";

Look at the subscriber_id we've put in the WHERE clause (4). The query tries to update a record that (deliberately) doesn't exist, and the result we get from running the script is

0E0 rows were affected.

0E0 is the equivalent of zero in a numeric context, but otherwise means true in the conditional sense; it means that the query succeeded without error, but no rows were affected.

In contrast, if the query fails, $sth returns undef. Here's an example using do() in which we try to insert data that would infringe a primary key, thus generating an error:

my $sql = "INSERT INTO subscribers
           SET name='New name',
               email='New@email',
               subscriber_id=3";   ## duplicate primary key
if (my $aff = $dbh->do ($sql)) {
  print "$aff rows were affected.
";
} else {
  print "The insert failed.
";
}
					

$aff += 0;