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 $endposition>=gend;
End_SQL
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
my @midstore;
my $midflag=1;
{
  my $ctrmid=0;
  while ( ($midstore[$ctrmid][0], $midstore[$ctrmid][1], $midstore[$ctrmid][2],$midstore[$ctrmid][3],$midstore[$ctrmid][4])= my @arr  = $sth->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:: $endposition\n";

my $sth1 = $dbh->prepare(<<End_SQL) or die "Couldn't prepare statement:$DBI::errstr; stopped";
SELECT * FROM wholegene  where gstart<=$startposition  and $startposition<=gend;
End_SQL
my $startflag=1;
my @startstore;
$sth1->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
{
  my $ctrstart=0;
  while (($startstore[$ctrstart][0],$startstore[$ctrstart][1],$startstore[$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 Field 4:$field4 Field5: $field5\n";
  ++$ctrstart;
  }
}
if ($startflag==1){print "NULL start\n";}

print "#################################$startposition\t:: $endposition\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; stopped";
{
  my $ctrend=0;
  while (($endstore[$ctrend][0],$endstore[$ctrend][1],$endstore[$ctrend][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 Field 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";