Re^3: My doubts about using fetchall_arrayref

[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

[CountOrlok]

Thanks. That is good to know.

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

[hdb]

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({});

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.

[Anonymous Monk]

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({});

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