Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

DBI Hash of Column name values

by avrono (Novice)
on Aug 09, 2010 at 15:07 UTC ( #853842=perlquestion: print w/replies, xml ) Need Help??
avrono has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I currently populate an array with hard coded column names like so (when doing a DBI query)... while (my @data = $sth->fetchrow_array()) { push(@result, { someCol => $data[0], someCol2 => $data1, }); } Is it possible to get the same result, however have the col_names pulled from the DB ? Also Could I do that with bind_columns ? I was trying something like this : $sth->bind_columns(\@result{@{$sth->{NAME_lc}}}); while ($sth->fetch) { push(@resultSet, \@result); } But can`t seem to dereference the array later, any ideas ?

Replies are listed 'Best First'.
Re: DBI Hash of Column name values
by Corion (Pope) on Aug 09, 2010 at 15:11 UTC

    See DBI about the Slice option:

    my $result = $sth->fetchall_arrayref({});
Re: DBI Hash of Column name values
by Tux (Abbot) on Aug 09, 2010 at 15:49 UTC

    The DBI docs also have a written-out example for that, giving you the advantage of speed combined with the ease of hash access, something like:

    my %rec; my $sth = $dbh->prepare ("select region, sales from sales_by_region"); $sth->execute; $sth->bind_columns (\(@rec{@{$sth->{NAME_lc}}})); while ($sth->fetch) { print "$rec{region}: $rec{sales}\n"; }

    Enjoy, Have FUN! H.Merijn
Re: DBI Hash of Column name values
by derby (Abbot) on Aug 09, 2010 at 16:05 UTC

    I don't know, bind columns always have a bad smell about them. I prefer the slice alternative where your returned resultset is an array of hashes:

    my $rows = $dbh->selectall_arrayref( $sql, { Slice => {} } );
    Now I can do this:
    foreach my $row ( @$rows ) { print $row->{NAME}, "\n"; }


      Still slower than bind:

      { my $t0 = [ gettimeofday ]; my $n = 0; my $rows = $dbh->selectall_arrayref ($sql, { Slice => {} }); foreach my $row (@$rows) { $n++; } printf STDERR "slice: %9.2f recs/sec\n", $n / tv_interval ($t0); } { my $t0 = [ gettimeofday ]; my ($n, %rec) = (0); my $sth = $dbh->prepare ($sql); $sth->execute; $sth->bind_columns (\@rec{@{$sth->{NAME_lc}}}); while ($sth->fetch) { $n++; } printf STDERR "bind: %9.2f recs/sec\n", $n / tv_interval ($t0); } => with postgres on other server: slice: 23842.81 recs/sec bind: 27994.01 recs/sec with postgres on local server: slice: 214963.65 recs/sec bind: 505370.38 recs/sec


      Enjoy, Have FUN! H.Merijn

        Interesting ... YMMV most definitely. Not sure if it's due to a difference in the databases, or the drivers or the data itself ... but using your approach against a 43 col table (mixed data types), I get:

        slice: 14021.59 recs/sec bind: 6750.10 recs/sec

        Note, my solution is using bind as suggested ...
      So $rows is a ref to an Array of Hashes ?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://853842]
Approved by BioLion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (2)
As of 2018-08-18 12:08 GMT
Find Nodes?
    Voting Booth?
    Asked to put a square peg in a round hole, I would:

    Results (185 votes). Check out past polls.