http://www.perlmonks.org?node_id=630411

molly has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am very frustrated with a subroutine wherein I am attempting to update a table in my ms access db. Here is the string of code i keep getting error messages on:

sub SearchResultUpdateExpertiseDB { # update SearchResult with info re +trieved from PubMed search my (%InsertComponent) = %{$_[0]}; # dereference the reference to the + %InsertComponent hash my $FinalInsertSQL = ''; my $StartInsertSQL = 'insert into SearchResult('; my $InsertColumnSQL = ''; my $InsertValuesSQL = ' values('; my $counter=0; my $flag=0; my $AtLeastOneValueToInsert=0; my $sth; foreach my $key (keys (%InsertComponent)) { if ($SearchResultsColumnType{$key} eq 'N') { # this is a number - +-> no need for quotes $InsertColumnSQL = "$InsertColumnSQL" . "$key"; $InsertValuesSQL = "$InsertValuesSQL" . "$InsertComponent{$key} +"; $flag = 1; $AtLeastOneValueToInsert=1; if ($debug) {print "SearchResultUpdate LOOP1: Column = $key Colu +mnType = $SearchResultsColumnType{$key} InsertComponent{$key} = $Inse +rtComponent{$key}\n"}; # debug }; if ($SearchResultsColumnType{$key} eq 'T') { # this is text --> ne +ed quotes $InsertColumnSQL = "$InsertColumnSQL" . "$key"; $InsertComponent{$key} =~ s|\'|\'\'|g; # check for single quote +in the string so that it can be escaped for inserting into DB $InsertValuesSQL = "$InsertValuesSQL" . '\'' . "$InsertComponen +t{$key}" . '\''; $flag = 1; $AtLeastOneValueToInsert=1; if ($debug) {print "SearchResultUpdate LOOP2: Column = $key Colu +mnType = $SearchResultsColumnType{$key} InsertComponent{$key} = $Inse +rtComponent{$key}\n"}; # debug }; if (($counter < ((keys (%InsertComponent)))-1) && ($flag == 1)) { $InsertColumnSQL = "$InsertColumnSQL" . ','; $InsertValuesSQL = "$InsertValuesSQL" . ','; $flag = 0; } $counter++; } # close the foreach $InsertColumnSQL = "$InsertColumnSQL" . ')'; # close this part of th +e insert statement $InsertValuesSQL = "$InsertValuesSQL" . ')'; # close this part of th +e insert statement if ($AtLeastOneValueToInsert == 1) { $FinalInsertSQL = $StartInsertSQL . $InsertColumnSQL . $InsertValu +esSQL; if ($debug1 == 1) {print "now updating SearchResults table\n"}; if ($debug == 1) { print "Updating SearchResult: FinalSQL = $FinalInsertSQL \n\n"; +# debug } $sth = $dbh->prepare($FinalInsertSQL); $sth->execute() # Execute the insert or die "Couldn't execute statement: " . $sth->errstr; $sth->finish;

I keep getting errors about uninitialized values, when clearly all my variables are defined at the beginning of the subroutine. The error output also claims that there is a syntax error in my "INSERT INTO" SQL statement. This program worked fine in earlier runs, and though no portions of the code have changed since( beyond adjusting input into the program in a virtually unrelated subroutine), I randomly am getting these errors. I had similar error messages earlier on in the debugging process, and it appeared to take care of itself once I dealt with (seemingly) unrelated formatting issues in my Access db. Are these kind of errors common as false flags that point toward other errors that anyone might be aware of? Any help would be amazing. Thanks.