Problem Converting SQL term to Perl Variable

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

Hi,
I have no problem performing such query under mysql prompt:

mysql> SELECT Symbol FROM gene_info RIGHT JOIN gene2go ON(gene_info.`G
+eneID`= gene2go.`GeneID`) WHERE GO_Term='proteasome localization'; 
+-------------+
| Symbol      |
+-------------+
| SPBC1734.06 |
| SPBC646.09c |
| cut8        |
| rhp6        |
| SPAC1420.03 |
| SPAC3G6.02  |
| rpn5-b      |
| SPBC19C7.02 |
+-------------+
8 rows in set (2 min 19.11 sec)
[download]

But when I run this Perl script:

use strict;
use Data::Dumper;
use Carp;
use DBI;

my $dsn = 'localhost';
my $user = 'ewijaya';
my $password = 'ewijaya';
my $dbh = DBI->connect('dbi:mysql:motif_finder','monkfan','monkpwd')
    or die "Connection Error: $DBI::errstr\n";

my $go_term = "proteasome localization";
my $sql = "SELECT Symbol FROM gene_info RIGHT JOIN gene2go ON(gene_inf
+o.`GeneID`= gene2go.`GeneID`) WHERE GO_Term=$go_term";

my  $sth = $dbh->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";


while ( my @row = $sth->fetchrow_array ) {
    print "@row\n";
}
[download]

It gives:

:!perl  search_gene.pl
DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near 'localization' at line 1 at search_gene.pl 
+line 21.
SQL Error: You have an error in your SQL syntax; check the manual that
+ corresponds to your MySQL server version for the right syntax to use
+ near 'localization' at line 1
[download]

How can we correctly assign SQL command in the Perl Script?

Regards,
Edward

Comment on Problem Converting SQL term to Perl Variable Select or Download Code

Replies are listed 'Best First'.
Re: Problem Converting SQL term to Perl Variable by naikonta (Curate) on Apr 23, 2007 at 05:42 UTC
Hi monkfan, you missed to quote the `$go_term`, it should be: `my $sql = "SELECT Symbol FROM gene_info RIGHT JOIN gene2go ON(gene_inf +o.GeneID=gene2go.GeneID) WHERE GO_Term='$go_term'";` [download] Or, the best way as can be found in the excellent manual of DBI is to use a placeholder (the `?` mark). `my $go_term = 'proteasome localization'; my $sql = 'SELECT Symbol FROM gene_info RIGHT JOIN gene2go ON(gene_inf +o.GeneID= gene2go.GeneID) WHERE GO_Term=?'; my $sth = $dbh->prepare($sql); $sth->execute($go_term) or die "SQL Error: $DBI::errstr\n";` [download] Then you don't need to worry about quoting anymore. Oh, another thing. Get used to use single quote on plain string, e.g it doesn't contain variable or evaluable expression. Update: gleh, fixed typo Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!	[reply] [d/l] [select]
Re: Problem Converting SQL term to Perl Variable by jeanluca (Deacon) on Apr 23, 2007 at 05:42 UTC
I see a difference between the both already! Try: ....Go_Term='$go_term'" Hopefully it helps LuCa UPDATE: Also have a look at the quote method from DBI!!	[reply]
Re: Problem Converting SQL term to Perl Variable by ysth (Canon) on Apr 23, 2007 at 09:08 UTC
When you have a mystery error like this, try to verify at each step that things are what you expect. In this case, that would mean printing $sql and comparing it to your working mysql prompt query.	[reply]
Re: Problem Converting SQL term to Perl Variable by quester (Vicar) on Apr 23, 2007 at 05:47 UTC
The Perl code isn't quoting the value substituted for $go_term. You could try adding quotes to match your SQL example, like this... my $sql = "SELECT Symbol FROM gene_info RIGHT JOIN gene2go ON(gene_inf +o.`GeneID`= gene2go.`GeneID`) WHERE GO_Term='$go_term'"; [download]	[reply] [d/l]