Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

My doubts about using fetchall_arrayref

by Anonymous Monk
on Apr 02, 2013 at 13:10 UTC ( #1026680=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!
In my application I want to have one sub to use just to retrieve all the information I need from my db table. The issue is that I am using fetchall_arrayref. In the first call to this sub I am retrieving all the records so I am passing all the column names I need to the sub, it works fine, but on my second call I donít need all the columns. My question is, can I pass only the rows I need or if there is a way so fetchall_arrayref will take whatever will be passed by the sql query.
Here is the sub with all the rows been queried from the db table.
# Retrieve all the data to process from the database sub _all_data { my $self = shift; my $sql = shift; my $sth = $mssql_dbh->prepare($sql); $sth->execute() || die $sth->errstr; # array indices for each returned row my $rs = $sth->fetchall_arrayref( { id => 1, account_number => 1, box => 1, number => 1, address => 1, type => 1, quantity => 1, count => 1, new_line => 1, date => 1, } ); return $rs; }

This first call as example, if I am retrieving all the records by calling all the rows from the table, this works fine.
... my $sql = "select id, account_number, box,number,address, type, quant +ity, count, new_line, date from my_table where id <>'' order by date desc"; my $results = $self->_all_data($sql); ...
This call, I would only need to retrieve 3 rows, but I would like to still use the same sub _all_data(), thats the issue, cause the _all_data() is expecting all the columns and I only need 3 this time.
... my $sql = "select id, account_number, date from my_table where id <>'' order by date desc"; my $results = $self->_all_data($sql); ...
Thanks for helping!

Comment on My doubts about using fetchall_arrayref
Select or Download Code
Replies are listed 'Best First'.
Re: My doubts about using fetchall_arrayref
by CountOrlok (Friar) on Apr 02, 2013 at 13:35 UTC
    Your post appears to be confusing the concept of rows and columns.

    I would recommend adding an additional parameter to _all_data(). The third parameter could be an arrayref. So the function could look like this:

    sub _all_data { my $self = shift; my $sql = shift; my $columns = shift; my %column_hash = map { $_, 1 } @$columns; my $sth = $mssql_dbh->prepare($sql); $sth->execute() || die $sth->errstr; # array indices for each returned row my $rs = $sth->fetchall_arrayref( \%column_hash ); return $rs; }
    Now you have to pass the function a list of columns you want it to return.

    As hdb mentioned, passing {} to fetchall_arrayref will return all columns. With this above version of _all_data, if you do not pass a third parameter, you will essentially be passing {} to fetchall_arrayref and therefore get all columns.

      That is interesting, do you mean by "pass the function a list of columns you want" like this?
      ... my $sql = "select id, account_number, date from my_table where id <>'' order by date desc"; my @columns = qw( id account_number box ); my $results = $self->_all_data($sql, \@columns); ...
        Yes. Though, to be nit-picky, in your example the third item in the list should be date, not box.
      I have a question for you, what if in the SQL query, you would have something like this: ... #my @columns = qw( id account_number, date); my $sql = "select id, account_number, CONVERT(VARCHAR(10),date,101) as 'the date' from my_table where id <>'' order by date desc"; ...
        I am sure you can't use 'the date' as a column name in sybase (which I think is what you are using). Try:
        my @columns = qw( id account_number the_date); my $sql = "select id, account_number, CONVERT(VARCHAR(10),date,101) as + the_date from my_table where id <> '' order by date desc";
Re: My doubts about using fetchall_arrayref
by hdb (Prior) on Apr 02, 2013 at 13:30 UTC

    In my understanding, fetchall_arrayref would return all the columns you specify in your sql statement and

    { id => 1, account_number => 1, box => 1, number => 1, address => 1, type => 1, quantity => 1, count => 1, new_line => 1, date => 1, }

    is not needed anyway? In which case, your _all_data function works with either sql statement. If you just provide an empty hash reference it should work and your _all_data function becomes more useful:

    my $rs = $sth->fetchall_arrayref({});

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2015-07-31 01:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (274 votes), past polls