Here is what I use.
Note : Global $dbh has the db handle.
sub Fetch_w_callback{ # Main DBI retrieval mechanism----
my ($sql, $callback) = @_;
my $state = $_[2] ||= {}; # ** This THIRD param is CALL-by-ref **
$debug{SQL} = $sql;
(my $sth= $state->{STH} = $dbh->prepare($sql))->execute();
$state->{ROW} = $state->{QUIT} = 0;
while (my $row = $sth->fetchrow_hashref()){
$debug{ROW} = $state->{ROW}++;
#last if ($callback->($row,$state) || 0) < 0; # Return negative
+to quit
$callback->($row,$state);
last if $state->{QUIT}; # Call-ee asked us to stop.
}
$sth->finish;
}
Calling example:
my $sql= "SELECT blah blah....";
Fetch_w_callback( "COPY ($sql) TO STDOUT CSV HEADER", # Let pg do c
+sv work
sub {
my ($row,$state) = @_;
print $row,"\n";
##$fieldnames ||= $state->{STH}->{NAME} || [keys %$row];
##if ($state->{ROW} ==1 ){
## print $state->{HEADER} =
## join(",",@$fieldnames, "SOURCE=\@$ENV{HOSTNAM
+E}"
## . " on " . scalar(localtime(time)))
+ ."\n"; # Header row
##}
##
##print join(",", map{defined $_?$_:""}@$row{@$fieldnames})
+ ."\n";
return 0;
},
$state
);
"Imaginary friends are a sign of a mental disorder if they cause distress, including antisocial behavior. Religion frequently meets that description"