Using a better database fetch

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

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;
}
[download]

Thanks for looking!

Comment on Using a better database fetch Download Code

Replies are listed 'Best First'.
Re: Using a better database fetch by Corion (Patriarch) on Jan 16, 2013 at 15:38 UTC
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.	[reply] [d/l]
Re: Using a better database fetch by BrowserUk (Patriarch) on Jan 16, 2013 at 15:39 UTC
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? With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice.	[reply]
Re: Using a better database fetch by NetWallah (Canon) on Jan 16, 2013 at 16:56 UTC
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 ); [download] Most people believe that if it ain't broke, don't fix it. Engineers believe that if it ain't broke, it doesn't have enough features yet.	[reply] [d/l]
Re: Using a better database fetch by Neighbour (Friar) on Jan 17, 2013 at 10:27 UTC
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;` [download] To `return $self->dbh->selectall_arrayref($sql, { Slice => {} });` [download] Where the `return` is optional as well.	[reply] [d/l] [select]
Re: Using a better database fetch by Anonymous Monk on Jan 16, 2013 at 20:45 UTC
If possible, "don't use memory where a database ought to be."	[reply]