Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: My doubts about using fetchall_arrayref

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


in reply to My doubts about using fetchall_arrayref

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.


Comment on Re: My doubts about using fetchall_arrayref
Download Code
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); ...
      Yes. Though, to be nit-picky, in your example the third item in the list should be date, not box.

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2014-09-23 08:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (212 votes), past polls