Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Extra iteration while fetching 'fetchrow_array' in DBI and standerd way of using DBI

by cool (Scribe)
on Jun 15, 2007 at 11:56 UTC ( #621444=perlquestion: print w/replies, xml ) Need Help??

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";
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 | -
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

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; }
    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; }
    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; }
    Now I may have overformatted it, but it certainly makes it easier to see what you're doing.

    --roboticus

      ($endstore[$ctrend][0], $endstore[$ctrend][1], $endstore[$ctrend][2], $endstore[$ctrend][3], $endstore[$ctrend][4]) = @arr;

      can be abbrieviated to

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

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

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

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

      push @endstore, [ @arr ];

      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"; }

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

        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...

        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 :)
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.
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, ..
    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}; ... }
    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})
      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() ) { ... }
      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).
        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

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...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://621444]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2020-02-19 01:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What numbers are you going to focus on primarily in 2020?










    Results (80 votes). Check out past polls.

    Notices?