Without a title

8
Database Access

Database Access

DBI
Using DBI to Solve a Real World Problem
Open Database Connectivity (ODBC)

As the Web makes inroads into corporate intranets, one of the primary uses of Web technology is providing an interface to data stored in corporate databases. One of the strengths of the Web in these environments lies in its ability to provide a platform independent, open interface to these corporate databases. Database interfaces also play a key role in online shopping, automated order processing, and information gathering and distribution applications. In all of these applications, using a professional UNIX-based database package such as Oracle, Sybase, or Informix (to name a few) is almost always the best alternative. These packages provide a powerful, configurable, and expandable solution. Unfortunately, manufacturers of these professional database packages have developed differing proprietary interfaces for their databases. Obviously, this makes life difficult for programmers who want to write their own Web-based database front ends to access data from these various databases.

In this chapter, we will look at how Perl5 and a set of modules provide a very elegant solution to this problem. You will be introduced to a set of Perl5 modules called DBI that provide a consistent database interface (API) independent of the actual database being used. We will also look at Microsoft's Open Database Connectivity Programming Interface, or ODBC API. ODBC is quickly being adopted by most, if not all, major database server vendors as the standard database interface. Once all database vendors add the ODBC interface to their previously proprietary interfaces, solutions like DBI will not be necessary.

Using Perl as a database interface, or front end, through the Web has many advantages. This approach is very flexible (there are few or no constraints on what you can do) and very easy to experiment with.

NOTE:

Is investing in a professional database package really worth it? Absolutely. With standard APIs such as DBI and ODBC emerging, investing your time developing your application to utilize a professional database makes sense. Portability from database to database has never been easier. Investing your resources in a professional database also makes the transition from a small enterprise to a large enterprise easier. Take, for example, a small ISP building an online catalog. In a matter of months, that ISP could go from taking 100 to 100,000 hits a day. If the ISP initially developed its online catalog on an entry-level UNIX-based Silicon Graphics server using an Oracle database, the migration to a faster SGI could be made in a matter of hours. However, if the ISP chose to make a smaller initial investment by developing their catalog on an Intel-based Linux or NT server, hacking together their own database, scalability of the software and hardware architecture becomes a serious issue.

DBI

The DBI:: module is a Perl5 module written by Tim Bunce that provides a common Perl interface to various database engines. DBI will allow your Perl CGI script to manipulate data contained in a database without regard for the actual database engine being used to service the requests. In other words, once you understand the syntax of the different DBI functions used to access a
database, those same functions allow you to access data from an Oracle, Sybase, Informix, or
whatever database precisely the same way, even though the different database engines have dif-
ferent 3GL API layers. The API defined by DBI will work on all supported database types.

A good example of this is a Web-based customer support application. Using DBI, your CGI could access customer information in an Oracle database, product information in an Informix database, and order status information in a Sybase database. DBI provides a single standard API that allows you to access this information from all of these databases from within the same CGI script.

DBI can be obtained from the Comprehensive Perl Archive Network (CPAN) at

http://www.perl.com/CPAN/modules/dbperl

or its source at

ftp://ftp.demon.co.uk/pub/perl/db/DBI

DBI implements support for the individual database engines through driver modules called DBDs. DBDs are currently available for most major database engines. Table 8.1 lists the current DBD interfaces for DBI as of this writing. The latest information on currently available modules is kept at the following URL:

http://www.hermetica.com/technologia/DBI/DBD/index.html

The DBD module for Oracle can be obtained from CPAN or its source at

ftp://ftp.demon.co.uk/pub/perl/db/DBD/Oracle

Table 8.1. DBD interfaces available for DBI.

Module Name Database Required Author

DBD::Oracle Oracle 6 or 7 Tim Bruce

DBD::Sybase Sybase Michael Peppler

DBD::Informix Informix Jonathan Leffler

DBD::mSQL mSQL 1.0.7+ Alligator Descartes

DBD::Empress Empress 6.8 Francis Jones

DBD::Fulcrum Fulcrum SearchServer 2/3 Davide Migliavacca

DBD:DB2 DB2 2.1+ Mike Moran

DBD::Interbase Interbase Bill Karwin

DBD::Quickbase NeXTStep Quickbase Ben Lindstrom

DBD::Ingres Ingres Henrik Tougaard

Using DBI to Solve a Real World Problem

Thomas E. White, a computer systems analyst at Mississippi State University, put together a nice example using DBI to set up a WWW "browse and search" application. In this example, DBI is used with DBD::mSQL to maintain, view, browse, and search an A/V library database.

The following software was configured on a Sun server for this example: Solaris 2.5

MiniSQL version 1.0.16

Perl related software

Version 5.003

CGI.pm-2.21

DBI.pm-0.71

DBD-mSQL-0.60pl10 First, the following raw records were saved to a text file, as shown in Listing 8.1.

Listing 8.1. Records to be imported into database

ABC OF GOLF GREEN CONSTRUCTION



F0A4-0001.00

Film

U.S. Golf Association, Clemson University, 1970

1 Reel.  16mm.  Sound.  Color.  20 min.

A detailed, step-by-step process of golf green construction. Includes

planning  and development, 

preparation of grade, drainage and soil mixtures. Very good film.

.

College

Adult









ABC'S OF DECISION MAKING



F0C6-0001.00

Film

Creative Media, 1975

1 Reel.  16mm.  Sound.  Color.  30 min.

Making a decision is often an unpleasant task. The aspects of decision making to

insure that they will be effective are covered in this film. 

A leader's guide is included.

.

College

Adult









ALL SUCCESSFUL PEOPLE HAVE IT



F0C6-0002.00

Film

Motivational Institute, 1980

1 Reel.  16mm.  Sound.  Color.  28 min.

Narrated by Marilyn Van Derbur, this film helps teens and adults be "somebody" 

in their own eyes. It shows how to accomplish something to be proud of - 

self-confidence and a positive self-image. It stresses the importance of having  

a goal and a logical, well-organized plan to carry  it out.

Rated high by 4-H youth 

who previewed it.

.

Junior High, grades 7-9

Senior High, grades 10-12

College

Adult

Importing Data into a Database with DBI

Listing 8.2 uses DBI to import the raw data contained in Listing 8.1 into an mSQL database. The mSQL databases that are available (such as AV_LIBRARY, in this example) are managed by MiniSQL, a database manager running on the server. There are five main steps required to add the records in Listing 8.1 to the database.

line 11 The name of the file containing the raw formatted data is read as an argument, and a new filehandle is created.
line 12 The driver (DBD) for mSQL is installed.
line 13 DBI is instructed to connect to the database named AV_LIBRARY.
line 93 The dbh IMPORT function of DBI is used to place the data into the database.
line 96 Finally, the database connection is closed.

Listing 8.2. Program that imports data into an mSQL database

1:   #!/usr/local/bin/perl

2:   #

3:   #  Load the catalog table in the AV_LIBRARY database

4:   #

5:   #    by Tom White

6:   #

7:   #    $Id: load_av.pl,v 1.3 1996/08/27 22:10:36 avlibrar Exp avlibrar $

8:  

9:   use DBI;

10:  

11: open (STDIN, "<$ARGV[0]") || die "Could not open file: $!";

12: $drh = DBI->install_driver( `mSQL' ) || die "Could not install: $DBI::errstr";

13: $dbh = $drh->connect( ``, `AV_LIBRARY' )

14:           || die "Could not connect: $DBI::errstr";

15:  $dbh->do( `drop table catalog' );

16:  $dbh->do(

17:    `create table catalog (

18:         call_num        char(12)   primary key,

19:         title           char(80)   not null,

20:         subtitle        char(80),

21:         type            char(5)    not null,

22:         description     char(50)   not null,

23:         publisher       char(60)   not null,

24:         narrative       char(1000),

25:       #Audience flags:  NULL means no; 1, yes.

26:         kinder          char(1),

27:         primer          char(1),

28:         element         char(1),

29:         junior          char(1),

30:         senior          char(1),

31:         college         char(1),

32:         adult           char(1)

33:     )'

34:   ) || die "Could not create table: $DBI::errstr";

35:

36:  while (! eof STDIN) {

37:    # build a record from the input text file

38:    $x = &nextline;

39:    next unless $x;

40:    print "Not ALLCAPS at $lnum\n" if ($x =~ m/[a-z]/);

41:    $r{`title'} = $x;

42:    $count++;

43:    $r{`subtitle'} = &nextline;

44:    $r{`call_num'} = &nextline;

45:    $r{`type'} = &nextline;

46:    $r{`publisher'} = &nextline;

47:    $r{`description'} = &nextline;

48:    $r{`narrative'} = ``;

49:    $x = &nextline;

50:    while ( $x ne `.' ) {

51:      $r{`narrative'} = $r{`narrative'} . ` ` . $x;

52:      $x = &nextline;

53:    }

54:    $r{`kinder'} = $r{`primer'} = $r{`element'} = $r{`junior'}

55:      = $r{`senior'} = $r{`college'} = $r{`adult'} = 0;

56:    $x = &nextline;

57:    while ($x) {

58:      if ($x eq `Kindergarten, ages 3-5') {

59:        $r{`kinder'} = 1;

60:      } elsif ($x eq `Primary, grades 1-3') {

61:        $r{`primer'} = 1;

62:      } elsif ($x eq `Elementary, grades 4-6') {

63:        $r{`element'} = 1;

64:      } elsif ($x eq `Junior High, grades 7-9') {

65:        $r{`junior'} = 1;

66:      } elsif ($x eq `Senior High, grades 10-12') {

67:        $r{`senior'} = 1;

68:      } elsif ($x eq `College') {

69:        $r{`college'} = 1;

70:      } elsif ($x eq `Adult') {

71:        $r{`adult'} = 1;

72:      }

73:      $x = &nextline;

74:    }

75:    # now build strings for the INSERT

76:    $ks = ""; $vs = "";

77:    foreach $k (keys %r) {

78:      $v = $r{$k};

79:      $v =~ s/\\/\\\\/g;            #escape backslash

80:      $v =~ s/'/\\'/g;              #escape single quote

81:      if ($v) {

82:        $ks = $ks . " $k,";

83:        $vs = $vs . " \'$v\',";

84:      }

85:      # keep up with maximum field lengths

86:      $l = length $r{$k};

87:      if ( $l > $max{$k} ) {

88:        $max{$k} = $l;

89:        $ex{$k}  = $r{$k};

90:      }

91:    }

92:    chop $ks; chop $vs;

93:    $dbh->do( "INSERT INTO catalog ( $ks  ) VALUES ( $vs )" )

94:       || die "Line $.:Could not add row $DBI::errstr\n";

95:  }



96:  $dbh->disconnect || die "Could not disconnect: $DBI::errstr";



97:  foreach $k (sort keys %max) {

98:    printf "%-12s %4d :%s:\n", $k, $max{$k}, $ex{$k};

99:  }



100: print "\nFound $count records\n";



101: exit;



102: sub nextline {

103:   my $line;

104:   $lnum++;

105:   $line = <STDIN>;

106:   $line =~ s/\s+$//;

107:   return $line;

108: }

Create an HTML Index from a Database

Listing 8.3 uses DBI to query the database and extract the call_num, title, and subtitle fields. As it extracts the data from the database, the fields are embedded into HTML to create an index of all titles currently in the A/V library. Figure 8.1 shows the HTML output generated by this program. There are four main steps in the program in Listing 8.3.

line 11 The driver (DBD) for mSQL is installed.
line 12 DBI is instructed to connect to the database named AV_LIBRARY.
line 14 After you connect to the database, a prepare function must be invoked to set up return values before the execute statement is invoked.
line 17 The execute statement pulls the values out of the database, and in this example embeds them in an HTML list.

Listing 8.3. Program that writes HTML from the database

1:   #!/usr/local/bin/perl

2:   #

3:   #  Build catalog.html from the catalog table in the AV_LIBRARY database

4:   #

5:   #    by Tom White

6:   #

7:   #    $Id: build_ful.pl,v 1.2 1996/08/28 13:39:51 avlibrar Exp avlibrar $



8:   use DBI;



9:   $target = "/home1/ces/avlibrar/public_html/catalog.html";

10:  open (NEWF,">$target.new") || die "Could not open $target: $!";



11:  $drh = DBI->install_driver( `mSQL' ) || die "Could not install: $DBI::errstr";

12:  $dbh = $drh->connect( ``, `AV_LIBRARY' )

13:            || die "Could not connect: $DBI::errstr";



14:  $sth = $dbh->prepare( "SELECT call_num, title, subtitle FROM catalog" )

15:            || die "Could not start select: $DBI::errstr";



16:  &header;



17:  $sth->execute || die "Could not select: $DBI::errstr";

18:  while ( ($call_num, $title, $subtitle) = $sth->fetchrow ) {

19:    print NEWF qq!  <li><tt><a href="display.cgi?cn=$call_num">$call_num</a></tt>

20:             $title\n!;

21:    if ( $subtitle ) {

22:      print NEWF " $subtitle\n";

23:    }

24:  }

25:  $sth->finish || die "Could not finish select: $DBI::errstr";



26:  &footer;





27:  # $dbh->disconnect || die "Disconnect failed: $DBI::errstr";



28:  close NEWF;

29:  rename "$target.new", $target || die "Could not rename: $!";



30:  exit;



31:  sub header {

32:    print NEWF <<EOD;

33:  <HTML>



34:  <HEAD>

35:    <TITLE>MCES's Audiovisual Reference Room - Full Listing</TITLE>

36:    <LINK REV="MADE" HREF="mailto:avlibrar\@ces.msstate.edu">

37:    <META NAME="MarkUp" CONTENT="Tom White">

38:  </HEAD>



39:  <BODY>



40:  <h1><img src="/pics/ces-b1mt.gif" width=499 height=53

41:           alt="Cooperative Extension Service -

42:                Mississippi State University">

43:  </h1>



44:  <h2>

45:    Audiovisual Reference Room

46:  </h2>

47:  <h3>

48:    Full Catalog Listing

49:  </h3>



50:  <ol>

51:  EOD

52:  }



53:  sub footer {

54:    my @loctime;

55:    my $lastupdate;

56:    @loctime = localtime;

57:    $lastupdate = sprintf("%02d-%02d-%02d", ($loctime[4]+1),

58:                    ($loctime[3]), ($loctime[5]));

59:    print NEWF <<EOD;

60:  </ol>



61:  <HR>

62:    [<a href="./">Audiovisual Reference Room</a>]

63:    <br>

64:    [<a href="/ces.html">Cooperative Extension</a>]

65:    [<a href="http://www.msstate.edu/">Mississippi State</a>]

66:    [<a href="http://www.msstate.edu/web/search.htm">Search MSU's Web</a>]

67:    <br>

68:    <FONT SIZE="-1">

69:    For information about this page, contact

70:       <a href="mailto:avlibrar\@ces.msstate.edu">avlibrar\@ces.msstate.edu</A>.

71:    <br>

72:    <b>Last modified:</b> $lastupdate

73:    <br>

74:    <A HREF="http://www.msstate.edu/web/disclaim.htm">Mississippi State University is 

75:  an equal opportunity institution.</A>

76:    </FONT>



77:  </BODY>



78:  </HTML>

79:  EOD

80:  }

Figure 8.1. Output from program in Listing 8.3.

Build HTML from Data on a Single Item in the Database

The program in Listing 8.4 uses DBI to query the database about a specific item and to extract the various information provided in the raw data we input earlier into the database. The data is formatted into HTML output in a similar fashion as in Listing 8.3. Figure 8.2 shows the HTML output generated by this program. Notice CGI.pm is used here to parse the URI encoded data containing the call_num of the item to look up. There are four main steps in the program in Listing 8.4.

line 20 The driver (DBD) for mSQL is installed.
line 21 DBI is instructed to connect to the database named AV_LIBRARY.
line 25 The prepare statement is invoked.
line 30 The execute statement is invoked.

Listing 8.4. Program that writes HTML from data

about a single item in the database.

1:   #!/usr/local/bin/perl

2:   #

3:   #  Display an item from the catalog table in the AV_LIBRARY database

4:   #

5:   #    by Tom White

6:   #

7:   #    $Id: display.cgi,v 1.4 1996/08/28 13:42:06 avlibrar Exp avlibrar $



8:   open (STDERR, ">&STDOUT");

9:   select STDERR; $| = 1;

10:  select STDOUT; $| = 1;



11:  use CGI;

12:  $ENV{`PATH'} = `/usr/local/bin:/usr/ucb:/usr/bin:/usr/ccs/bin:/usr/sbin';

13:  $ENV{`MANPATH'} =`/usr/local/man:/usr/man';

14:  $ENV{`PAGER'} = `/usr/bin/cat';

15:  $ENV{`SHELL'} = `/bin/sh';

16:  $ENV{`IFS'} = ``;



17:  $query = new CGI;

18:  &pheader;



19:  use DBI;



20:  $drh = DBI->install_driver( `mSQL' ) || die "Could not install: $DBI::errstr";

21:  $dbh = $drh->connect( ``, `AV_LIBRARY' )

22:            || die "Could not connect: $DBI::errstr";



23:  $call_num = uc $query->param(`cn');

24:  $sstring = "call_num = \'$call_num\'";

25:  $sth = $dbh->prepare( "SELECT title, subtitle, type, description,

26:                                publisher, narrative, kinder, primer,

27:                                element, junior, senior, college, adult

28:                         FROM catalog WHERE $sstring" )

29:           || die "Could not start select: $DBI::errstr";

30:  $sth->execute || die "Could not select: $DBI::errstr";

31:  print "<dt><b>Call Number</b> <dd>$call_num\n";

32:  if ( ($title, $subtitle, $type, $description, $publisher,

33:        $narrative, $kinder, $primer, $element, $junior,

34:        $senior, $college, $adult) = $sth->fetchrow ) {

35:      print "<dt><b>Title</b> <dd>$title\n";

36:      if ( $subtitle ) {

37:        print "<dt><b>Subtitle</b> <dd>$subtitle\n";

38:      }

39:      print "<dt><b>Medium</b> <dd>$type";

40:      if ( $description ) {

41:        print " ($description)";

42:      }

43:      print "\n";

44:      print "<dt><b>Publisher</b> <dd>$publisher\n";

45:      print "<dt><b>Narrative</b> <dd>$narrative\n";

46:      print "<dt><b>Audience</b>";

47:      print " <dd>Kindergarten (ages 3-5)" if ( $kinder );

48:      print " <dd>Primary (grades 1-3)" if ( $primer );

49:      print " <dd>Elementary (grades 4-6)" if ( $element );

50:      print " <dd>Junior High (grades 7-9)" if ( $junior );

51:      print " <dd>Senior High (grades 10-12)" if ( $senior );

52:      print " <dd>College" if ( $college );

53:      print " <dd>Adult" if ( $adult );

54:  } else {

55:      print "<br><b>is NOT on file.</b>";

56:  }

57:  print "\n";

58:  $sth->finish;



59:  &pfooter;



60:  exit;



61:  sub pheader {

62:    print $query->header;

63:    print <<EOD;

64:  <HTML>



65:  <HEAD>

66:    <TITLE>MCES's Audiovisual Reference Room - Item Listing</TITLE>

67:    <LINK REV="MADE" HREF="mailto:avlibrar\@ces.msstate.edu">

68:    <META NAME="MarkUp" CONTENT="Tom White">

69:  </HEAD>



70:  <BODY>



71:  <h1><img src="/pics/ces-b1mt.gif" width=499 height=53

72:           alt="Cooperative Extension Service -

73:                Mississippi State University">

74:  </h1>

75:  <h2>

76:    Audiovisual Reference Room

77:  </h2>

78:  <h3>

79:    Item Listing

80:  </h3>

81:  <dl>

82:  EOD

83:  }



84:  sub pfooter {

85:    print <<EOD;

86:  </dl>

87:  <HR>

88:    [<a href="./">Audiovisual Reference Room</a>]

89:    <br>

90:    [<a href="/ces.html">Cooperative Extension</a>]

91:    [<a href="http://www.msstate.edu/">Mississippi State</a>]

92:    [<a href="http://www.msstate.edu/web/search.htm">Search MSU's Web</a>]

93:    <br>

94:    <FONT SIZE="-1">

95:    For information about this page, contact

96:       <a href="mailto:avlibrar\@ces.msstate.edu">avlibrar\@ces.msstate.edu</A>.

97:    <br>

98:    <b>Last modified:</b> 08-10-96

99:    <br>

100:   <A HREF="http://www.msstate.edu/web/disclaim.htm">Mississippi State 

       University is an equal opportunity institution.</A>

101:   </FONT>

102:  </BODY>

103:  </HTML>

104:  EOD

105: }

Figure 8.2. Output from Listing 8.4.

Searching the Database for Keywords and Outputting Results

Listing 8.5 uses DBI to query the database about an item based on parameters specified in an HTML form. The data returned is formatted into HTML output in a similar fashion as in Listing 8.3. Figures 8.3 and 8.4 show the HTML output generated by this program. If no input is sent to the CGI, only the search form is displayed; however, if URI encoded data is detected by CGI.pm in the URI, that data is operated on, the database is queried, and search results are displayed. Notice extensive use of CGI.pm in this example.

Listing 8.5. Searching the database for keywords

1:   #!/usr/local/bin/perl

2:   #

3:   #  Search the AV Reference Room

4:   #

5:   #  by Tom White

6:   #

7:   #  $Id: search.cgi,v 1.6 1996/08/28 13:42:13 avlibrar Exp avlibrar $

8:   #



9:   open (STDERR, ">&STDOUT");

10:  select STDERR; $| = 1;

11:  select STDOUT; $| = 1;



12:  use CGI;

13:  $ENV{`PATH'} = `/usr/local/bin:/usr/ucb:/usr/bin:/usr/ccs/bin:/usr/sbin';

14:  $ENV{`MANPATH'} =`/usr/local/man:/usr/man';

15:  $ENV{`PAGER'} = `/usr/bin/cat';

16:  $ENV{`SHELL'} = `/bin/sh';

17:  $ENV{`IFS'} = ``;



18:  $query = new CGI;



19:  # build a WHERE clause for mSQL

20:  # note: mSQL evaluates compound conditions strictly left-to-right

21:  $where_string = "";

22:  $conjunc = "WHERE ";

23:  $fstring = $query->param(`string');

24:  if ( $fstring ) {

25:    $fstring = &prep_regex ($fstring);

26:    $where_string = $where_string . $conjunc .

27:      "title LIKE $fstring OR subtitle LIKE $fstring OR narrative LIKE $fstring";

28:    $conjunc = " AND ";

29:  }

30:  $fmedium = $query->param(`medium');

31:  if ( $fmedium && $fmedium ne `Any' ) {

32:    $where_string = $where_string . $conjunc . "type = `$fmedium'";

33:    $conjunc = " AND ";

34:  }

35:  $fpuber = $query->param(`puber');

36:  if ( $fpuber ) {

37:    $fpuber = &prep_regex ($fpuber);

38:    $where_string = $where_string . $conjunc . "publisher LIKE $fpuber";

39:    $conjunc = " AND ";

40:  }

41:  $faud = $query->param(`aud');

42:  if ( $faud && $faud ne `Any' ) {

43:    $where_string = $where_string . $conjunc . " $faud <> NULL";

44:    $conjunc = " AND ";

45:  }





46:  if ( $where_string ) {

47:    $hit_limit = $query->param(`hitlimit') || "25";

48:    &pheader;

49:    &search;

50:    &ptrailer;

51:  } else {

52:    &pheader;

53:    &ptrailer;

54:  }

55:  exit;





56:  #prepare a search string for use in mSQL LIKE

57:  sub prep_regex {

58:    $_[0] =~ s/\[/\[\[\]/g;

59:    $_[0] =~ s/\\/\[\\\\\\\\\]/g;           # no good - backslash breaks LIKE

60:    $_[0] =~ s/([\$%\(\)\?\^|])/\[$1\]/g;

61:    $_[0] =~ s/\'/\\\'/g;

62:    $_[0] =~ s/_/\\\\_/g;

63:    $_[0] =~ s/([A-Za-z])/\[\l$1\u$1\]/g;

64:    return "`%" . $_[0] . "%'";

65:  }





66:  #search the database

67:  sub search {

68:    use DBI;

69:    $drh = DBI->install_driver( `mSQL' )

70:            || die "Could not install: $DBI::errstr";

71:    $dbh = $drh->connect( ``, `AV_LIBRARY' )

72:            || die "Could not connect: $DBI::errstr";

73:    $sth = $dbh->prepare( "SELECT call_num, title, subtitle FROM catalog

74:                           $where_string LIMIT $hit_limit" )

75:            || die "Could not start select: $DBI::errstr";

76:    $sth->execute || die "Could not select: $DBI::errstr";

77:    print "<b>Search Results</b>\n<ol>\n ";

78:    while ( ($call_num, $title, $subtitle) = $sth->fetchrow ) {

79:      print qq!  <li><tt><a href="display.cgi?cn=$call_num">$call_num</a></tt>

80:               $title\n!;

81:      if ( $subtitle ) {

82:        print " $subtitle\n";

83:      }

84:      $hits++;

85:    }

86:    print "</ol>\n";

87:    if ( ! $hits ) {

88:      print "<b>No</b> matches\n";

89:    } elsif ( $hits >= $hit_limit ) {

90:      print "Search stopped after first <b>$hits</b> matches.\n";

91:    }

92:    $sth->finish || die "Could not finish select: $DBI::errstr";

93:  }





94:  #send the header

95:  sub pheader {

96:    print $query->header;

97:    print <<EOD;

98:  <HTML>



99:  <HEAD>

100:   <TITLE>MCES's Audiovisual Reference Room - Search</TITLE>

101:    <LINK REV="MADE" HREF="mailto:avlibrar\@ces.msstate.edu">

102:    <META NAME="MarkUp" CONTENT="Tom White">

103:  </HEAD>



104:  <BODY>



105:  <h1><img src="/pics/ces-b1mt.gif" width=499 height=53

106:           alt="Cooperative Extension Service -

107:               Mississippi State University">

108: </h1>



109: <h2>

110:   Audiovisual Reference Room

111: </h2>



112: <h3>

113:   Search

114: </h3>



115: EOD

116: print $query->startform(`GET', `search.cgi', `""`);

117: print "<b>String:</b> ";

118: print $query->textfield(-name=>`string', -default=>``, -size=>15,

119:                         -maxlength=>30);

120: print "    (Must appear in either title or narrative.)\n";

121: print "<br><b>Medium:</b> ";

122: print $query->popup_menu(-name=>`medium', -default=>`Any',

123:                          -values=>[`Any','Film','Slide','Audio','Video']);

124: print "\n<br><b>Publisher:</b> ";

125: print $query->textfield(-name=>`puber', -default=>``, -size=>15,

126:                         -maxlength=>15);

127: print "    (Must appear under Publisher.)\n";

128: print "<br><b>Audience:</b> ";

129: print $query->popup_menu(-name=>`aud', -default=>`Any',

130:       -values=>[`Any','kinder','primer','element','junior',

131:                 `senior','college','adult'],

132:       -labels=>{`Any'=>,'Any',

133:                 `kinder'=>,'Kindergarten (ages 3-5)',

134:                 `primer'=>,'Primary (grades 1-3)',

135:                 `element'=>,'Elementary (grades 4-6)',

136:                 `junior'=>,'Junior High (grades 7-9)',

137:                 `senior'=>,'Senior High (grades 10-12)',

138:                 `college'=>,'College',

139:                 `adult'=>`Adult'});

140: print "\n<br>\n";

141: print $query->submit(`Start Search'), "\n";

142: print "Stop search after ";

143: print $query->popup_menu(-name=>`hitlimit', -default=>`25',

144:                          -values=>[`10','25','50','100','9999']);

145: print " matches\n";

146: print $query->endform, "\n";

147: }







148: #send the trailer

149: sub ptrailer {

150:   print <<EOD;



151: <HR>

152:   [<a href="./">Audiovisual Reference Room</a>]

153:   <br>

154:   [<a href="/ces.html">Cooperative Extension</a>]

155:   [<a href="http://www.msstate.edu/">Mississippi State</a>]

156:   [<a href="http://www.msstate.edu/web/search.htm">Search MSU's Web</a>]

157:   <br>

158:   <FONT SIZE="-1">

159:   For information about this page, contact

160:      <a href="mailto:avlibrar\@ces.msstate.edu">avlibrar\@ces.msstate.edu</a>.

161:   <br>

162:   <b>Last modified:</b> 08-06-96

163:   <br>

164:   <A HREF="http://www.msstate.edu/web/disclaim.htm">Mississippi State University 

       is an equal opportunity institution.</A>

165:   </FONT>



166: </BODY>



167: </HTML>

168: EOD

169: }

Figure 8.3. Output from Listing 8.5.

Figure 8.4. More output from Listing 8.5.

Open Database Connectivity (ODBC)

Microsoft's Open Database Connectivity API attempts to define a standard SQL syntax common to all database engines. The standard is based on the SQL-Access group's CLI, which has been adopted as an international standard. Microsoft has developed a call-level interface based directly on the SQL CLI. ODBC will make the development of generic, scalable client applications such as CGI/Perl5 interfaces less difficult once drivers and interfaces are more universally available.

ODBC is a component of Microsoft's Windows Open Services Architecture (WOSA). Applications can call a generic API, which is routed to the appropriate database-specific driver.

Database vendors are currently adopting ODBC as the native CLI for their database engines. ODBC drivers are currently being included in most commercial database packages. An ODBC driver manager is included in current releases of Sun's Solaris OS. ODBC is quickly becoming the standard. ODBC does not replace DBI, however. Currently, DBI's DBM modules provide access to the differing proprietary APIs implemented by the different database vendors. The DBI module itself provides a Perl5 object oriented interface to the database through the DBM modules. A Perl interface is still necessary to the ODBC API. Tim Bruce, the author of DBI, is currently rewriting DBI to fully support ODBC. The new DBI will include a new low-level ODBC API interface, along with high-level methods similar to the current DBI interface. The latest information on the development of DBI can be found at

http://www.hermetica.com/technologia/DBI/

There is currently a Perl5 ODBC module for the 32-bit Windows architectures, including Windows 95 and Windows NT for Intel and DEC Alpha. The module is currently being ported to Macintosh and various flavors of UNIX and should be available on these platforms by the time you read this. The module is called Win32::ODBC and is being written by Dave Roth. Dave's Win32 ODBC.pm module is based on the original ODBC.PM code written by Dan DeMaggio.

Win32::ODBC may be obtained from the following URL:

http://www.perl.com/CPAN/authors/Dave_Roth/

Currently, in order to use this module, you must be running on some 32-bit Windows architecture such as NT 3.51, 4.0, or Windows 95. You must install version 3.0 or greater of Microsoft's ODBC interface drivers and have Win32 Perl installed and working. The latest version of the ODBC drivers can be obtained directly from Microsoft at the following URL:

http://www.microsoft.com/kb/softlib/mslfiles/WX1220.EXE

Win32::ODBC is currently not compatible with the DBI interface specification. From a Perl interface perspective, the syntax of its interface is different, but the idea is the same: You interface the database through the Win32::ODBC module from your Perl code. I will not go into great detail about the Win32::ODBC module, as it is currently available only on the Windows
platform. Questions about the module are addressed on a FAQ located at the following URL:

http://WWW.ROTH.NET/ODBC/ODBCFAQ.HTM

Summary

In this chapter, we have seen how DBI can be used to greatly simplify building a Web-based database interface. Now that database applications such as Oracle, Sybase, and, on a somewhat different scale, Microsoft Access are being more widely used, Perl provides a great way to access data via the Web. Now, a small company, with a home-brewed Microsoft Access database, can easily write a Web interface to their data. With DBI and ODBC, the database could be moved from Access to Oracle without having to completely rewrite the Web interface.

Module Name	Database Required	Author
DBD::Oracle	Oracle 6 or 7	Tim Bruce
DBD::Sybase	Sybase	Michael Peppler
DBD::Informix	Informix	Jonathan Leffler
DBD::mSQL	mSQL 1.0.7+	Alligator Descartes
DBD::Empress	Empress 6.8	Francis Jones
DBD::Fulcrum	Fulcrum SearchServer 2/3	Davide Migliavacca
DBD:DB2	DB2 2.1+	Mike Moran
DBD::Interbase	Interbase	Bill Karwin
DBD::Quickbase	NeXTStep Quickbase	Ben Lindstrom
DBD::Ingres	Ingres	Henrik Tougaard

8 Database Access