Programming the Perl DBI

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,^[51] the Perl variables associated with each column are automatically updated with the fetched values.

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"; ...