Suggested Solution to HW #5 CSCI 4230, Spring 2000

CSCI 4230
Software Tools
Spring 2000
Suugested Solution to Homework #5

(1) For example,

use strict;
use Win32::ODBC;
#
#    h5populateDb.pl
#        This program parses the input file
#        h4glossary.txt and stores the
#        definition of all mortgage terms
#        and store it into the ODBC source demo.
#    It is assumed that all terms and definitions
#    are enclosed by: <b>term: </b>definition</b>
#    after the second HR.
#
@ARGV < 1 && die "Usage h5populatedb.pl filename" ;
open(IN, $ARGV[0]);
#   Open db connection.
my $db;
$db = new Win32::ODBC("DSN=demo") || die qq(cannot open the ODBC DSN demo.\n) if (!$db);

my $body = "";
while ($_ = <IN>) {
chomp;
$body .= "$_ " if ($_);
}
$body =~ s/"?/"/g;
$body =~ s/&?/&/g;
$body =~ s/'/''/g;

#   A hash is really not needed.
my %hash = ();
while ($body =~ /<b>(.*?):*\s*<\/b>(.*?)<\/p>/gi) {
   my $sql = "insert into s2000mortgage (Term, Definition) values('$1', '$2')";
   my $result = $db->Sql($sql);
   if ($result > 0) {
      print "The term $1 cannot be inserted.\n" . $db->Error();
   }
}
exit 0;

(2) For example,

#!/opt/gnu/bin/perl
use strict;
use Win32::ODBC;
$|++;
use CGI qw(:standard);

# Get user parameters.
my $firstchar = param("firstchar");
my $keyword = param("keyword");

print header, start_html("Mortgage Terms");

if (!$firstchar && !$keyword) {
   # No parameter. Print start page.
   print h2("Mortgage Terms"), "\n";
   print "Welcome to the page of mortgage terms. Please use the " .
         "form to search for your mortgage terms.\n";
}
else {
   if ($firstchar) {
      # Search according to first character.
      my $sql = "select Term, Definition from s2000mortgage where Term like '" .
                "$firstchar%'";
      my $termref = get_result($sql);
      my %terms = %$termref;

      print h2("Result"),
            "There are ",
            scalar keys %terms,
            " matched results with first char <font color=blue>$firstchar</font>.",
            p;

      print result_string(\%terms);
   }
   else {
      # Search using keyword.
      my $sql = "select Term, Definition from s2000mortgage where Term like '" .
                "%$keyword%' or Definition like '%$keyword%'";

my $termref = get_result($sql);
my %terms = %$termref;

      print h2("Result"),
            "There are ",
            scalar keys %terms,
            " matched results with the keyword <font color=blue>$keyword</font>.",
            p;

      print result_string(\%terms);
   }
   print p, h3("More Searching");
}

print &search_form;
print end_html;
exit 0;

#### Subroutines.

sub get_result {
my $db;
$db = new Win32::ODBC("DSN=yue") || die qq(cannot open the ODBC DSN demo.\n) if (!$db);

my $sql = shift;

# Execute query.
my $result = $db->Sql($sql);

   my %terms = ();
   while ($db->FetchRow()) {
       $terms{$db->Data("Term")} = $db->Data("Definition")
   }
   \%terms;
}

sub result_string {
   my $termsref = shift;
   my $result = <<__END_RESULT;
<table border=1 cellspacing=2 bgcolor="yellow">
   <tr><td>Terms</td><td>Definitions</td></tr>
__END_RESULT

   foreach (sort keys %$termsref) {
      $result .= "<tr><td>$_</td><td>" . $$termsref{$_}. "</td></tr>\n";
   }
   $result .= "</table>";
   $result;
}

sub search_form {
   #   Return the two forms of searching.
   my $result = <<__END_FORM;
<p>
Alphabetical listing:
<p>
   <a href="h5search.pl?firstchar=a">A</A>  
   <a href="h5search.pl?firstchar=b">B</A>  
   <a href="h5search.pl?firstchar=c">C</A>  
   <a href="h5search.pl?firstchar=d">D</A>  
   <a href="h5search.pl?firstchar=e">E</A>  
   <a href="h5search.pl?firstchar=f">F</A>  
   <a href="h5search.pl?firstchar=g">G</A>  
   <a href="h5search.pl?firstchar=h">H</A>  
   <a href="h5search.pl?firstchar=i">I</A>  
   <a href="h5search.pl?firstchar=j">J</A>  
   <a href="h5search.pl?firstchar=k">K</A>  
   <a href="h5search.pl?firstchar=l">L</A>  
   <a href="h5search.pl?firstchar=m">M</A>  
   <a href="h5search.pl?firstchar=n">N</A>  
   <a href="h5search.pl?firstchar=o">O</A>  
   <a href="h5search.pl?firstchar=p">P</A>  
   <a href="h5search.pl?firstchar=q">Q</A>  
   <a href="h5search.pl?firstchar=r">R</A>  
   <a href="h5search.pl?firstchar=s">S</A>  
   <a href="h5search.pl?firstchar=t">T</A>  
   <a href="h5search.pl?firstchar=u">U</A>  
   <a href="h5search.pl?firstchar=v">V</A>  
   <a href="h5search.pl?firstchar=w">W</A>  
   <a href="h5search.pl?firstchar=x">X</A>  
   <a href="h5search.pl?firstchar=y">Y</A>  
   <a href="h5search.pl?firstchar=z">Z</A>  
<P>
<form method="post">
   Alternatively, please enter keyword for searching:
   <p>
   <input type="text" name="keyword" maxlength="40" size="20">
   <p>
   <input type="submit" value="Search!">  
   <input type="reset">
</form>
__END_FORM
$result;
}