FAQ
I am trying to query a MySQL database through a perl script but in a
way that emulates a command line call like:

echo "select species,access,utr3 from pg,plant where geneID = 12
and length(utr3) < 300 and pg.plantID = plant.plantID\n" | mysql -
uUser -pPass Database_Name. #I know that this works

This approach works just fine in a script I wrote for populating a
database where the call looks like this:

my @pg_insert = (0,",",$symID,",",$plantID,",",$start+1,",",$end
+2,",",$direction,",", "\'$utr3\'\n"); #prepare string values
my $string = "insert into pg values \(@pg_insert\);\n"; #prepare
string.
`echo "$string"| mysql -uUser -pPass Database`; #populate Database.

In a simpler block to get values from Database is where I am running
into a wall:

1:my $query_string = "select species,access,utr3 from pg,plant where
geneID = $geneID and pg.plantID = plant.plantID\n";
2:print "\n $query_string \n";
3:`echo "select * from plant\n" | mysql -uUser -pPass Database`;

(2) Does confirm a properly formated query string but (3) returns
nothing. I have checked that the db is populated and that the query
string works. It is just the call from perl that I can't get right.
Why does the first block work just fine and the second one not?

Please help.
Thanks.

Search Discussions

  • Jim Gibson at Aug 8, 2011 at 11:39 pm
    On 8/8/11 Mon Aug 8, 2011 9:30 AM, "addie" <adlai@refenestration.com>
    scribbled:
    I am trying to query a MySQL database through a perl script but in a
    way that emulates a command line call like:

    echo "select species,access,utr3 from pg,plant where geneID = 12
    and length(utr3) < 300 and pg.plantID = plant.plantID\n" | mysql -
    uUser -pPass Database_Name. #I know that this works

    This approach works just fine in a script I wrote for populating a
    database where the call looks like this:

    my @pg_insert = (0,",",$symID,",",$plantID,",",$start+1,",",$end
    +2,",",$direction,",", "\'$utr3\'\n"); #prepare string values
    my $string = "insert into pg values \(@pg_insert\);\n"; #prepare
    string.
    `echo "$string"| mysql -uUser -pPass Database`; #populate Database.

    In a simpler block to get values from Database is where I am running
    into a wall:

    1:my $query_string = "select species,access,utr3 from pg,plant where
    geneID = $geneID and pg.plantID = plant.plantID\n";
    2:print "\n $query_string \n";
    3:`echo "select * from plant\n" | mysql -uUser -pPass Database`;

    (2) Does confirm a properly formated query string but (3) returns
    nothing. I have checked that the db is populated and that the query
    string works. It is just the call from perl that I can't get right.
    Why does the first block work just fine and the second one not?
    If those three lines are in your Perl program verbatim, then you are
    discarding the results of line 3. You need to save the output. You should
    also escape the embedded double-quotes:

    my @result = `echo \"select * from plan\n\" | mysql -uUser -pPass Database`;

    Have you considered using the DBI module for database access? Your Perl
    program would benefit from using that module instead of shelling out to the
    mysql utility.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupbeginners @
categoriesperl
postedAug 8, '11 at 4:31p
activeAug 8, '11 at 11:39p
posts2
users2
websiteperl.org

2 users in discussion

Addie: 1 post Jim Gibson: 1 post

People

Translate

site design / logo © 2021 Grokbase