Executing Non-SELECT Statements (Programming the Perl DBI)

5.2. Executing Non-SELECT Statements

We discussed in Chapter 3, "SQL and Relational Databases " the various data manipulation techniques that you might wish to use on your data. So far in this chapter, we have discussed the most commonly used data manipulation operation, fetching. But what about inserting, deleting, and updating data?

These operations are treated somewhat differently than querying, as they do not use the notion of a cursor to iterate through a result set. They simply affect rows of data stored within tables without returning any rows to your programs. As such, the full prepare-execute-fetch-deallocate cycle is not as appropriate for these operations. The fetch stage simply doesn't apply.

Since you're usually going to invoke these statements only once, it would be very tiresome to have to call prepare( ) to get a statement handle and then call execute( ) on that statement handle to actually invoke it, only to immediately discard that statement handle.

Fortunately, the DBI defines a shortcut for carrying out these operations -- the do( ) method, invoked against a valid database handle. Using do( ) is extremely easy. For example, if you wished to delete some rows of data from the megaliths table, the following code is all that's required:

### Assuming a valid database handle exists....
### Delete the rows for Stonehenge!
$rows = $dbh->do( "
            DELETE FROM megaliths
            WHERE name = 'Stonehenge'
        " );

To signify whether or not the SQL statement has been successful, a value is returned from the call signifying either the number of rows affected by the SQL statement, or undef if an error occurred.

Some databases and some statements will not be able to return the number of rows affected by some statements; -1 will be returned in these cases.

As a special case, a row count of zero is returned as the string 0E0, which is just a fancy mathematical way of saying zero. Returning 0E0 instead of 0 means that the do( ) method still returns a value that Perl interprets as true, even when no rows have been affected.[49] The do( ) method returns a false value only on an error.

[49]Perl actually has special logic to allow the string 0 but true to be used for this kind of purpose. The DBI doesn't use that because people are bound to write messages like print Deleted $rows rows\n and Deleted 0E0 rows reads slightly better than Deleted 0 but true rows.

A good DBI method to remember is quote( ) -- especially when building SQL statements, and especially when inserting new data into the database via do( ). This method correctly quotes values as literal strings within your SQL statement before it is issued to the database. We discussed this method in Chapter 4, "Programming with the DBI ".