Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
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
Re: My doubts about using fetchall_arrayref
by hdb (Parson) 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({});
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";

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 romping around the Monastery: (13)
As of 2014-07-22 13:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (113 votes), past polls