DBI and ARGV 2

[JohnLucania]

use DBI;

...........
print "Enter the Gene Name: \n";
$ARGV[0] = <STDIN>;

print "Enter the organism: \n";
$ARGV[1] = <STDIN>;

print "Enter the exp level: \n";
$ARGV[2] = <STDIN>;
..............

my $st = "SELECT GENE.GENE_NAME, ORGANISM.ORGANISM_NAME, MRNA.MRNA_EXPRESSION_LEVEL";
$st .= " FROM GENE, ORGANISM, MRNA";
$st .= " WHERE GENE.GENE_ID = MRNA.MRNA_GENE_NAME AND ORGANISM_ID = MRNA.MRNA_ORGANISM AND
(GENE.GENE_NAME LIKE '$ARGV[0]' OR ORGANISM.ORGANISM_NAME LIKE '$ARGV[1]')
AND MRNA.MRNA_EXPRESSION_LEVEL =$ARGV[2] ";

Enter the Gene Name: U
Enter the organism: A
Enter the exp level:3

These three arguments (U, A, and 3) are qualified in the SQL (= works well in the SQL statement), but in this .pl, nothing returns. Why is that?

 

[kordaff]

Did you chomp() those command line variables you are over-writing?

=)

Kordaff

 

[JohnLucania]

duh.......................!

I am missing % for '$ARGV[0]%' and '$ARGV[1]%'.

jl

 

[JohnLucania]

while( my @row = $sth->fetchrow_array() ) {
print "Gene: '$row[0]', Organism: '$row[1]', Expression Level: '$row[2]'\n";
}

If none qualifies, how do you print "Result Not Found!"?

jl

 

[kordaff]

You're getting \n's on the end without chomp()'ing them. That would make them fail when fed to an SQL db.

The @ARGV array is loaded automatically from the command line arguements.

#!/usr/bin/perl -w
use strict;
($ARGV[2]) or 
  die "Syntax: $0 Gene_name organism exp_level\n";
print "Got Gene_name: $ARGV[0], organism: $ARGV[1], exp_level: $ARGV[2]\n";

Without assigning input from <STDIN> to them, you don't have to worry about chomp()'ing.

Kordaff

 

[kordaff]

$rv = $sth->rows;

That will tell you how many rows your query has obtained.

Kordaff

 

[JohnLucania]

I got it.
wonderful!!!

jl

 

[kordaff]

I was looking for my linux partition with last DBI and DBD-pg i installed. Help if I had installed gcc lol. WinXP did wonderful thing to my partition table...

Kordaff

 

[fishiface]

@ARGV is a system array used by perl to give you any command-line arguments to your code. It is unusual and confusing to use it, as you have, for temporary storage. You would be wiser to use normal lexical variables. These also give you the ability to give them meaningful names, which makes your code more readable.

While it doesn't look as if your code is going to be made widely available to the bad guys, it is currently vulnerable to SQL injection attacks.

It's safer (and, as a bonus, easier and more efficient) to use placeholders in your statements.

I've rewritten your SQL using q{} style quoting as I've found it's the neatest way to separate blocks of SQL from blocks of perl in complex code. This makes it much easier to understand what the SQL is doing should you need to revisit your work and is also much easier to type!

Combining these three ideas with the use of chomp(),

use DBI;

 ...........
print "Enter the Gene Name: \n";
chomp( my $name = <STDIN> );

print "Enter the organism: \n";
chomp( my $org = <STDIN> );

print "Enter the exp level: \n";
chomp( my $exp = <STDIN> );
..............

my $sh = $dbh->prepare( q{

      SELECT GENE.GENE_NAME, ORGANISM.ORGANISM_NAME,
             MRNA.MRNA_EXPRESSION_LEVEL
         FROM GENE, ORGANISM, MRNA
      WHERE GENE.GENE_ID = MRNA.MRNA_GENE_NAME
         AND ORGANISM_ID = MRNA.MRNA_ORGANISM
         AND (
             GENE.GENE_NAME LIKE ?
             OR ORGANISM.ORGANISM_NAME LIKE ?
         )
         AND MRNA.MRNA_EXPRESSION_LEVEL = ?

  } ) or die $DBI::errstr;
$sh->execute( $name, $ord, $exp ) or die $DBI::errstr;
...

Some databases and/or DBD modules don't support placeholders in LIKE clauses or, indeed, at all. Your mileage may vary.

Yours,

fish

[&quot;]As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs.[&quot;]
--Maur

 

[JohnLucania]

Great!
Thanks a bunch!
jl