Saving array reference

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

When iterating over the results of a DBI query, I'd like to save a copy of the first row that matched.

my $sth = $dbh->prepare("SELECT * FROM tickets ORDER BY ticket_id");
$sth->execute();
my $first;
my $i = 0;

while (my $record = $sth->fetch()) {
   if ($i == 0) { 
      $first = $record;
   }
   blahh...
   blahh...
   $i++
}
[download]

I thought I would do this by assigning the array ref to a scalar on the first iteration. But instead of the first iteration, I end up with the last. (If there are ten rows that matched, $first contains the results from row 10... rather than row 1). How can a copy of the first array ref be saved for later use. Thanks.

Comment on Saving array reference Download Code

Replies are listed 'Best First'.
Re: Saving array reference by moot (Chaplain) on Apr 30, 2005 at 19:59 UTC
DBI::st appears to re-use internally an arrayref (I just got bitten by this today). You will have to store the record yourself: `my $first = undef; while (my $record = $sth->fetchrow_arrayref) { unless (defined $first) { @{$first} = @{$record} } # other stuff }` [download] ..although I'm unclear as to why you can't just stop retrieving records after the first, or why you can't just use a 'limit 1' clause in your SQL. Also my DBI doesn't define a fetch method on statement handles, only fetchrow_* and fetchall_*, so you may need to re-jig my example. Update: jZed++ for pointing out fetch is an alias for fetchrow_arrayref. Thanks, I've managed to miss that in every reading of perldoc DBI.	[reply] [d/l]
Re^2: Saving array reference by jZed (Prior) on Apr 30, 2005 at 20:28 UTC
fetch() is a synonym for fetchrow_arrayref().	[reply]
Re^2: Saving array reference by Errto (Vicar) on May 01, 2005 at 01:34 UTC
Good suggestions. Just two minor points: first, in fairness to the DBI developers, the fact that `fetchrow_arrayref` reuses the same reference isn't just "internal", but is actually documented in the DBI docs: 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. Second, the 'limit n' syntax isn't used across all databases, though for anyone on MySQL that's certainly a good suggestion. But Oracle, for example, doesn't have it.	[reply] [d/l]
Re^2: Saving array reference by mhearse (Chaplain) on Apr 30, 2005 at 20:18 UTC
Thanks, works great. You right though, I guess I could just do another query (strictly for the first match). Just wasn't thinking that way at the time. My thought was to save the first match for later use.	[reply]
Re: Saving array reference by jZed (Prior) on Apr 30, 2005 at 20:26 UTC
If you need to process all the rows, moot's code will work. If you only need to get the first row, then LIMIT (if available) and $row = $dbh->selctrow_arrayref() will work without a loop. If you need to grab the first row and then process the remaining rows, then grab the first row and then loop: my $first = $sth->fetch; while (my $non_first_row = $sth->fetch) { ... }.	[reply]