1. line 11 The name of the file containing the raw formatted data is read as an argument, and a new filehandle is created.
2. line 12 The driver (DBD) for mSQL is installed.
3. line 13 DBI is instructed to connect to the database named AV_LIBRARY.
4. line 93 The dbh IMPORT function of DBI is used to place the data into the database.
5. 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: }