in reply to Trying to Debug DB Search

Alright:

I did a little snooping and tweaked my code:

#!/usr/bin/perl -w use strict; use Fcntl; use DBI; use File::Basename; use Text::English; #input Words print "Give a list of words:\n\n"; my $query = <STDIN>; my @words = split /\s*(,|\s+)/, $query; #File Type: print "Give a file type (email, article):\n\n"; my $type = <STDIN>; my %paths = search(\@words, $type); for my $val (keys %paths) { my $file = $paths{$val}; print "$file \n"; } sub search { my($words, $type) = @_; my($dbh, $sth1, $sth2, $rule_append, $word, $file_index); my %matches; #Database Connection: $dbh = DBI->connect( "DBI:mysql:host=localhost;database=test" +, "test", "test", {PrintError=>0,RaiseError=>1}); #Establish Rules for file type: CASE: { if($type = "email") { $rule_append = "where filetype = 'email' "; last CASE; } if($type = "article") { $rule_append = "where filetype = 'article' "; last CASE; } $rule_append = "where filetype = 'article' OR filetype + = 'email' "; }#End of Rule foreach $word(@$words) { my $match; ( $word ) = Text::English::stem( $word ); my @poss; #round 1: Find all file pertaining to word. my $statement1 = "SELECT files FROM catalog WHERE word +s LIKE %".$word."%"; $sth1 = $dbh -> prepare($statement1); $sth1 -> execute(); while(my @val = $sth1 -> fetchrow_array()) { $match = $val[0]; #Get List of files. #Parse Files and Check for matches within. @poss = split(/:/, $match); foreach $file_index(@poss) { $sth2 = $dbh ->prepare("select filenam +e, type from library where filename + = $file_index and".$rule_app +end); $sth2 -> execute(); if(my @val2 = $sth2->fetchrow_array()) { $matches{$val2[0]} = $val2[1] +; } } # All Relevant Files found. }# Cycle through all possible matches. } #End of word search loop return(%matches); #$dbh -> disconnect; }#End of search Function

Now I get these errors:

Undefined subroutine &Text::English::stem called at search.pl line 64, + <STDIN> line 2. Issuing rollback() for database handle being DESTROY'd without explici +t disconnect(), <STDIN> line 2.

However, I know that the English stem module is installed, as I have used it before. Any ideas?

Replies are listed 'Best First'.
Re^2: Trying to Debug DB Search
by japhy (Canon) on Oct 27, 2005 at 01:03 UTC
    One mistake I see in your code is this:
    my @words = split /\s*(,|\s+)/, $query;
    I think you want:
    my @words = split /\s*(?:,|\s+)/, $query;
    If you have capturing parens in your split regex, what they capture gets returned also! You don't want commas and whitespace in your @words array, do you? As for the Text::English problem, I can't figure out why the module would load without incident but stem() not exist.

    Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
    How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart
      Turns out that someone removed the English pm from my server. I put it back. (I'm the programmer, its my party.) Now it fails on the email searches.
        You've got
        if ($type = "email") { ... } if ($type = "article") { ... }
        Those = signs are ASSIGNING to $type, not comparing. You want the 'eq' operator to compare strings for equality.

        Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
        How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart
        Another problem is:
        my $statement1 = "SELECT files FROM catalog WHERE words LIKE %".$word. +"%"; $sth1 = $dbh -> prepare($statement1); $sth1 -> execute();
        I don't think SELECT files FROM catalog WHERE words LIKE %something% is valid SQL. The %something% needs to be quoted. Which leads me to placeholders.
        my $find_files = $dbh->prepare(q{ SELECT files FROM catalog WHERE words LIKE ? }); for my $w (@words) { $find_files->execute("%$w%"); while (my ($file) = $find_files->fetchrow_array) { ... } }
        You run into a similar problem later:
        $sth2 = $dbh ->prepare("select filename, type from library where filename = $file_index and".$rule_append);
        I'm guessing $file_index is a string, not a number, which will lead you to another SQL syntax error. Placeholders are your friends. I would create a hash with the three possible SQL statements, already prepared:
        my %search_library = ( email => $dbh->prepare(q{ SELECT filename, type FROM library WHERE filename = ? AND filetype = 'email' }), article => $dbh->prepare(q{ SELECT filename, type FROM library WHERE filename = ? AND filetype = 'article' }), both => $dbh->prepare(q{ SELECT filename, type FROM library WHERE filename = ? AND (filetype = 'email' OR filetype = 'article') }), );
        Now you can just do:
        for my $w (@words) { $find_files->execute("%$w%"); while (my ($file) = $find_files->fetchrow_array) { for my $f (split /:/, $file) { $search_library{$type}->execute($f); if (my @rec = $search_library{$type}->fetchrow_array) { $match{$rec[0]} = $rec[1]; } } } }
        Another comment is that I don't know why you're fetching 'filename' from the library table when you're using it to find the columns, unless you're looking to get the normalized value of it (that is, the value as it appears in the database, since your database might be case insensitive).

        Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
        How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart