That really depends on a few factors:

• The size of the records being fetched
• The length of time it takes for the query to be run
• The amount of memory on the machine you are running the query

In general, the best solution is to do the query a single time, and pass around an arrayref to any sub-routines doing the checksums and computations. This works well for smaller-ish query results, or intense queries that you don't want to subject your database server to very often

Of course, this could vary if you have a large recordset. I would not attempt this with 100,000 rows of data, for example, unless you've got alot of memory and/or the machine is not being used by multiple simultaneous users.

With that said, it is possible to pass around a single prepared $sth handle. All you have to remember is to pass it, AND your @bind_values around. For example:

#!/usr/bin/perl -wT

use strict;
use DBI;

#Global Database Connection string, set in Apache's httpd.conf
$ENV{DBI_DSN} ||= 'DBI:mysql(RaiseError=>1,ChopBlanks=>1,Taint=>1,Auto
+Commit=>1):;mysql_read_default_file=/etc/.my.cnf;database=MyDB';

my $statement = q{
  SELECT *
    FROM table
   WHERE column = ?
};

my @bind_values = qw(value);

my $dbh = DBI->connect;
my $sth = $dbh->prepare($statement);

if(checksums($sth, \@bind_values)) {
  my $results = computation($sth, \@bind_values);

  #do something with the $results
}

$dbh->disconnect;

sub checksums {
  my $sth         = shift;
  my $bind_values = shift;

  $sth->execute( @$bind_values );
  my $results = $sth->fetchall_arrayref({});

  #validate the $results

  return 1;
}

sub computation {
  my $sth         = shift;
  my $bind_values = shift;

  $sth->execute( @$bind_values );
  my $results = $sth->fetchall_arrayref({});

  #perform computation on the $results

  return $results;
}
[download]

IMHO, you're playing with fire querying & validating the data one time, then querying it again a second time to fetch the results.

One reason that comes to mind is that in between the first and second query, there is the possibility that the $results could be modified by another user/process. In that case, the query results from the first pass are irrelevant, and the second pass could be hiding "bad data" which needs to be validated again.

Granted, this is remote in some applications, it still could occur, and something you need to keep in mind

The playing with fire thing is exactly what I don't want to do and what I wanted to only execute the query once and pass the statement handle around rather then re-executing it each time I wanted to pull a full set of results from that statement handle. From all things said it looks like I'll have to pass the results to an array ref and pass that around if I don't want to requery each time. I've noticed that even after you've pulled you can still do a rows method and get a full count of rows so I was thinking somehow I would be able to execute some sort of reset and pull a full array ref from it any time you wanted but it looks like that is not the case. Thanks for the help.

my $dbh = DBI->connect('...', { RaiseError => 0 })
    or die "Unable to connect to datasource: $DBI::errstr\n";
my $sth = $dbh->prepare('SELECT PK, Name, Memo...');
    or die "Unable to prepare query statement: $DBI::errstr\n";
my $rv = $sth->execute()
    or die "Unable to execute query statement: $DBI::errstr\n";

my $data = $sth->fetchall_arrayref;
my %hash = %{ &checksums($data) };

# some time later...

foreach(@$data) {
    print "PK:\t", $_->[0], "\t";
    print "Name:\t", $_->[1], "\t";
    print "Digest:\t", $hash{$_->[0]}, "\n";
}

sub checksums {
    my $recordset = shift;
    my %checksums;
    foreach(@$recordset) {
        $checksums{$_->[0]} = &digest($_->[2]);
    }
    return \%checksums;
}
[download]

...untested, of course. That may not even work, it depends on exactly what fetchall_arrayref does.

Otherwise, maybe you can alias or copy the statement handle, although I'm not sure how that would work. If you're feeling ambitious, you could write a wrapper for DBI that caches the statement handle in memory and allows you to manipulate the cursor. Actually, there may already be a module to do that. Check CPAN. =) If you're feeling lazy, maybe you can just run the same query twice and generate two equivalent recordsets. Eew!

When I hear about problems like this I can't help but imagine there's a better way to approach the issue. If you can rewrite your code to avoid having to loop through the recordset twice, that might be the best solution. (see below for a rewrite of the above code)

Good luck,

'kaboo

(this node has been updated)

• a) re-use the array_ref as suggested above (preferable)
• b) re-execute the prepared statement handle which will reset the cursor to the beginning of the result set, though you have no guarantee that the table hasn't changed in the meantime.