Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re^3: My doubts about using fetchall_arrayref

by CountOrlok (Friar)
on Apr 02, 2013 at 14:54 UTC ( #1026707=note: print w/ replies, xml ) Need Help??


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

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


Comment on Re^3: My doubts about using fetchall_arrayref
Download Code
Re^4: My doubts about using fetchall_arrayref
by Anonymous Monk on Apr 02, 2013 at 15:02 UTC
    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); ...

      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.

        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); ...
Re^4: My doubts about using fetchall_arrayref
by mpeppler (Vicar) on Apr 03, 2013 at 04:55 UTC
    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

      Thanks. That is good to know.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1026707]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2014-07-12 18:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (240 votes), past polls