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"; ...
Get Programming the Perl DBI now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.