Xi Vix Xi Vix - 3 months ago 22
Perl Question

How to reread perl database query result

I would like to use the results of a query twice.

How do I reposition the pointer to start reading the results from the beginning a second time?

Example below (just printing to screen for simplicity):

if ( $dbh = DBI->connect( "DBI:mysql:database=tng;host=ip", "username", "password" ) ) {
$strSQL = "select * from table";
if ( $strQuery = $dbh->prepare($strSQL) ) {
if ( $strQuery->execute() ) {
while ( @data = $strQuery->fetchrow_array() ) {
print $data[0];
}
--reposition to top and reread the query result and do something else with the data-- $strQuery->finish;
}
else {
$strMsg = "$strDateTime ERROR -- unable to execute statement: " . $strQuery->errstr . "\n";
print logFile "$strMsg";
}
}
else {
$strMsg = "$strDateTime ERROR -- unable to prepare statement: " . $dbh->errstr . "\n";
print logFile "$strMsg";
}
$dbh->disconnect();
}
else {
print logFile "$strDateTime ERROR -- unable to connect to iptables database ... " . DBI->errstr . " \n";
}

Answer

You wouldn't expect an IO library to load the entire file into memory to read it line by line, so why do you expect it from a database library?

Besides, it's totally unnecessary. It's super easy to load the complete result.

my $sth = $dbh->prepare($sql);
my $data = $dbh->selectall_arrayref($sth);

for my $row (@$data) {
    my ($col1, $col2, ...) = @$row;
    ...
}

for my $row (@$data) {
    my ($col1, $col2, ...) = @$row;
    ...
}

You can load the data into a DBD::Sponge if you need a DBI sth.

Comments