cool has asked for the wisdom of the Perl Monks concerning the following question:

Revered monks,

Recently I have started working with SQL and so with DBI. I have a few small queries. I have given the code, input file and output.

It would be great if someone can spare time for these questions.

1- Why While loop over fetchrow_array is running one extra iteration (?, is this the actual prob)

2- Is this a good method to check 'NULL'? or there is some better alternative available.

3- For firing different queries, is the way used in code appropriate? Or there is another method.

Thank you in advance (further will be given as rep ++ :-)) 
use strict;
use DBI;
print "Please enter the Nucleotide position \n";
my $in=<STDIN>;
chomp $in;
my $flankingregion=3000;
my $startposition=$in-$flankingregion;
my $endposition=$in+$flankingregion;
my $dbh = DBI->connect('DBI:Pg:dbname=trial')
    or die "Couldn't open database: $DBI::errstr; stopped";
my $sth = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement:
+$DBI::errstr; stopped";
SELECT * FROM wholegene  where $startposition<=gstart  and $endpositio
+n>=gend;
End_SQL
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stop
+ped";
my @midstore;
my $midflag=1;
{
  my $ctrmid=0;
  while ( ($midstore[$ctrmid][0], $midstore[$ctrmid][1], $midstore[$ct
+rmid][2],$midstore[$ctrmid][3],$midstore[$ctrmid][4])= my @arr  = $st
+h->fetchrow_array() ) {
  $midflag=2;

  my ($field1, $field2, $field3,$field4,$field5) =@arr;
  ++$ctrmid;
          print STDOUT "Field 1: $field1  Field 2: $field2  Field 3: $
+field3 Field 4:$field4 Field5: $field5\n";
    }
}
if ($midflag==1){print "NULL\n";}

print "#################################$startposition\t:: $endpositio
+n\n";

my $sth1 = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement
+:$DBI::errstr; stopped";
SELECT * FROM wholegene  where gstart<=$startposition  and $startposit
+ion<=gend;
End_SQL
my $startflag=1;
my @startstore;
$sth1->execute() or die "Couldn't execute statement: $DBI::errstr; sto
+pped";
{
  my $ctrstart=0;
  while (($startstore[$ctrstart][0],$startstore[$ctrstart][1],$startst
+ore[$ctrstart][2],$startstore[$ctrstart][3],$startstore[$ctrstart][4]
+)= my @arr = $sth1->fetchrow_array() ) {
  $startflag=2;
                                                                      
+                   
my ($field1, $field2, $field3,$field4,$field5) = @arr;
  print STDOUT "Field 1: $field1  Field 2: $field2  Field 3: $field3 F
+ield 4:$field4 Field5: $field5\n";
  ++$ctrstart;
  }
}
if ($startflag==1){print "NULL start\n";}

print "#################################$startposition\t:: $endpositio
+n\n";

my $sth2 = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement
+:$DBI::errstr; stopped";
SELECT * FROM wholegene  where gstart<=$endposition  and $endposition<
+=gend;
End_SQL

my $endflag=1;
my @endstore;

$sth2->execute() or die "Couldn't execute statement: $DBI::errstr; sto
+pped";
{
  my $ctrend=0;
  while (($endstore[$ctrend][0],$endstore[$ctrend][1],$endstore[$ctren
+d][2],$endstore[$ctrend][3],$endstore[$ctrend][4]= my @arr)= $sth2->f
+etchrow_array() ) {
  my ($field1, $field2, $field3,$field4,$field5)=@arr;
  $endflag=2;
  print STDOUT "Field 1: $field1  Field 2: $field2  Field 3: $field3 F
+ield 4:$field4 Field5: $field5\n";
  ++$ctrend;
  }
}
if ($endflag==1){print "NULL end\n";}

$dbh->disconnect();
{
  my $x=0;
  foreach  (@midstore)
  {

   print "\nMID POINT@@@@@@@@@@@@@@\n$midstore[$x][0]\t$midstore[$x][1
+]\t$midstore[$x][2]\t$midstore[$x][3]\t$midstore[$x][4]\n";
   $x++;
  }
}
{
  my $x=0; foreach  (@startstore)
  { 
    print "\nSTARTING PT@@@@@@@@@@@@\n$startstore[$x][0]\t$startstore[
+$x][1]\t$startstore[$x][2]\t$startstore[$x][3]\t$startstore[$x][4]\n"
+;
    $x++;   } }
{
  my $x=0;
  foreach  (@endstore)
  {

    print "\nEND POINT@@@@@@@@@@@@@@\n$endstore[$x][0]\t$endstore[$x][
+1]\t$endstore[$x][2]\t$endstore[$x][3]\t$endstore[$x][4]\n";
    ++$x;
  }
}
my $y=scalar(@midstore);
print "\n$y";

[download]
Database relation 
geneid  | genename | gstart  |  gend   | strand
----------+----------+---------+---------+--------
 EG11277  | thrL     |     190 |     255 | +
 EG10998  | thrA     |     337 |    2799 | +
 EG10999  | thrB     |    2801 |    3733 | +
 EG11000  | thrC     |    3734 |    5020 | +
 G6081    | yaaX     |    5234 |    5530 | +
 EG10011  | yaaA     |    5683 |    6459 | -
 EG11555  | yaaJ     |    6529 |    7959 | -
 EG11556  | talB     |    8238 |    9191 | +
 EG11511  | mog      |    9306 |    9893 | +
 EG11512  | yaaH     |    9928 |   10494 | -
 EG11509  | htgA     |   10830 |   11315 | +
 G6082    | yaaW     |   10643 |   11356 | -
 G8202    | yaaI     |   11382 |   11786 | -
 EG10241  | dnaK     |   12163 |   14079 | +
 G0-8893  | tpke11   |   14080 |   14168 | +
 EG10240  | dnaJ     |   14168 |   15298 | +
 G6083    | insL-1   |   15445 |   16557 | +
 G0-9563  | hokC     |   16751 |   16903 | -
 EG10373  | mokC     |   16751 |   16960 | -
 G0-9581  | sokC     |   16952 |   17006 | +
 EG10652  | nhaA     |   17489 |   18655 | +
 EG11078  | nhaR     |   18715 |   19620 | +
 G6085    | insB-1   |   19811 |   20314 | -
 G6086    | insA-1   |   20233 |   20508 | -
 G0-10535 | insAB-1  |   19810 |   20508 | -
 EG10919  | rpsT     |   20815 |   21078 | -

[download]
Output 
Please enter the Nucleotide position
4000
Field 1: EG10999  Field 2: thrB  Field 3: 2801 Field 4:3733 Field5: +
Field 1: EG11000  Field 2: thrC  Field 3: 3734 Field 4:5020 Field5: +
Field 1: G6081  Field 2: yaaX  Field 3: 5234 Field 4:5530 Field5: +
Field 1: EG10011  Field 2: yaaA  Field 3: 5683 Field 4:6459 Field5: -
#################################1000   :: 7000
Field 1: EG10998  Field 2: thrA  Field 3: 337 Field 4:2799 Field5: +
#################################1000   :: 7000
Field 1:   Field 2:   Field 3:  Field 4: Field5:

MID POINT@@@@@@@@@@@@@@
EG10999 thrB    2801    3733    +

MID POINT@@@@@@@@@@@@@@
EG11000 thrC    3734    5020    +

MID POINT@@@@@@@@@@@@@@
G6081   yaaX    5234    5530    +

MID POINT@@@@@@@@@@@@@@
EG10011 yaaA    5683    6459    -

MID POINT@@@@@@@@@@@@@@


STARTING PT@@@@@@@@@@@@
EG10998 thrA    337     2799    +

STARTING PT@@@@@@@@@@@@


END POINT@@@@@@@@@@@@@@
EG11555 yaaJ    6529    7959    -

END POINT@@@@@@@@@@@@@@


5

[download]

Replies are listed 'Best First'.
Re: Extra iteration while fetching 'fetchrow_array' in DBI and standerd way of using DBI
by roboticus (Chancellor) on Jun 15, 2007 at 12:51 UTC
    cool:

    No offense intended, but you should trim down your code before sending a question like that. Many will skip the node if they have to work too hard to see the problem. (See nodes like How (Not) To Ask A Question).

    Anyway, on to your question. Based on your statement and the code, I'd imagine that your problem is that you process the data this way:

      while (($endstore[$ctrend][0],$endstore[$ctrend][1],$endstore[$ctren
+d][2],$endstore[$ctrend][3],$endstore[$ctrend][4])= my @arr)= $sth2->
+fetchrow_array() ) {
  my ($field1, $field2, $field3,$field4,$field5)=@arr;
  $endflag=2;
  print STDOUT "Field 1: $field1  Field 2: $field2  Field 3: $field3 F
+ield 4:$field4 Field5: $field5\n";
  ++$ctrend;
  }

    [download]
    The $sth2->fetchrow_array() call returns false when it has nothing to report back, and you're assigning that to an array. Move the extra assignment into your loop so it's only copied if you have a row of data:

      while (my @arr = $sth2->fetchrow_array() ) {
    # Keep a copy of the data for future use
    ($endstore[$ctrend][0],$endstore[$ctrend][1],$endstore[$ctrend][2]
+,$endstore[$ctrend][3],$endstore[$ctrend][4])= @arr;
    my ($field1, $field2, $field3,$field4,$field5)=@arr;
    $endflag=2;
    print STDOUT "Field 1: $field1  Field 2: $field2  Field 3: $field3
+ Field 4:$field4 Field5:$field5\n";
    ++$ctrend;
  }

    [download]
    But you really need to start working on structuring your programs to make them simpler to read. This will help make your code more maintainable, as well as making it easier to steal bits from for other projects.

    Use indentation liberally, don't be afraid of blank lines, and use meaningful variable names. I'm not going to take on your entire script, but let's work on this loop for a moment. A little bit of rearranging will make it a little easier to read:

      while (my @arr = $sth2->fetchrow_array() ) {
    # Keep a copy of the data for future use
    ($endstore[$ctrend][0], $endstore[$ctrend][1],
     $endstore[$ctrend][2], $endstore[$ctrend][3],
     $endstore[$ctrend][4]) = @arr;

    my ($field1, $field2, $field3, $field4, $field5) = @arr;
    $endflag=2;
    print STDOUT "Field 1: $field1  Field 2: $field2  "
    .            "Field 3: $field3  Field 4: $field4  "
    .            "Field 5: $field5\n";
    ++$ctrend;
  }

    [download]
    Now I may have overformatted it, but it certainly makes it easier to see what you're doing.

    --roboticus

[reply]
[d/l]
[select]
      ($endstore[$ctrend][0], $endstore[$ctrend][1],
 $endstore[$ctrend][2], $endstore[$ctrend][3],
 $endstore[$ctrend][4]) = @arr;

      [download]

      can be abbrieviated to 

      @{$endstore[$ctrend]}[0..3] = @arr[0..3];

      [download]

      If I read the intent correctly, it can be abbrieviated further to 

      $endstore[$ctrend] = [ @arr ];

      [download]

      Of course, since $ctrend is always being incremented, we simply want 

      push @endstore, [ @arr ];

      [download]

      Final result: 

      while (my @arr = $sth2->fetchrow_array() ) {
    push @endstore, [ @arr ];

    my ($field1, $field2, $field3, $field4, $field5) = @arr;
    $endflag=2;
    print STDOUT "Field 1: $field1  Field 2: $field2  "
    .            "Field 3: $field3  Field 4: $field4  "
    .            "Field 5: $field5\n";
}

      [download]

      Note that [ @arr ] can't be replaced by \@arr since you'd be pushing multiple references to the same array.

[reply]
[d/l]
[select]
        ikegami:

        I thought about simplifying it further, but I didn't want to "go all perlmonks on him". ;^)

        Actually, I was going to go further and try to simplify the endstore assignment, but I wasn't comfortable enough in my perliness to be certain the best way to do it. (I've been doing C++ for a while lately, and it takes my brain a bit to shift gears...)

        --roboticus

        Update: Added hyperlink to quote...

[reply]
[d/l]
        Dear ikegami,

        Actually something just opposite I wanted; I wanted monks to go all monks way go all perlmonks on him, but to phrase that in such way was a problem :) To tell you the truth, I have learnt anonymous reference Re^2: create hash names dynamically just 5 days ago and while learing DBI got the opportunity to use that. But after writing/working I knew there must be shorter ways to do that. But it would come to this, I did not have idea. So frankly I am impressed, once again. And to add further to that, would love to be bombarded by samego all perlmonks on him way, all the time.

        Thank you for showering of wisdom.
        Love you all monks :)
[reply]
Re: Extra iteration while fetching 'fetchrow_array' in DBI and standerd way of using DBI
by jZed (Prior) on Jun 15, 2007 at 17:18 UTC
    I'm afraid your code is too long for me to be able to answer your main questions, but here are some general suggestions:
    • instead of sprinkling DBI::errstrs all over your code, just set RaiseError=>1,PrintError=>0 in you connection which will automatically do the the error checking and reporting.
    • Instead of $sth->fetchrow_array, use $sth->fetchrow_hashref or bind_columns() to put your results in a hash since you want a hash, not an array
    • Do not pass your user input directly into the SQL the way you are doing now, read about placeholders, and always use them.
[reply]
Re: Extra iteration while fetching 'fetchrow_array' in DBI and standerd way of using DBI
by andreas1234567 (Vicar) on Jun 15, 2007 at 14:13 UTC
    Just a minor comment:
    SELECT *
    I would replace that with 
    SELECT column1, column2, ..

    [download]
    to ensure that you get the columns in the order you expect.

    Continue down the same road with the fetch:

    while ( my @arr = $sth->fetchrow_array() )
    I would replace that with 
    while ( my $href  = $sth->fetchrow_hashref() ) {
   my $col1 = $href->{column1};
   my $col2 = $href->{column2};
   ...
}

    [download]
    This way I'm certain that my variables correspond to the values by name, not just by their position in the array.
    --
    print map{chr}unpack(q{A3}x24,q{074117115116032097110111116104101114032080101114108032104097099107101114})
[reply]
[d/l]
[select]
      SELECT *

      I sometimes use 'select *' but it is usually combined with something like:

      $sth->execute();
my @columns = $sth->{NAME_uc};
my %row; $sth->bind_columns( \@row{@columns} );
while ( $sth->fetch() ) {
...
}

      [download]
      But then you have to be watch out for columns that get renamed or deleted that you explicitly use in the while loop, etc. (and all selected fields must have an explicit name, e.g., you need an alias for calculated fields).
[reply]
[d/l]
        runrig:

        I like the trick to bind the columns up, but I still wouldn't use a select * if only because someone might WTF-ify the database and you'll be reading quite a bit more data than you need. Also, future maintainers won't know which columns are significant without significant spelunking through the code.

        --roboticus

[reply]
[d/l]
Re: Extra iteration while fetching 'fetchrow_array' in DBI and standerd way of using DBI
by pajout (Curate) on Jun 15, 2007 at 12:37 UTC
    Could you, please, explain in a few sentences, what do you need? I am sorry, code is messy from my point of view. Alternatively, DBI documentation gives you more oportunities how to fetch rows from database...
[reply]

Back to Seekers of Perl Wisdom