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

Hi

I am using sql to retrieve info from a database through perl, but don't know how to get back to the first row of the record set after I have iterated through it once ie:

    # Send our query
    my $sth = $dbh->query("SELECT clause here;");

    # Iterate through the returned rows
    my @arr = ();

    while (@arr = $sth->fetchrow)
     {
     do whatever
     }

- now I want to get back to the first row of the record set - how do I
+ do it?

[download]

thanks for any help

Replies are listed 'Best First'.
Re: sql record sets
by tadman (Prior) on Sep 15, 2002 at 22:11 UTC
    Not sure about the DBI query function, but this should do the trick: 
    my $sth = $dbh->prepare("SELECT * FROM foo");
$sth->execute();

while (my $row = $sth->fetchrow_arrayref())
{
     # Use @$row like $row->[0], $row->[1], etc.
}
$sth->finish();    # Important!

    [download]


    Update:
    In response to your repeating question, you might want to look at the functions that can load in the entire result set, so that you can go over it as many times as you like. 
    my $rows = $dbh->selectall_arrayref("SELECT * FROM foo");

# Now @$rows contains all the row data.
# Use @$rows like $rows->[$row_number]->[$column_number]...
foreach my $row (@$rows)
{
     # ...or @$row like $row->[$column_number]
}

# Repeat as required, as long as $rows is still defined.

# $sth->finish();  # Update per pope (Not required, no $sth defined)

    [download]
[reply]
[d/l]
[select]
      finish() is not necessary here, since all rows have been fetched. This is necessary in a case where after fetching particular rows, you want to discard the rest.
[reply]
Re: sql record sets
by FoxtrotUniform (Prior) on Sep 15, 2002 at 22:45 UTC

    Rather than fetch the data a row at a time using $sth->fetchrow, you might fetch all of the data ahead of time, with something like $sth->fetchall_arrayref. Then you can do pretty much whatever you like with them.

    If the data are too large to fit comfortably in memory, this isn't the right approach. In that case, you're probably better off doing multiple queries (if you prepare the query ahead of time using $dbh->prepare, you may save some time over doing a $dbh->query every time).

    Update: D'oh! That's fetchall_arrayref, not fetchrow_arrayref. Thanks pope and Flexx!

    --
    F o x t r o t U n i f o r m
    Found a typo in this node? /msg me
    The hell with paco, vote for Erudil!

[reply]
[d/l]
[select]
      fetchrow_arrayref() also fetches a row at a time, it differs from fetchrow() only in the way it stores the return value. fetchrow() returns an array while fetchrow_arrayref() returns an array ref.
[reply]

        fetchall_arrayref() or fetchall_hashref() (quite new) would slurp in all at once.

[reply]
[d/l]
Re: sql record sets
by pope (Friar) on Sep 16, 2002 at 08:04 UTC
    DBI doesn't support scrollable cursor, if that's what you're looking for. If you want to "walk" on the result set, you may consider using the $dbh->selectall_arrayref() method which returns the whole result set as an array ref of array refs. 
    use Data::Dumper;

...

$rows = $dbh->selectall_arrayref("SELECT ...");
# the first row:
print Dumper $rows->[0];

    [download]

    Update: fixed typo

[reply]
[d/l]

Back to Seekers of Perl Wisdom