Chapter 5. Interacting with the Database

5.1.1. Preparing SQL Statements

What typically occurs is that the SQL statement is sent as a string of characters via a valid database handle to the database. This string is then parsed by the database itself to ensure that it is valid SQL, both in terms of syntax and also in terms of entities referred to within the database (e.g., to make sure you aren't referring to tables that don't exist and that you have permission to refer to those that do).

Figure 5-1. Statement preparation data flow via DBI

It is through this DBI statement handle that you perform the remainder of the data-fetching cycle.

#!/usr/bin/perl -w
#
# ch05/prepare/ex1: Simply creates a database handle and a statement handle

use DBI;

### The database handle
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" );

### The statement handle
my $sth = $dbh->prepare( "SELECT id, name FROM megaliths" );

exit;

This, of course, assumes that all goes well with the parsing of the statement. It is possible that you made a mistake when typing in your SQL statement, or that the database failed to parse the statement for any number of other reasons. If this occurs, a value of undef is returned from the prepare() call, signifying that the parse has failed.

5.1.2. Executing Select Statements

Performing the execution of the SQL statement occurs via a valid statement handle created when the prepare() method successfully completes. For example, execution of an SQL statement can be expressed as simply as:

### Create the statement handle
my $sth = $dbh->prepare( "SELECT id, name FROM megaliths" );

### Execute the statement handle
$sth->execute();

So, after successful execution, the database and driver are ready to return the results, but those results will not have been returned to your Perl program yet. This is an important point to remember. To extract the results data from the database, you need to explicitly fetch them. This is the third stage in the cycle.

5.1.3. Fetching Data

Cursors are used for sequential fetching operations: records are fetched in the order in which they are stored within the result set. Currently, records cannot be skipped over or randomly accessed. Furthermore, once a row addressed by a cursor has been fetched, it is ``forgotten'' by the cursor. That is, cursors cannot step backwards through a result set.

Therefore, the general way in which we fetch data from the database's result set is to loop through the records returned via the statement handle, processing each row until no rows are left to fetch. This can be expressed by the following pseudo-code.

while ( records to fetch from $sth ) {
    ### Fetch the current row from the cursor
    @columns = get the column values;
    ### Print it out...
    print "Fetched Row: @columns\n";
}

The DBI simplifies this process even further by combining the check for more data and the fetching of that data into a single method call.

### Prepare the SQL statement ( assuming $dbh exists )
$sth = $dbh->prepare( "
            SELECT meg.name, st.site_type 
            FROM megaliths meg, site_types st
            WHERE meg.site_type_id = st.id
          " );

### Execute the SQL statement and generate a result set
$sth->execute();

### Fetch each row of result data from the database as a list
while ( ( $name, $type ) = $sth->fetchrow_array ) {
    ### Print out a wee message....
    print "Megalithic site $name is a $type\n";
}

You could also fetch the data via fetchrow_array() into an array variable instead of a list of scalar variables by writing:

while ( @row = $sth->fetchrow_array ) {
    ### Print out a wee message
    print "Megalith site $row[0] is a $row[1]\n";
}

which is functionally identical.

The fundamentally important thing to remember is that the fields in the result set are in the order in which you asked for the columns in the SQL statement. Therefore, in the example code listed above, the name field was requested before the site_type field. This ensured that the first element of the array or scalar list was the value of the name field, followed by the values of the site_type field.

The while loop keeps looping until the expression in parentheses evaluates to false. Naturally, we want to stop looping when there's no more data to fetch, and the fetchrow_array() method arranges that for us. It returns an empty list when there's no more data. Perl treats that as a false value, thus stopping the loop.

### Fetch the rows of result data from the database
### as an array ref....
while ( $array_ref = $sth->fetchrow_arrayref ) {
    ### Print out a wee message....
    print "Megalithic site $arrayref->[0] is a $array_ref->[1]\n";
}
die "Fetch failed due to $DBI::errstr" if $DBI::err;

An important thing to watch out for is that currently the same array reference is used for all rows fetched from the database for the current statement handle. This is of utmost importance if you are storing the row data somewhere for future reference. For example, the following code was written to stash the returned megalith data in a persistent store for future reference after fetching:

### The stash for rows...
my @stash;

### Fetch the row references and stash 'em!
while ( $array_ref = $sth->fetchrow_arrayref ) {
    push @stash, $array_ref;        # XXX WRONG!
}

### Dump the stash contents!
foreach $array_ref ( @stash ) {
    print "Row: @$array_ref\n";
}

Something very strange happens here. All the rows printed out from the stash are identical instead of being different. This is because you've stored the reference to the row data instead of the row data itself, and since DBI reuses the same reference for each row, you don't quite get the results you expect. Be sure to store a copy of the values that the array reference points to rather than the reference itself, as this example shows:

### The stash for rows...
my @stash;

### Fetch the row references and stash 'em!
while ( $array_ref = $sth->fetchrow_arrayref ) {
    push @stash, [ @$array_ref ];  # Copy the array contents
}

### Dump the stash contents!
foreach $array_ref ( @stash ) {
    print "Row: @$array_ref\n";
}

The fetchrow_arrayref() method is used especially in conjunction with column binding, which we shall discuss later in this chapter.

### Fetch the current row into a hash reference
while ( $hash_ref = $sth->fetchrow_hashref ) {
    ...

The hash pointed to by the reference has the names of the fetched fields as the keys to the hash, and the values of those fields are stored as the hash values. Thus, if we fetched the name and site_type fields from the database, we could address the hash elements like this:

### Fetch rows into a hash reference
while ( $hash_ref = $sth->fetchrow_hashref ) {
    print "Megalithic site $hash_ref->{name} is a $hash_ref->{site_type}\n";
}

There are, as you might expect, a few caveats to using this particular method. The most important thing to watch out for is the actual name of the field that you've fetched. Some databases will do strange things to the field name, such as convert it to all uppercase or all lowercase characters, which could cause you to access the wrong hash key. You can avoid this problem by telling fetchrow_hashref() the name of the attribute to use to supply the field names. That is, you could use NAME as the default; NAME_uc to force field names to be uppercase; and NAME_lc to force them to be lowercase. For example, a portable way to use hash references can be written as:

### Fetch rows into a hash reference with lowercase field names
while ( $hash_ref = $sth->fetchrow_hashref('NAME_lc') {
    print "Megalithic site $hash_ref->{name} is a $hash_ref->{site_type}\n";
}

Specifying NAME_uc or NAME_lc is recommended, and doesn't have any impact on performance.

SELECT megaliths.id, ...

then most databases will still return only the string id as the name of the field. That's not usually a problem but can trip you up if you have selected more than one field with the same name, for example:

SELECT megaliths.id, media.id ...

SELECT megaliths.id meg_id, media.id med_id ...

though some databases require the slightly more verbose form:

SELECT megaliths.id AS meg_id, media.id AS med_id ...

This alias technique is also very handy when selecting expressions such as:

SELECT megaliths.id + 1 ...

because databases differ in how they name columns containing expressions. Using aliases not only makes it easier to refer to the columns but also makes your application more portable.

When discussing fetchrow_arrayref(), we pointed out that it currently returns the same array reference for each row. Well, fetchrow_hashref() currently doesn't return the same hash reference for each row but definitely will in a future release. (This change will also make it faster, as it's a little slower than we'd like at the moment.)

5.1.4. Finishing a Data Fetch Early

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.

The second type of situation is less obscure, mainly because the DBI nags you about it by issuing warnings like this one from disconnect( ):

disconnect invalidates 1 active statement handle
(either destroy statement handles or call finish on them before disconnecting)

What's happening here is that the DBI is warning you that an active statement handle, that may still have data to be fetched from it, is being invalidated (i.e., made unusable) by disconnecting from the database.

Why does the DBI bother to warn you? The idea is to help you spot cases where you have not caught and dealt with an error from a fetch method that has terminated a fetch loop before all the data has been retrieved. Some row fetch errors, such as a transaction being aborted, mean that it's not possible for more rows to be fetched from that statement handle. In those cases, the driver resets the Active flag. For others though, such as a divide-by-zero in a column expression, or a long field value being truncated, further rows can be fetched, so the driver leaves the Active flag set.

In practice, there are other situations apart from fetch loops that can leave you with active statement handles both in the normal flow of events and due to exceptional circumstances.

The most humble is the common desire to fetch only n rows because you know there are only n rows to be fetched. Most drivers can't tell that you've fetched the last row, so they can't reset the Active flag. This is similar to the "good database-citizen" situation we discussed earlier. The following example shows the finish( ) method being called after fetching the one and only row of interest:

sub select_one_row {
    my $sth = shift;
    $sth->execute(@_) or return;
    my @row = $sth->fetchrow_array(  );
    $sth->finish(  );
    return @row;
}

5.1.5. Deallocating Statement Handles

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

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

Here's a slightly different example:

### Issue SQL statements to select sites by type
foreach ( 'Stone Circle', 'Monolith', 'Henge' ) {
    my $sth = $dbh->prepare( ... $_ ... );
    $sth->execute(  );
    $sth->dump_results(  );
}

The second and subsequent itterations of the loop assign a new statement handle reference to the $sth variable, which deletes the reference it previously held. So once again, since that was the only reference to the handle and it's now been deleted, the handle gets deallocated.

You might have an application that prepares, uses, and discards thousands (or hundreds of thousands) of statement handles throughout its lifetime. If the database resources for the statements were not freed until the database connection was closed, you could easily exhaust the database resources in a short amount of time.

In practice, the only time that you might overload the database is when you're storing the statement handles in arrays or hashes. If you're not careful to delete or overwrite old values, then handles can accumulate.