Perl -- DBI selectall_arrayref when querying getting Not Hash Reference

Question

Hi

I am very new to perl (but from a c# background) and I am trying to move some scripts to a windows box. Due to some modules not working easily with windows I have changed the way it connects to the DB. I have an sqlserver DB and I had a loop reading each row in a table, and then within this loop another query was sent to select different info. I was the error where two statements can't be executed at once within the same connection. As my connection object is global I couldn't see an easy way round this, so decided to store the first set of data in an array using:

my $query = shift;
    my $aryref = $dbh->selectall_arrayref($query) || die "Could not select to array\n";
    return($aryref);

(this is in a module file that is called)

I then do a foreach loop (where @$s_study is the $aryref returned above)

  foreach my $r_study ( @$s_study ) {
    ~~~             
    my $surveyId=$r_study->{surveyid};  <-------error this line
 ~~~~               
        };

When I run this I get an error "Not a hash reference". I don't understand?! Can anyone help!

Bex

Answer 1

$r_study->{surveyid} is a hashref

$r_study->[0] is an arrayref

this is your error. You should use the second one

Answer 2

You need to provide the { Slice => {} } parameter to selectall_arrayref if you want each row to be stored as a hash:

my $aryref = $dbh->selectall_arrayref($query, { Slice => {} })

See perldoc DBI for more details.

Answer 3

If you have a problem with a method, then a good first step is to read the documentation for that method. Here's a link to the documentation for selectall_arrayref. It says:

This utility method combines "prepare", "execute" and "fetchall_arrayref" into a single call. It returns a reference to an array containing a reference to an array (or hash, see below) for each row of data fetched.

So the default behaviour is to return a reference to an array which contains an array reference for each row. That explains your error. You're getting an array reference and you're trying to treat it as a hash reference. I'm not sure that the error could be much clearer.

There is, however, that interesting bit where it says "or hash, see below". Reading on, we find:

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 you have two options. Either switch your code to use an array ref rather than a hash ref. Or add the "{ Slice => {} }" option to the call, which will return a hash ref.

The documentation is clear. It's well worth reading it.