in reply to Re^3: My doubts about using fetchall_arrayref
in thread My doubts about using fetchall_arrayref

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

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

[download]

Replies are listed 'Best First'.
Re^5: My doubts about using fetchall_arrayref
by hdb (Monsignor) on Apr 02, 2013 at 15:13 UTC

    You cannot. My proposal only works for simple column names. Do you really need to pass the columns names? Can you not use 

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

    [download]

    without specifying the columns? According to the documentation it should work but I do not have your database to test it. This way everything would be controlled by your sql only.

[reply]
[d/l]
      In this case I will use the sample below for SQL queries that require a more complex call, yes I could use this: 
      my $rs = $sth->fetchall_arrayref({});

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

      [download]
[reply]
[d/l]
[select]
        If you have a more advanced query like this one: 
        my $sql = "select id, account_number, CONVERT(VARCHAR(10),date,101) as
+ date
                from my_table where id <> ''
                order by date desc";

        [download]
        Then you can call the version of _all_data() that I gave above as either:
        my @columns = qw( id account_number date );
my $results = $self->_all_data($sql, \@columns);

        [download]
        or
        my $results = $self->_all_data($sql);

        [download]
        They will both get the same result set.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom