Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

perl dbi : fetchrow_arrayref

by pavunkumar (Scribe)
on Oct 22, 2010 at 09:57 UTC ( #866773=perlquestion: print w/replies, xml ) Need Help??
pavunkumar has asked for the wisdom of the Perl Monks concerning the following question:

Hi all ,

Actually I have executed the postgres query, assume that it has return the 10 rows. Now I am having that's statement handler ( $sth ) .

print Dumper $sth->fetchrow_arrayref; print Dumper $sth->fetchrow_arrayref; print Dumper $sth->fetchrow_arrayref; print Dumper $sth->fetchrow_arrayref; print Dumper $sth->fetchrow_arrayref;

Now , I have fetched the 5 rows from the statement handler ( $sth .Now I want to get back $sth reference pointer to 1th row.....

What should I do....?

Replies are listed 'Best First'.
Re: perl dbi : fetchrow_arrayref
by sundialsvc4 (Abbot) on Oct 22, 2010 at 13:03 UTC

    There is also a pure-SQL consideration to think about here.   “An SQL result-set can be arbitrarily large.”   Also, it is often the case that an application does not actually examine all the rows; only the first few.   The designers of database engines seek to exploit (and to compensate for) these factors.

    What DBI encapsulates for you is formally known as a cursor, which is a window into the result set of the query.   Depending on the circumstances (the database, the driver, how the query was set up, and so on) that cursor may or may not be updatable and it may or may not be scrollable in the reverse direction.

    It is often desirable to arrange that your query be as specific as possible (yielding the smallest recordset); that it do whatever sorting needs to be done on the server; and that you do not need to “back up” while moving through it.

Re: perl dbi : fetchrow_arrayref
by Anonymous Monk on Oct 22, 2010 at 10:11 UTC
    Two ideas
    • cache results in memory
    • execute query again
      If caching the results, the following should be taken into consideration (from the DBI perldoc):

      Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. Also, the elements of the array are also reused for each row, so take care if you want to take a reference to an element. See also "bind_columns".

Re: perl dbi : fetchrow_arrayref
by Marshall (Abbot) on Oct 24, 2010 at 05:33 UTC
    You could ask for a table of array_ref's. fetchall_arrayref.
    $tbl_ary_ref = $sth->fetchall_arrayref; $tbl_ary_ref = $sth->fetchall_arrayref( $slice ); $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
    Or perhaps, another way, @ary = $sth->fetchrow_array;
    Might want to look at the hashref returning methods also.

    I haven't come across the need to "backup". But with methods that return arrays of results, you can do that or process them even perhaps out of order. But I like sundialsvc4's idea of just getting the query "right" so you don't have to do that.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://866773]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2018-05-25 09:35 GMT
Find Nodes?
    Voting Booth?