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

Dear Perl Monks,
I am new to Perl, and am currently adapting a previously-made program to my research needs. Basically, it systematically queries the PubMed database with a long list of string queries that it gets from an MS Access database file, and returns information on the papers it finds back into my Access database. I was told that the program was ready to run out of the box, but already I've encountered a couple errors:

Question 1:
In the subroutine that is supposed to grab the queries from my database, I run into this error output...

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver
+] Too few parameters. Expected 2. (SQL-07002)(DBD: st_execute/SQLExec
+ute err=-1) at SearchPubMed2.pl line 178.
Retrieve SearchTerms - Couldn't execute statement: [Microsoft][ODBC Mi
+crosoft Access Driver] Too few parameters. Expected 2. (SQL-07002)(DB
+D: st_execute/SQLExecute err=-1) at SearchPubMed2.pl line 178.

[download]

...on the execute line of this section of code...
my $sth = $dbh->{LongReadLen} = 255;
  $sth = $dbh->prepare($SearchOnSearchTermIDSQL);
  $sth->execute()
    or die "Retrieve SearchTerms - Couldn't execute statement: " . $st
+h->errstr;

[download]

Not really sure what it means; let me know if you need more information to solve this problem.

Question 2:
I don't know if anyone is too familiar with PubMed, but i'm using their E-Fetch utility to retrieve information from papers that come out from my query. Perlide highlights in red this line of text...
my $efetch = "$utils/efetch.fcgi?db=$db&WebEnv=" . $WebEnvID . "&query_key=" . $QueryKey  . "&retmode=xml&rettype=docsum&email=email@university.edu";
...whenever I attempt to run (i'm using a different email address, obviously). Is this concatenation missing any specific variables? I imagined that the "&query_key=" would come from the output of a previous subroutine that uses E-Search to grab that value.

Thanks for any tips you might have!

Replies are listed 'Best First'.
Re: programmatic database querying
by dsheroh (Monsignor) on Jul 18, 2007 at 22:46 UTC
    On #1, if you look at the value of $SearchOnSearchTermIDSQL, you'll probably find that it contains two ? characters. These are "placeholders" for values that need to be filled in when you execute the statement.

    For instance, if you had the SQL statement SELECT * FROM person WHERE first_name = ? AND last_name = ?;, then you would call $sth->execute('John', 'Doe'); to look for John Doe or $sth->execute('Bob', 'Smith'); for Bob Smith.

[reply]
[d/l]
[select]
Re: programmatic database querying
by snopal (Pilgrim) on Jul 18, 2007 at 22:50 UTC

    You didn't supply the SQL (understandable), but I've found that this type of error message means that some bind variables are expected, yet not provided.

    for example: 

    my $sql = qq{SELECT * FROM mytable WHERE mytableid = ?};
my $sth = $dbh->prepare ($sql);
$sth->execute ($mytableid_value);

    [download]
    If the value $mytableid_value is not present (missing), the error message you display would be produced.

[reply]
[d/l]
Re: programmatic database querying
by GrandFather (Saint) on Jul 18, 2007 at 22:50 UTC

    2/ You need to quote the '@'. It causes the array @university to be interpolated into your string, and I bet you don't have an @university array. Note the the whole string could be generated using interpolation (i.e. avoiding the explicit concatenation):

    my $efetch = "$utils/efetch.fcgi?db=$db&WebEnv=$WebEnvID&query_key=$Qu
+eryKey&retmode=xml&rettype=docsum&email=email\@university.edu";

    [download]
    DWIM is Perl's answer to Gödel
[reply]
[d/l]
[select]
      And if you use strict and warnings, you don't have to worry about getting bitten by that sort of accident (at least not without an explicit error or warning).
[reply]

Back to Seekers of Perl Wisdom