Re: My doubts about using fetchall_arrayref

[CountOrlok]

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.

[Anonymous Monk]

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);
...

[CountOrlok]

Yes. Though, to be nit-picky, in your example the third item in the list should be date, not box.

[hdb]

As there is redundant information in the sql statement and the list of columns, you could also do

...
my @columns = qw( id account_number date );

my $sql  = "select ".join( ",", @columns).
                " from my_table where id <>''
                order by date desc";

my $results = $self->_all_data($sql, \@columns);
...

[Anonymous Monk]

Yes, my fault, but doing that whay I am getting an error now with HTML::Template:

There has been an error: HTML::Template::param() : attempt to set para
+meter 'results' with a scalar - parameter is not a TMPL_VAR!<br>
Any idea why?

[Anonymous Monk]

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"; ...

[CountOrlok]

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";

[mpeppler]

This is pretty off-topic, but you can indeed use column labels with spaces or other special characters, as long as they are quoted.

So

select foo as 'this is the label' from bar

is perfectly valid, and DBD::Sybase will set the column name to 'this is the label'.

Michael

[Anonymous Monk]

I know, but my question was in regards of this situation:
How would I pass " CONVERT(VARCHAR(10),date,101)" to the names of columns. From this code:

...
my $sql  = "select id, account_number, CONVERT(VARCHAR(10),date,101) a
+s date
                from my_table where id <>''
                order by date desc";

my @columns = qw( id account_number box );

my $results = $self->_all_data($sql, \@columns);
...

To this:

...
my @columns = qw( id account_number date );

my $sql  = "select ".join( ",", @columns).
                " from my_table where id <>''
                order by date desc";

my $results = $self->_all_data($sql, \@columns);
...