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();
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.
Can I do it, and in that case where and how?

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
Re: Passing DBI resultset to function
by roboticus (Chancellor) on Dec 15, 2009 at 20:03 UTC


    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"; } } }


    Error checking left as an exercise for the reader!

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.

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); }
    • re-run the query from the function
    • store the results and pass them to the function

      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?

      Is there any reason why you propagate the obsolete perl4 syntax for function calls?


      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)