Other DBI Data-Retrieval Functions

Now that we have covered the basics of fetching data from a database table using the Perl DBI, we'll cover some alternative ways in Perl to fetch the data. In addition, we'll dabble with making our output look a little nicer.

With Perl and the DBI, you are not stuck using the fetch function ($sth->fetch) to get data; there are several different ways to do so. There are fetchrow_array, fetchrow_arrayref, fetchrow_hashref, and fetchall_arrayref. You can choose whichever method or methods you wish. You will probably become comfortable with one of the methods and use that one most often, which is perfectly fine. Remember that with Perl there is more than one way to do it.

The functions fetch and fetchrow_arrayref do the exact same thing, so we'll skip any further explanation of fetchrow_arrayref, since fetch has been covered so well in our previous examples.

fetchrow_array

Since we've been dealing with array references up to this point, working with actual arrays is now appropriate. The following code shows the fetchrow_array function.

01: #!/usr/bin/perl -w 02: # 03: # select6.pl 04: # Chapter 4 05: # Listing 4-6# 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass")  10:     or die("Cannot connect: $DBI::errstr");

Lines 1-10 are very similar to the corresponding lines in the previous programs in this chapter.

11: my $sql = qq(SELECT isbn, price, title FROM library);

Line 11 is our SQL statement. This time, we fetch only the isbn, price, and title of the books instead of every field for each record. You do not have to fetch all of the data each time you perform a SELECT on a database-you can tell the DBMS exactly which fields you want by placing them after the SELECT statement instead of using a *.

12: my $sth = $dbh->prepare($sql); 13: $sth->execute;

Lines 12 and 13 are also identical to our previous examples.

14: while(my @array = $sth->fetchrow_array){

Line 14 looks similar to the previous examples, but we are now dealing with an actual array instead of a reference to an array.

Each time through the while loop, we fetch one row of data. Each field of the current record gets stored in the array. If you look at the output from this example, you can see that element 0 of the array contains the isbn, that element 1 contains the price, and that element 2 contains the title.

15:     no warnings; 16:     my $index = 0;

Line 15 turns off warnings. If we try to print the data and a field from the table we are selecting data from is empty, a warning message gets printed out each time. The warning messages generated as a result of this are no big deal; we know what is causing them. Instead of having our output littered with warnings, we simply turn them off. When warnings are turned off like this, it applies to the current block only-so once we exit that block of code, warnings are back on.

Line 16 declares a variable called $index and initializes it with a value of 0.

17:     for my $data(@array){ 18:         print "\$array[$index] contains $data\n"; 19:         $index++; 20:     }

Line 17 begins a for loop that loops through the @array and stores the current value from @array into the $data variable.

Line 18 prints the text shown in the program output.

Line 19 increments the $index variable.

Line 20 closes the for loop.

21:     print "\n"; 22: }

Line 21 prints a final newline so that the output of each record is visually separated.

Line 22 closes the while loop. This line marks the end of the program.

The following code shows the output of this program.

$array[0] contains 1565922697 $array[1] contains     $34.95 $array[2] contains Web Security and Commerce $array[0] contains 1565924924 $array[1] contains     $34.95 $array[2] contains HTML: The Definitive Guide $array[0] contains 0672308916 $array[1] contains     $34.95 $array[2] contains PERL Programming Unleashed, with CD-ROM $array[0] contains 1565925254 $array[1] contains     $32.95 $array[2] contains Palm Programming: The Developer's Guide with CD-ROM $array[0] contains 1565924207 $array[1] contains     $29.95 $array[2] contains PalmPilot: The Ultimate Guide $array[0] contains 0672304023 $array[1] contains     $29.95 $array[2] contains UNIX, with CD-ROM (Unleashed)

Remember that with the fetchrow_array method, the result is an actual array rather than a pointer to an array. Depending on how you are using the data and how much data you have to access, it may be easier to deal with an array rather than a reference to an array; the choice is yours.

fetchall_arrayref

The fetchall_arrayref function can be a bit trickier to work with than the fetchrow_array function, since what you end up with is a reference to an array-where each row of the referenced array is a reference to another array containing the data for that row. Confused? The following example should help clear things up a bit:

01: #!/usr/bin/perl -w 02: # 03: # select7.pl 04: # Chapter 4 05: # Listing 4-7 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass")  10:     or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute;

Lines 1-13 are almost identical to the previous programs. Please refer back if you are uncertain what these lines do.

14: my $ref = $sth->fetchall_arrayref; 15: my $record_idx = 0;

Line 14 is the call to the fetchall_arrayref function. Notice that we do not have to have a loop that calls the fetch function several times.

Line 15 declares a variable named $record_idx and initializes it to 0. This is our record index and is the first of two counters for this program.

16: for my $rec (@$ref){

Line 16 is a for loop that loops through the array referenced in $ref. Each time through the loop, $rec gets set to a reference to an array that holds one row of data. (This is our second reference). At this point, with $rec, we have data that is like the data when we use the fetch method.

We basically have something like this: ReferenceÍReferenceÍData

17:     no warnings; 18:     my $field_idx = 0;

Line 17 turns off warnings for this block. Several of the data elements are blank, so turning off warnings suppresses these messages when the program is run.

Line 18 declares a variable named $field_idx and initializes it to 0. This is the second counter, field index; we'll use this to keep track of where we are (what field we are at) in the arrays.

19:     for my $field (@$rec){ 20:         print "ref[$record_idx][$field_idx] = $field\n"; 21:         $field_idx++; 22:     }

Line 19 begins a for loop that iterates through the current array reference that we get from Line 16.

Line 20 prints the current data along with where it is referenced. The output of the program shows you what this looks like.

Line 21 increments $field_idx, the second counter.

Line 22 closes the inner for loop.

23:     $record_idx++; 24:     print "\n"; 25: }

Line 23 increments the first counter, $record_idx.

Line 24 prints a newline character so that we can visually separate the different records in our output.

Line 25 closes the outer for loop.

26: print "\n"; 27: print "$ref->[57][1]\n\n";

Line 26 prints a newline.

Line 27 is an example of how you can print a specific item from the result set. In this example, we want to print only a specific field from a specific record-namely, field 1 from record 57 from the arrayref in $ref. That is, $ref->[57][1].

The following code shows the output of this program.

ref[57][0] = 1565925254 ref[57][1] = Palm Programming: The Developer's Guide with CD-ROM ref[57][2] = Neil Rhodes Julie McKeehan ref[57][3] =     $32.95 ref[57][4] =  Paperback, 1st ed., 482pp. ref[57][5] =  O'Reilly & Associates, Incorporated ref[57][6] =  December 1998 ref[57][7] =  ref[58][0] = 1565924207 ref[58][1] = PalmPilot: The Ultimate Guide ref[58][2] = Tim O'Reilly (Editor) ref[58][3] =     $29.95 ref[58][4] =  Paperback, 1st ed., 489pp. ref[58][5] =  O'Reilly & Associates, Incorporated ref[58][6] =  May 1998 ref[58][7] =  ref[59][0] = 0672304023 ref[59][1] = UNIX, with CD-ROM (Unleashed) ref[59][2] = Emmett Dulaney Susan Christopherson Fran Hatton ref[59][3] =     $29.95 ref[59][4] =  Hardcover, 1st ed., 1600pp. ref[59][5] =  Sams ref[59][6] =  November 1993 ref[59][7] =  Palm Programming: The Developer's Guide with CD-ROM

There are a couple important things to remember when using fetchall_arrayref.

• If you have a lot of data, it will all be loaded into memory as a result of a call with this function. If you are dealing with a large number of matching records, consider using one of the other data-access methods.

• The data returned from a fetchall_arrayref call is not always all of the matching records. Instead, it is all of the remaining matching records. So, if you have gone through the first 10 results with the fetchrow_array function, a call to fetchall_arrayref will return results 11 through the end.

fetchrow_hashref

The fetchrow_hashref method of data access is one of the best. Instead of having to deal with arrays and array indexing, you can access the data via a hash; the keys of the hash are the field names of the data.

01: #!/usr/bin/perl -w 02: # 03: # select8.pl 04: # Chapter 4 05: # Listing 4-8 06: # 07: use strict; 08: use DBI; 09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass")  10:     or die("Cannot connect: $DBI::errstr"); 11: my $sql = qq(SELECT * FROM library); 12: my $sth = $dbh->prepare($sql); 13: $sth->execute;

This program is also quite similar to the others we've worked with so far, but we have made a few changes because we are using the fetchrow_hashref function. If you haven't noticed yet, most of the data-access methods in the DBI are very similar.

Lines 1-13 are again nearly identical to the previous examples; if you have questions about what something is doing, please look back in this chapter for a more detailed explanation.

14: my $counter = 1;

Line 14 declares a variable named $counter and initializes it to 1. We use this variable to keep the current record number.

15: while(my $record = $sth->fetchrow_hashref){

Line 15 begins a while loop that fetches a row of data at a time. The $record inside of the while loop declaration stores a reference to a hash of the current record. This is almost identical to the fetch and fetchrow_arrayref functions, but we store a hash reference instead of an array reference.

16:     no warnings; 17:     print "Book #$counter\n";

Line 16 turns off warnings. Some of the fields in the database are empty, and if we try to use these fields in the program's output, we end up triggering a warning each time. Since we know what causes these warnings, we can safely turn them off for this block of code.

Line 17 prints the current book number.

18:     print "Title:     $record->{title}\n"; 19:     print "Author:    $record->{author}\n"; 20:     print "Publisher: $record->{publisher}\n"; 21:     print "Cost:      $record->{price}\n";

Lines 18-21 print a label and the database-field information for the current record. Notice that the hash keys are the same as the database field names. These hash keys are taken directly from the database. They are case sensitive, so make sure you use the exact spelling you use with the database fields.

22:     print "\n"; 23:     $counter++; 24: }

Line 22 prints an extra newline character so that each record is shown separately and easier to distinguish.

Line 23 increments the $counter variable.

Line 24 ends the while loop and the program.

The following code shows the output of this program.

Book #58 Title:     Palm Programming: The Developer's Guide with CD-ROM Author:    Neil Rhodes Julie McKeehan Publisher:  O'Reilly & Associates, Incorporated Cost:          $32.95 Book #59 Title:     PalmPilot: The Ultimate Guide Author:    Tim O'Reilly (Editor) Publisher:  O'Reilly & Associates, Incorporated Cost:          $29.95 Book #60 Title:     UNIX, with CD-ROM (Unleashed) Author:    Emmett Dulaney Susan Christopherson Fran Hatton Publisher:  Sams Cost:          $29.95

The fetchrow_hashref function is as easy to use, if not easier, as the array methods. The best thing about this function is that it is easy to tell exactly what database field you are dealing with, since the key value is actually the field name from the database. Working with the field names instead of with an array index also makes the code clearer for anyone who reads it or for anyone may have to come back and maintain it.