Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks

I'm a perl beginner and can't see what is up with the following code 


 $sql = "select * from table limit 10";
 $sth = $db->prepare($sql);


$sth->execute()  or die "SQL Error: $DBI::errstr\n";
my $rows = $sth->fetchall_arrayref({ Slice => {}});

foreach my $row ( @$rows ) {
    print $row->{'id'}, "\n";
  }

[download]
I though each element in the array was a hash ref and i have confirmed this by ref($row) but nothing is being output? The data is there as this works 
 $sth->execute
 or die "SQL Error: $DBI::errstr\n";
 while (@row = $sth->fetchrow_array) {
    print $row[0], "\n";
 }

[download]
thanks

Replies are listed 'Best First'.
Re: problem with fetchall_arrayref
by roboticus (Chancellor) on Dec 30, 2010 at 16:14 UTC

    I'm guessing you don't have a column named "Slice". As I read the docs, using a non-empty hash reference tells it to return only the columns named in the hash--Slice, in this case. Perhaps you meant to return all rows as hashes containing all columns like this:

    my $rows = $sth->fetchall_arrayref( {} );

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
Re: problem with fetchall_arrayref
by derby (Abbot) on Dec 30, 2010 at 23:54 UTC

    You want selectall_arrayref: 

    $sql = "select * from table limit 10";
$rows = $db->selectall_arrayref( $sql, { Slice => {} } );

foreach my $row ( @$rows ) {
  print $row->{id}, "\n";
}

    [download]

    -derby
[reply]
[d/l]
Re: problem with fetchall_arrayref
by Anonymous Monk on Dec 30, 2010 at 16:16 UTC
    having done this 
    my $sql_return = $sth->fetchall_arrayref({ Slice => {}});
print Dumper $sql_return;

    [download]
    I can see that the code doesn't do at all what I intended. How do i get each row as a hash_ref? I have to use a statement handler. I looked at fetchall_hashref and that requires that i define a key field and I don't want to.

    many thanks
[reply]
[d/l]

      If you don't want to define a key field, then you can't get the functionality you want. Come on, be reasonable. :)

      Note that $key_field can be an arrayref, a column number, or an arrayref containing a list of column numbers: 

      #!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;
$|++;

my $dbh = DBI->connect("dbi:mysql:wordpress","username","password");
my $sth = $dbh->prepare('SELECT ID, placeName FROM wp_ajax_places WHER
+E ID <= 5');
$sth->execute;
my $h = $sth->fetchall_hashref([ 1..2 ]);

print Dumper(\$h);

      [download]
      Output: 
      $VAR1 = \{
            '4' => {
                     'Akiachak AK' => {
                                        'ID' => '4',
                                        'placeName' => 'Akiachak AK'
                                      }
                   },
            '1' => {
                     'Ester AK' => {
                                     'ID' => '1',
                                     'placeName' => 'Ester AK'
                                   }
                   },
            '3' => {
                     'Akhiok AK' => {
                                      'ID' => '3',
                                      'placeName' => 'Akhiok AK'
                                    }
                   },
            '2' => {
                     'Aguikchuk AK' => {
                                         'ID' => '2',
                                         'placeName' => 'Aguikchuk AK'
                                       }
                   },
            '5' => {
                     'Akiak AK' => {
                                     'ID' => '5',
                                     'placeName' => 'Akiak AK'
                                   }
                   }
          };

      [download]
      -- 
Education is not the filling of a pail, but the lighting of a fire.
 -- W. B. Yeats
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom