Programming the Perl DBI

Handling LONG/LOB Data

The DBI requires some additional information to allow you to query back LONG/LOB (long/large object) datatypes from a database. As we discussed earlier in the section on the LongReadLen and LongTruncLen attributes, the DBI is unable to determine how large a buffer to allocate when fetching columns containing LOB data. Therefore, we cannot simply issue a SELECT statement and expect it to work.

Selecting LOB data is straightforward and essentially identical to selecting any other column of another datatype, with the important exception that you should set at least the LongReadLen attribute value prior to preparing the statement that will return the LOB. For example:

### We're not expecting binary data of more than 512 KB...
$dbh->{LongReadLen} = 512 * 1024;

### Select the raw media data from the database
$sth = $dbh->prepare( "
            SELECT mega.name, med.media_data
            FROM megaliths mega, media med
            WHERE mega.id = med.megaliths_id
       " );
$sth->execute();
while ( ($name, $data) = $sth->fetchrow_array ) {
    ...
}

Without the all-important setting of LongReadLen, the fetchrow_array() call would likely fail when fetching the first row, because the default value for LongReadLen is very small—typically 80 or less.

What happens if there’s a rogue column in the database that is longer than LongReadLen? How would the code in the previous example cope? What would happen?

When the length of the fetched LOB data exceeds the value of LongReadLen, an error occurs unless you have set ...