Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Using a better database fetch

by Anonymous Monk
on Jan 16, 2013 at 15:35 UTC ( #1013591=perlquestion: print w/ replies, xml ) Need Help??
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; }

Thanks for looking!

Comment on Using a better database fetch
Download Code
Re: Using a better database fetch
by Corion (Pope) 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.

Re: Using a better database fetch
by BrowserUk (Pope) 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.
Re: Using a better database fetch
by NetWallah (Abbot) 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 );

                 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.

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."
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;
    To
    return $self->dbh->selectall_arrayref($sql, { Slice => {} });
    Where the return is optional as well.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1013591]
Approved by BrowserUk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (15)
As of 2014-10-31 19:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (223 votes), past polls