Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
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 romping around the Monastery: (4)
As of 2014-12-29 01:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (183 votes), past polls