Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

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 drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2018-12-12 07:44 GMT
Find Nodes?
    Voting Booth?
    How many stories does it take before you've heard them all?

    Results (57 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!