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?

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]
      &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]
      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]

Back to Seekers of Perl Wisdom