Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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.


In reply to Updating my database...or not by molly

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Domain Nodelet?
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this?Last hourOther CB clients
    Other Users?
    Others lurking in the Monastery: (5)
    As of 2024-07-15 07:58 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?
      erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.