My doubts about using fetchall

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

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

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

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; }` [download] 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.	[reply] [d/l]
Re^2: My doubts about using fetchall_arrayref by Anonymous Monk on Apr 02, 2013 at 13:56 UTC
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); ...` [download]	[reply] [d/l]
Re^3: My doubts about using fetchall_arrayref by CountOrlok (Friar) on Apr 02, 2013 at 14:02 UTC
Yes. Though, to be nit-picky, in your example the third item in the list should be date, not box.	[reply]
Re^4: My doubts about using fetchall_arrayref by hdb (Monsignor) on Apr 02, 2013 at 14:10 UTC
Re^5: My doubts about using fetchall_arrayref by CountOrlok (Friar) on Apr 02, 2013 at 14:58 UTC
Re^4: My doubts about using fetchall_arrayref by Anonymous Monk on Apr 02, 2013 at 14:12 UTC
Re^5: My doubts about using fetchall_arrayref by CountOrlok (Friar) on Apr 02, 2013 at 14:51 UTC
Re^2: My doubts about using fetchall_arrayref by Anonymous Monk on Apr 02, 2013 at 14:29 UTC
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"; ...	[reply]
Re^3: My doubts about using fetchall_arrayref by CountOrlok (Friar) on Apr 02, 2013 at 14:54 UTC
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";` [download]	[reply] [d/l]
Re^4: My doubts about using fetchall_arrayref by mpeppler (Vicar) on Apr 03, 2013 at 04:55 UTC
Re^5: My doubts about using fetchall_arrayref by CountOrlok (Friar) on Apr 03, 2013 at 05:16 UTC
Re^4: My doubts about using fetchall_arrayref by Anonymous Monk on Apr 02, 2013 at 15:02 UTC
Re^5: My doubts about using fetchall_arrayref by hdb (Monsignor) on Apr 02, 2013 at 15:13 UTC
Some notes below your chosen depth have not been shown here
Re: My doubts about using fetchall_arrayref by hdb (Monsignor) 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, }` [download] 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({});` [download]	[reply] [d/l] [select]