Programming the Perl DBI

Fetching Data

Fetching data is the main object of issuing queries to the database. It’s fine to exercise a database by executing queries, but unless you actually retrieve that data, your program will never be able to make use of it.

The data retrieved by your SQL query is known as a result set (so called because of the mathematical set theory on which relational databases are based). The result set is fetched into your Perl program by iterating through each record, or row, in the set and bringing the values for that row into your program. This form of fetching result set data on a row-by-row basis is generally termed a cursor .

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