Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

How does DBI return an arrayref with key/value pairs?

by walkingthecow (Friar)
on Jun 21, 2013 at 14:09 UTC ( #1040177=perlquestion: print w/replies, xml ) Need Help??
walkingthecow has asked for the wisdom of the Perl Monks concerning the following question:

For example:
$rows = $dbh->selectall_arrayref( " SELECT name,age,gender FROM people ", { Slice => {} } ); foreach my $row (@$rows) { my $name = $row->{name}; my $age = $row->{age}; my $gender = $row->{gender}; }
All the documentation I am reading on arrayrefs is accessing the results like so:
$ { $array_ref } [2]
I have yet to find a single one that accesses the value with a key, like DBI does (e.g., $row->{column}). So, this leaves me wondering exactly how the DBI module returns an arrayref whose values can be accessed with keys (the column names). If anyone could help me understand this, or point me in the right direction, it would be greatly appreciated.

Replies are listed 'Best First'.
Re: How does DBI return an arrayref with key/value pairs?
by hdb (Monsignor) on Jun 21, 2013 at 14:34 UTC


    You may often want to fetch an array of rows where each row is stored as a hash. That can be done simple using:

    my $emps = $dbh->selectall_arrayref( "SELECT ename FROM emp ORDER BY ename", { Slice => {} } ); foreach my $emp ( @$emps ) { print "Employee: $emp->{ename}\n"; }

    So despite the name you get a reference to an array of hash references.

    UPDATE: Have you tried looking at it using Data::Dumper? Should show the structure being a hash at the bottom.

      Well, my tired eyes must need some sleep, because I seem to have glazed right over that when reading that. I was getting bothered at the fact that at one point it says, " It returns a reference to an array containing a reference to an array (or hash, see below)" and was thinking, "See below where?!" Turns out I either need glasses or some sleep. Thanks for clearing that up for me.

      I did do a print Dumper on it, and what I was seeing was a hash, but somewhere I read in the documentation (trying to find it now) that it returns "a reference to an array of array refs," and my brain was thinking, "Wow, so somehow a reference to an array of array refs becomes a hash?" Silly brain.

        That is a very useful observation, hdb.   Meanwhile, walkingthecow, I hope that you did not take my rather-flippant comment wrongly.   Nothing was intended, but upon re-read I see that it could have slapped.   Hope not.   (But if not ... “it was moooo...”)

Re: How does DBI return an arrayref with key/value pairs?
by BillKSmith (Vicar) on Jun 21, 2013 at 14:43 UTC
    Quote from DBI
    If $slice is a hash reference, fetchall_arrayref uses fetchrow_hashref to fetch each row as a hash reference.
    Note that you are specifing $slice as a hashref.
Re: How does DBI return an arrayref with key/value pairs?
by ruzam (Curate) on Jun 22, 2013 at 03:02 UTC

    As BillKSmith pointed out:

     { Slice => {} }

    is your answer. The Slice tells DBI what format you want the selected records to be returned as. You've asked for them to be returned as hash references {} so that's what you get.

    You could have also used:

     { Slice => [] }

    in which case, each selected row would be returned as an array reference. You wouldn't get the table column names (hash keys), instead you would just get an array of the row values and it would be up to you to remember the order of the columns selected.

    Returning the records as hash references is slower than returning the records as array references. But returning records as array references can make the code harder to read (and harder to find bugs). Especially if you select all columns generically using queries like "Select * From". Returning the records as array references could blow up on you horribly in this situation if the database table layout changes in the future. I avoid using "Slice => []" unless the number of columns selected is small or I'm expecting huge numbers of returned records and performance is critical.

      All valuable info. I'd like to also add that dbh->selectall_arrayref( $Sql ) by default returns a Reference to an Array of References of each row fields stored in an Array, so it's essentially it's a Reference to an Array of References of row-Arrays, thus there is no need to pass extra parameter { Slice => [] }, since that's a default behavior. Only if you want your individual rows be stored in Hashes instead of Arrays do you need to pass { Slice => {} } as a second parameter, so your call would look like this: dbh->selectall_arrayref( $Sql, { Slice => {} } )
Re: How does DBI return an arrayref with key/value pairs?
by erix (Parson) on Jun 21, 2013 at 14:24 UTC
    #!/opt/perl-5.18/bin/perl use strict; use warnings; use DBI; main(); exit; sub main { my $dbh = DBI->connect('dbi:Pg:',undef,undef,{RaiseError=>1, Print +Error=>1, AutoCommit=>0,}) or die "oops - $!\n"; my $sql = " select * from ( values (1, 100, 1000) -- row 1 , (2, 200, 1500) -- row 2 ) as f(id, debet, credit) order by id; "; my $sth = $dbh->prepare( $sql ) or die "oops - prepare - $!\n"; my $rc = $sth->execute() or die "oops - execute - $!\n"; my %row; $sth->bind_columns( \( @row{ @{ $sth->{ NAME_lc} } } ) ); while ($sth->fetch) { print " id ", $row{ id } , "\n"; print " credit ", $row{ credit } , "\n"; print " debet ", $row{ debet } , "\n\n"; } $dbh->disconnect; } # note that fetch is an alias for fetchrow_arrayref


    $ perl id 1 credit 1000 debet 100 id 2 credit 1500 debet 200
Re: How does DBI return an arrayref with key/value pairs?
by PerlSufi (Friar) on Jun 21, 2013 at 14:23 UTC
    Hello, I'm not sure I completely understand your question, but I have used a little of the DBI module to produce excel sheets.
    I did something like this:
    my $row=0; my $col=0; $xWS->write($row, $col++, $_) for @{$sth->{NAME}}; # Read the query results and write them into the spreadsheet while (my $ar=$sth->fetchrow_arrayref) { ++$row; $col=0; $xWS2->write($row, $col++, $_) for @$ar; }
    Actually, I won't take much credit for that. I found something similar on the perlmonks site itself. I hope this helps..
      I can understand the misunderstanding here since my wording may not be the best on this one. I am asking how DBI does it, not how to do with DBI (i.e., how does vs. how to). The code in my question works, but I don't fully understand how it works. I don't get how the arrayref that gets returned has values that can be accessed with a key.
Re: How does DBI return an arrayref with key/value pairs?
by grantm (Parson) on Jun 24, 2013 at 01:59 UTC

    I think you'll find the enlightenment you're after in perlreftut - Mark's very short tutorial about references.

    When you called $rows = $dbh->selectall_arrayref, DBI returned a single scalar value which you assigned to $rows. That value was a reference to an array. Each element in that array was a reference to a hash of column names and values.

    The array-dereferencing syntax you quoted:

    $ { $array_ref } [2]

    is exactly equivalent to this syntax:


    And in your case would return a hashref.

    my $hash_ref = $array_ref->[2]; my $value = $hashref->{$key};

    You can also chain together the dereferencing like this:

    my $value = $array_ref->[2]->{$key};

    And you can even omit the arrow when the initial data structure contains references:

    my $value = $array_ref->[2]{$key};
Re: How does DBI return an arrayref with key/value pairs?
by sundialsvc4 (Abbot) on Jun 21, 2013 at 14:28 UTC

    You cannot have searched seriously through DBI and failed to have noticed the entry for fetchrow_hashref() ...

      Yeah that's what I thought at first too, sundialsvc4. But the OP apparently wanted to know how the module did it, not how to actually do it with the module.
        There is a distinction between the two following questions:
        1. How does a television work?
        2. How do I work a television?
        Can you spot it?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1040177]
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2018-06-23 23:58 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.