From http://search.cpan.org/~timb/DBI-1.53/DBI.pm:

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";
  }
[download]

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...”)

If $slice is a hash reference, fetchall_arrayref uses fetchrow_hashref to fetch each row as a hash reference.

As BillKSmith pointed out:

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:

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 => {} } )

#!/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
[download]

output:

$ perl arrayref.pl
      id  1
  credit  1000
   debet  100

      id  2
  credit  1500
   debet  200
[download]

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;
                        }
[download]

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.

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:

is exactly equivalent to this syntax:

And in your case would return a hashref.

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

You can also chain together the dereferencing like this:

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

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

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

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?