perl dbi : fetchrow

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;
[download]

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

Comment on perl dbi : fetchrow_arrayref Download Code

Replies are listed 'Best First'.
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	[reply]
Re^2: perl dbi : fetchrow_arrayref by mjscott2702 (Pilgrim) on Oct 22, 2010 at 11:07 UTC
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".	[reply]
Re: perl dbi : fetchrow_arrayref by locked_user 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 Marshall (Canon) 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 );` [download] 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.	[reply] [d/l]