How to return $ref from: $ref = $sth->fetchall

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

I'm building a user interface to run a bunch of queries. It uses a subroutine to prep the data then calls a subroutine that does the query. I want to return just the $ref to the calling subroutine so I can manipulate the table differently depending on the type of query/data. Looping thru @$ref inside the query subroutine works great...

my $ref = $sth->fetchall_arrayref;

for my $rec (@$ref){
  $query_results .= "<tr>";
  no warnings;
  for my $field (@$rec)
    {$query_results .= "<td>$field</td>"; }
  $query_results .= "</tr>";    
}
$sth->finish();
$dbh->disconnect();
return($query_results);
[download]

However, when I try to return the $ref to the calling subroutine and process it there, I get no data. In fact, I don't even get any table cells. Your help is greatly appreciated.

my $ref = &select_query($sqlstatement);

for my $rec (@$ref){
  $query_results .= "<tr>";
  no warnings;
  for my $field (@$rec)
    {$query_results .= "<td>$field</td>"; }
  $query_results .= "</tr>";    
}
[download]

Comment on How to return $ref from: $ref = $sth->fetchall_arrayref; Select or Download Code

Replies are listed 'Best First'.
Re: How to return $ref from: $ref = $sth->fetchall_arrayref; by diotalevi (Canon) on Mar 15, 2006 at 00:17 UTC
DBI does some tricky optimizations. You should consider that reference to be owned by the $sth so when you closed your database connection, it appears you also cleared your results. Try making a shallow copy of the data first and if that doesn't work, make a deep copy. `sub select_query { # shallow copy my @results = @{ $sth->fetchall_arrayref() \|\| [] }; return \ @result; } use Storable 'dclone'; sub select_query { # deep copy my @results = @{ dclone( $sth->fetchall_arrayref() ) \|\| [] }; return \ @result; }` [download] ⠤⠤ ⠙⠊⠕⠞⠁⠇⠑⠧⠊	[reply] [d/l]
Re: How to return $ref from: $ref = $sth->fetchall_arrayref; by ioannis (Abbot) on Mar 15, 2006 at 01:13 UTC
I followed your code, and everything is working fine. Data are returned to $ref for outside processing. Check if your $sqlstatement is properly quoted I doubt there could possibly be a problem with $sth being out of scope: the statement $a = $b does not copy pointers, it copies the actual data of $b .	[reply]
Re^2: How to return $ref from: $ref = $sth->fetchall_arrayref; by hipnoodle (Novice) on Mar 20, 2006 at 13:43 UTC
Thanks for your help. I persisted, changed the for loop to foreach and it worked. (crazy). Anyway, thanks for letting me know I was on the right track.	[reply]