Passing a shell command

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.


--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/

Re: Passing a shell command

On 8/8/11 Mon Aug 8, 2011 9:30 AM, "addie"
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.



--
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
http://learn.perl.org/