Another DB question

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

Dear Perl Monks,

Thank you for your help on my previous quandary! I found the error, but have moved on to others. At this point in my program I'm having trouble inserting retrieved values from the PubMed database back into my Access database. Does the following error message generally refer to a problem with SQL scripts or with the Perl script?

DBD::ODBC::db prepare failed: [Microsoft][ODBC Microsoft Access Driver
+] Syntax error in INSERT INTO statement. (SQL-42000)(DBD: st_prepare/
+SQLPrepare err=-1) at SearchPubMed2.pl line 325.
Can't call method "execute" on an undefined value at SearchPubMed2.pl 
+line 326.
[download]

Here is a segment of the code surrounding that line 326, if it helps:

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;
[download]

Here is the "insert into" variable definition from earlier on in that subroutine:

my $StartInsertSQL = 'insert into SearchResult(';

Thanks for any advice!!

Comment on Another DB question Select or Download Code

Replies are listed 'Best First'.
Re: Another DB question by GrandFather (Saint) on Jul 19, 2007 at 01:16 UTC
Add strictures to the start of your code: `use strict; use warnings;`. That may not help your immediate problem (unless one of the variables you are concatenating together are undefined), but it will save you mega time in the future. Turn on your debugging (look for a commented out `$debug1 = 1;` and uncomment it or add `my $debug1 = 1;` just after the strictures) so you can see the contents of $FinalInsertSQL. If you can't see the error in the SQL, update your node to include that vital piece of information. BTW, there are two errors there - one reported by DBD and one reported by Perl so the answer to your actual question is: "Both!" DWIM is Perl's answer to Gödel	[reply] [d/l] [select]
Re: Another DB question by mr_mischief (Monsignor) on Jul 19, 2007 at 02:34 UTC
To be more precise about the "both" comment GrandFather made, the Perl error is caused by the error DBD::ODBC is reporting. The prepare fails, and that failure returns undef. The execute can't be performed on the statement handle because the statement handle is undefined. DBD::ODBC is, in fact, reporting that there is a syntax error in the INSERT INTO statement and that it got a failure back fro the database server for that reason. Either the syntax of $FinalInsertSQL is wrong in general or one of the variables concatenated to form it is wrong, undefined, or empty. Getting your debugging on is a good idea. The database server doesn't appear to give very helpful error hints other than the fact that it is a syntax error in the INSERT statement.	[reply]