Binding Output Columns (Programming the Perl DBI)

5.4. Binding Output Columns

In the examples of fetching data that we've seen so far, a fetch() method has been called that returns values we've copied into Perl variables. For example:

while( ( $foo, $bar ) = $sth->fetchrow_array ) { ... }

This syntax is fine, but it can get messy if many fields are being returned. It also involves extra copying of data, which can get expensive if many large strings are being fetched.

DBI supports a feature that simplifies the fetching of data and avoids the extra copying. This has the desired effect of making fetches very fast. It's known as binding columns, and it works by nominating a Perl variable to be used directly for storing values of a particular column as they are fetched. This has the basic effect that when data is fetched from the database via a fetch( ) method,[53] the Perl variables associated with each column are automatically updated with the fetched values.

[53]fetch( ) is just a handy short alias for fetchrow_arrayref( ).

The best way to illustrate this process is by an example:

### Perl variables to store the field data in
my ( $name, $location, $type );

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

### Associate Perl variables with each output column
$sth->bind_col( 1, \$name );
$sth->bind_col( 2, \$location );
$sth->bind_col( 3, \$type );

### Fetch the data from the result set
while ( $sth->fetch ) {
    print "$name is a $type located in $location\n";
}

The method we have used to explicitly associate the Perl variables to the output columns is bind_col( ) , which takes the index of the column to associate, starting from 1, and a reference to the Perl variable to associate it with. Thus, when the fetch() call completes, the associated Perl variables will be automatically updated without having to explicitly assign the fetched values. This is an extremely efficient way of fetching data from the database, both from a programming perspective and also from a performance point of view. bind_col( ) uses references to Perl variables, and, as such, there is no additional object or memory allocation when using bound output columns.

To ensure maximum portability, bind_col( ) should be called against an executed statement handle. For example, if your database does not return any real information from the prepare( ) call, bind_col( ) will not have sufficient information to succesfully associate the output columns with the Perl variables. This might lead to extremely peculiar results.

Using bind_col( ) to explicitly bind each column individually can get a bit tiresome, especially if many output columns are used. Fortunately, DBI defines an additional method called bind_columns( ) that can be used to quickly specify column bindings for multiple columns in one call.

bind_columns( ) works in an almost identical way to bind_col( ) except that instead of explicitly specifying the column index to bind a Perl variable to, you simply specify the Perl variables and the column assignation occurs automatically. For example, the code from earlier can be rewritten in the following way to use bind_columns( ):

### Perl variables to store the field data in
my ( $name, $location, $type );

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

### Associate Perl variables with each output column
$sth->bind_columns( undef, \$name, \$location, \$type );

### Fetch the data from the result set
while ( $sth->fetch ) {
    print "$name is a $type located in $location\n";
}

It is important to know that the number of columns specified in the SQL statement and the number of Perl variables specified in bind_columns( ) must match exactly. You cannot pick and choose which columns to fetch the data from as you can with bind_col( ).[54]

[54]The first argument to bind_columns( ) is an undef, due to historical reasons. It's no longer required if you are using DBI 1.08 or later.

Since bind_columns( ) uses bind_col( ) internally, the rules for using these two methods are the same.

Finally, we should mention that bind values specified with a SQL statement are completely unrelated to the ability to bind Perl variables to output columns of an SQL statement. They are separate operations. Bind values operate at a database input level, whereas output column bindings operate purely at a Perl output level.