Using a better database fetch

[Anonymous Monk]

Hi Monks!
I have this sub I am trying to implement that uses "fetchall_arrayref" I would like to use some other way to fetch the table, how could a use another method and still maintain the same code logic?

sub _get_users {
    my $self = shift;
    my $sql  = "select * from my_table order by username";
    $self->dbh->{PrintError} = 0;
    $self->dbh->{RaiseError} = 1;
    # fetchrow_hashref?
    my $sth = $self->dbh->prepare($sql);
    $sth->execute();
    my $rs = $sth->fetchall_arrayref(
        {
            user_id    => 1,
            username   => 1,
            first_name => 1,
            last_name  => 1
        }
    );
    return $rs;
}

Thanks for looking!

[Corion]

Have you read the discussion of ->fetchall_arrayref in DBI? It discusses another way. But if you look at your logic, you will see that your logic returns the complete results at the end of your subroutine. If you want to keep your logic, you will always have to fetch all users.

[BrowserUk]

I would like to use some other way to fetch the table, ...

You don't say why you are trying to avoid fetchall_arrayref. or what benefit you are hoping to accrue from the change?

[NetWallah]

I usually use a callback routine for convenience.
This reduces looping logic to one common place.

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=$dbh->prepare($sql))->execute();
   
   $state->{ROW} = 0; 
   while (my $row = $sth->fetchrow_hashref()){
      $debug{ROW} = $state->{ROW}++;
      last if $callback->($row,$state) < 0; # Return negative to quit
   }
   $sth->finish;
 }
## Sample call, later in the code ....
Fetch_w_callback( "select client_name from xxx where cid='some-cid'",
                sub{ $row->{whatever} =  $_[0]->{client_name}; # SET
                    #Process  other values );

[Neighbour]

You can reduce

my $sth = $self->dbh->prepare($sql);
    $sth->execute();
    my $rs = $sth->fetchall_arrayref(
        {
            user_id    => 1,
            username   => 1,
            first_name => 1,
            last_name  => 1
        }
    );
    return $rs;

To

return $self->dbh->selectall_arrayref($sql, { Slice => {} });

Where the return is optional as well.

[Anonymous Monk]

If possible, "don't use memory where a database ought to be."