Simple Data-Retrieval Wrap-up

We've covered five methods of fetching data with the DBI. (Actually, we've covered four methods, as fetch and fetchrow_arrayref are the same.) Getting data from a database with Perl should be a breeze for you at this point, and none of our example programs have gone over 25 lines yet!

We are going to cover one final example, which uses one of the data-access methods we just learned about. But this example is going to make the output of the program a little more user friendly. This example reinforces what you've already learned, showing you another way to deal with warnings.

01: #!/usr/bin/perl -w 02: # 03: # 04: # Chapter 4 05: # Listing 4-9 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; 14: my $counter = 1;

Lines 1-14 are again almost identical to our previous examples.

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

Line 15 begins the while loop that fetches the data one row at a time and stores a reference to a hash containing the data in the variable $record.

16:     for my $field (keys %$record){ 17:         $record->{$field} = "N/A" unless $record->{$field}; 18:         $record->{$field} =~ s/^\W+//; 19:         $record->{$field} =~ s/\015?\012//g; 20:     }

Line 16 begins a for loop that loops through each of the keys in the hash %$p.

The addition of this loop is something that we have not done yet. This loop is intended to check the data and to filter some of the characters we don't want out of the data. It also helps us eliminate the warnings we get when we try to print a blank database item.

Line 17 sets the current value at $record->{$field} to N/A if it is currently a blank field. By doing this, we get rid of any warnings generated by trying to print blank data.

Line 18 gets rid of any nonword characters at the beginning of the string. Many of the fields originally contain spaces at the beginning. This line removes those spaces so that our output is aligned and looks much better.

The regular expression replacement s/^\W+//; breaks down like this:

  • ^ means we are looking at the beginning of the string.

  • \W means any nonword characters; this includes spaces and control characters.

  • + means matching the expression one or more times. If there is one nonword character, or 100 nonword characters at the beginning of the line, this expression will get rid of all of them.

  • // at the end is what we are substituting any matched characters with-nothing. This expression removes any nonword characters from the beginning of the string.

Line 19 is another regular expression. This time, the regular expression is looking for carriage-return characters or linefeed characters.

\015 is the octal value for a carriage return, and \012 is the octal value for a linefeed. The operating system the program is running on determines exactly what constitutes a newline. Some systems use only a linefeed, but some use both. This regular expression takes care of most situations. The \015?\012 means the carriage return may or may not be present, but if it is, that's ok. The \012 means the linefeed is always present.

Simply put, this regular expression removes all newlines from the string. The g on the end means to apply this regular expression globally to the string. If we eliminate the g, only the first instance of a newline will be removed from the string.

Line 20 ends the for loop that removes the characters we don't want our strings to contain.

21:     print "Book #$counter\n"; 22:     print "Title:     $record->{title}\n"; 23:     print "Author:    $record->{author}\n"; 24:     print "Publisher: $record->{publisher}\n"; 25:     print "ISBN:      $record->{isbn}\n"; 26:     print "Cost:      $record->{price}\n"; 27:     print "Pub Date:  $record->{pubdate}\n"; 28:     print "\n"; 29:     $counter++; 30: }

Line 21 prints the current book number.

Lines 22-27 print the data. All data elements should end up aligned with each other, since we have removed any leading spaces.

Line 28 prints a newline to separate each record.

Line 29 increments $counter so that our book number remains correct.

Line 30 ends the while loop as well as the program.

The output of this program, shown in the following code, looks the best yet. One thing to consider when creating database applications is that when you present data to the user, the data should be in a neat, easy-to-read format.

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

Perl Database Programming
Perl Database Programming
ISBN: 0764549561
EAN: 2147483647
Year: 2001
Pages: 175 © 2008-2017.
If you may any questions please contact us: