Programming the Perl DBI

Finishing a Data Fetch Early

When a statement handle for a SELECT statement has been successfully executed, it is said to be active. There’s even a boolean statement handle attribute called Active that you can read. Being active simply means that there’s something actively going on within the database server on behalf of this handle.

When you call a fetch method again, after fetching the last row of data, the driver automatically finishes whatever is actively going on within the database server on behalf of this execute() and resets the Active attribute. Most drivers don’t actually have to do anything in this particular case because the server knows that the driver has fetched the last row. So the server has automatically freed up any resources it was using to store that result set.

Since this finishing-up is done automatically when a fetch method returns an end-of-data status, there’s usually no need to be aware of it. However, there are two types of situations where it’s appropriate to take matters into your own hands by calling the finish() method on the statement handle. (Keep in mind that finish() doesn’t “finish” the statement handle itself—it finishes only the current execution of it. You can still call execute() on the handle again later.)

The first situation is a little obscure and relates to being a good database-citizen. If the database server is using a significant amount of temporary disk space to hold your result set, and you haven’t fetched all the records, ...