Re use Mysql result set in Perl

[vaibhav1983]

Hi ALL,

I am writing a script to retreive entries from a DB. And then I loop through all the entries.

Once I am done looping through all the result sets, I have to re-loop over them again.

This is done in an infinite loop (Please don't ask why to do it infinitely :P).

The script is as follows.

$sth = $dbh->prepare($sql);
$sth->execute;
while()
{
while(@row = $sth->fetchrow_array())
{
//Working on the row returned.
}
}

The code has a wrapped infinite loop around it. So you can see the code as well, how I want it to work.

$sql is the sql query I am firing.

In PHP we have a function mysql_data_seek to resuse the result set.

Do we have something similar in Perl?
I am using DBI to make connection with MySql Database.

I will appreciate any help!

Regards
Vaibhav

[d5e5]

I don't think you can re-use a result set with DBI base on this quote from this tutorial:

Cursors are used for sequential fetching operations: records are fetched in the order in which they are stored within the result set. Currently, records cannot be skipped over or randomly accessed. Furthermore, once a row addressed by a cursor has been fetched, it is ``forgotten'' by the cursor. That is, cursors cannot step backwards through a result set.

I think you could do what you want by moving the $sth->execute; into the start of your outer loop. Really you would be re-querying the database each time you iterate through that loop.

An alternative would be to copy each array into an array of arrays and iterate through that. Make sure you copy and save each array the array reference points to, instead of copying the same array ref multiple times, or you will end up with only the last row's data saved.

[d5e5]

I haven't tried this, but have a look at selectall_arrayref
Looks like @result = @{ $dbh->selectall_arrayref($sql, { Slice => {} }) }; puts all the rows of the result set into an array which you can then loop through as many times as you want.