$sth->execute error

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

Dear Monks, Trying to execute the following SQL statement throws this error message (line 169 is $sth->execute):

DBD::mysql::st execute failed: called with 1 bind variables when 0 are
+ needed at test.pl line 169.
[download]

my $sql = "
      insert into BiscHet 
      ( 
      pdbAndChainID1, 
      asaSingle1, 
      asaComplex1, 
      organismPdb1, 
      taxidPdb1, 
      scopClass1, 
      scopFold1, 
      scopSuperfamily1, 
      scopFamily1,
      intactID1,
      evalue1,
      length1,
      identity1,
      organismIntact1,
      taxidIntact1,
      pdbAndChainID2, 
      asaSingle2, 
      asaComplex2, 
      organismPdb2, 
      taxidPdb2, 
      scopClass2, 
      scopFold2,
      scopSuperfamily2,
      scopFamily2,
      intactID2,
      evalue2,
      length2,
      identity2,
      organismIntact2,
      taxidIntact2,
      sequenceAtom2, 
      sequenceSeqRes2, 
      jmolSelect,
      interface
      ) 
       VALUES 
      ( 
      $id1, 
      $asaSingleC1,           
      $asaComplexC1,          
      $organismC1,           
      $taxidC1,              
      $scopClassC1,           
      $scopFoldC1,           
      $scopSuperfamilyC1,    
      $scopFamilyC1,        
      $uniprotID1,           
      $evalue1,              
      $length1,              
      $identity1,            
      $organismIntAct1,    
      $taxidIntAct1,         
      $id2, 
      $asaSingleC2,       
      $asaComplexC2, 
      $organismC2,       
      $taxidC2,          
      $scopClassC2,       
      $scopFoldC2,       
      $scopSuperfamilyC2,
      $scopFamilyC2,
      $uniprotID2,     
      $evalue2,          
      $length2,          
      $identity2,        
      $organismIntAct2,
      $taxidIntAct2,     
      'ATCGT',
      'GATAT',
      'NA',
      'interface'
      );
      "
   my $sth = $dbh->prepare($sql) or die $dbh->errstr;
   $sth->execute($sql) or die $dbh->errstr;
   $sth->finish();
[download]

Printing out the $sql and executing in MySQL works fine.

Please enlighten me!

Comment on $sth->execute error Select or Download Code

Replies are listed 'Best First'.
Re: $sth->execute error by ikegami (Patriarch) on Feb 14, 2009 at 06:08 UTC
Short Answer: `$sth->execute()` [download] Long Answer: Wanna bet most of those variables don't contain SQL literals? my $sql = " insert into BiscHet ( pdbAndChainID1, asaSingle1, asaComplex1, organismPdb1, taxidPdb1, scopClass1, scopFold1, scopSuperfamily1, scopFamily1, intactID1, evalue1, length1, identity1, organismIntact1, taxidIntact1, pdbAndChainID2, asaSingle2, asaComplex2, organismPdb2, taxidPdb2, scopClass2, scopFold2, scopSuperfamily2, scopFamily2, intactID2, evalue2, length2, identity2, organismIntact2, taxidIntact2, sequenceAtom2, sequenceSeqRes2, jmolSelect, interface ) VALUES (".join(',', ('?')x34 ).") " my $sth = $dbh->prepare($sql) or die $dbh->errstr; $sth->execute( $id1, $asaSingleC1, $asaComplexC1, $organismC1, $taxidC1, $scopClassC1, $scopFoldC1, $scopSuperfamilyC1, $scopFamilyC1, $uniprotID1, $evalue1, $length1, $identity1, $organismIntAct1, $taxidIntAct1, $id2, $asaSingleC2, $asaComplexC2, $organismC2, $taxidC2, $scopClassC2, $scopFoldC2, $scopSuperfamilyC2, $scopFamilyC2, $uniprotID2, $evalue2, $length2, $identity2, $organismIntAct2, $taxidIntAct2, 'ATCGT', 'GATAT', 'NA', 'interface' ) or die $dbh->errstr; $sth->finish(); [download] Finally, the presence of fields X1 and X2 is usually a sign of a bad database design, and here is no exception.	[reply] [d/l] [select]
Re^2: $sth->execute error by RobertCraven (Sexton) on Feb 14, 2009 at 06:28 UTC
Thanks, that worked! Beside the fields evalue1 & evalue2 the variables are either integer or text. The design of database is very bad. It runs in a virtual machine on NFS. Every join takes ages, so now the attempt to have all data in one table.	[reply]
Re^3: $sth->execute error by Rodster001 (Pilgrim) on Feb 14, 2009 at 07:52 UTC
NFS and virtual machine is not necessarily bad. I would check your indexes though (make sure the columns you join on are indexed and your where clauses are too).	[reply]
Re: $sth->execute error by targetsmart (Curate) on Feb 14, 2009 at 06:11 UTC
execute argument is bind value not the sql statement itself, after prepare just execute the sql and get the results from fetchall_arrarref (eg). see man DBI for more information Vivek -- In accordance with the prarabdha of each, the One whose function it is to ordain makes each to act. What will not happen will never happen, whatever effort one may put forth. And what will happen will not fail to happen, however much one may seek to prevent it. This is certain. The part of wisdom therefore is to stay quiet.	[reply]
Re^2: $sth->execute error by RobertCraven (Sexton) on Feb 14, 2009 at 06:29 UTC
Thanks for the explanation!	[reply]