Passing DBI resultset to function

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

Hi! This is probably very simple. I'm making an SQL query to the database:


    $sth = $dbh->prepare("MY SQL QUERY");
$sth->execute();

while (my $rad = $sth->fetchrow_hashref())
{
    $a_variable = $rad->{'Some_column'};
}

$sth->finish();
[download]

No problems there. But, I'd _also_ like to send the result to a function where I want to iterate over the resultset once more.

&DoSomeTreatment($sth???);
[download]

Can I do it, and in that case where and how?

Comment on Passing DBI resultset to function Select or Download Code

Replies are listed 'Best First'.
Re: Passing DBI resultset to function by eric256 (Parson) on Dec 15, 2009 at 14:31 UTC
Depending on the size of your results you could use a selectall_arrayref, store the results and then pass those to the function. ___________ Eric Hodges	[reply]
Re: Passing DBI resultset to function by roboticus (Chancellor) on Dec 15, 2009 at 20:03 UTC
DreamT: Here's an untested example of how you go about passing a statement handle (resultset) to a subroutine. `my $sth = $dbh->prepare("MY SQL QUERY"); $sth->execute(); SimpleResultsDump($sth); sub SimpleResultsDump { my $SH = shift; my $cnt=0; while (my $hr = $SH->fetchrow_hashref()) { print ("-" x 40), " record: ", ++$cnt, "\n"; for my $k (sort keys %$hr) { my $v = "'$$hr{$k}'" // 'null'; print "Field $k holds: $v\n"; } } }` [download] ...roboticus Error checking left as an exercise for the reader!	[reply] [d/l]
Re: Passing DBI resultset to function by misterwhipple (Monk) on Dec 15, 2009 at 15:24 UTC
Is there a reason you can't pass the `$rad` hashref to your function? -- Any sufficiently interesting Perl project will depend upon at least one module that doesn't run on Windows.	[reply] [d/l]
Re: Passing DBI resultset to function by bfdi533 (Friar) on Dec 15, 2009 at 18:09 UTC
Seems like you have several options. You could: parse the results at the same time by the function `while (my $rad = $sth->fetchrow_hashref()) { $a_variable = $rad->{'Some_column'}; &DoSomeTreatment($rad); }` [download] re-run the query from the function store the results and pass them to the function	[reply] [d/l]
Re^2: Passing DBI resultset to function by DreamT (Pilgrim) on Dec 17, 2009 at 11:26 UTC
Thank you. My goal is to operate on the resultset, rather than having to re-run the query. I guess that would do the trick?	[reply]
Re^2: Passing DBI resultset to function by afoken (Chancellor) on Dec 18, 2009 at 00:33 UTC
`&DoSomeTreatment($rad);` Is there any reason why you propagate the obsolete perl4 syntax for function calls? Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l]


Pathologically Eclectic Rubbish Lister
	PerlMonks