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. "; }
Note
If you write a query and want to see in a more user-friendly way how many rows were affected (avoiding 0E0), the statement
$aff += 0;
would convert this variable to a numeric, thus displaying either a number or 0 to the user.
18.223.237.29