Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Problem Converting SQL term to Perl Variable

by monkfan (Curate)
on Apr 23, 2007 at 05:32 UTC ( #611439=perlquestion: print w/ replies, xml ) Need Help??
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)
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"; }
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
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
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!!
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'";
    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";
    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!

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'";
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://611439]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2014-09-30 21:03 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (384 votes), past polls