Programming the Perl DBI

Deallocating Statement Handles

When a statement is prepared, the returned statement handle is associated with allocated memory resources within both your Perl script and the database server you’re connected to. When you no longer need a statement handle, you should destroy it. That sounds drastic, but all it really means is letting go.

Statement handles are actually represented by Perl objects and, as such, are subject to the machinations of Perl’s garbage collector. This implies that when no references to a statement handle remain (for example, the handle variable has gone out of scope or has been overwritten with a new value), Perl itself will destroy the object and reclaim the resources used by it.

Here’s an example of a short-lived statement handle:

if ($fetch_new_data) {
    my $sth = $dbh->prepare( ... );
    $sth->execute(  );
    $data = $sth->fetchall_arrayref(  );
}

Notice that we don’t have to make any explicit steps to free or deallocate the statement handle. Perl is doing that for us. The my $sth variable holds the only reference to that particular statement handle object. When the $sth variable ceases to exist at the end of the block, the last reference is removed and Perl’s garbage collector swings into action. Similarly, when the script exits, all global variables cease to exist and any objects they refer to are deallocated in the same way.

Here’s a slightly different example:

### Issue SQL statements to select sites by type foreach ( 'Stone Circle', 'Monolith', 'Henge' ) { my $sth ...