- 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: }
by updated
