Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Having problems with DBI selectall_arrayref

by SergioQ (Beadle)
on Dec 26, 2020 at 16:36 UTC ( #11125748=perlquestion: print w/replies, xml ) Need Help??

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

Oh help me please Holy Monks, this one is killing me.

I have an old pm module that does all the connecting and querying with MySQL, so I have faith in it.

Now I am trying to retrieve multiple rows with all columns using selectall_hashref and am tripping all over myself.

my $data_all =  $connection->selectall_hashref($query, { 'Columns' => {} } );

And I get back: Not an ARRAY reference at /usr/local/lib/x86_64-linux-gnu/perl/5.26.1/DBI.pm line 2106.

I know all the column names, this is not a read any strange table function.

Somewhere my approach has it all wrong, should I take a different approach?

What I am trying to get back is an array of hash references, that's al, to my multiple row query, and yes my query works manually.

Can you help me?

p.s. I found this thread <a href="https://www.perlmonks.org/?node_id=1040177 "target="_blank> How does DBI return an arrayref with key/value pairs? so I tried that, switching to selectall_arrayref

But when I ran

for my $row (@$data_all)<br> {<br> my %currec = $row;<br> foreach my $columns (keys %currec){<br> say $columns;<br> say $currec{$columns};<br> }<br> }

What I got back was that $columns was another hash, and that led my second "say" to output nothing.

So confused

Replies are listed 'Best First'.
Re: Having problems with DBI selectall_arrayref
by 1nickt (Abbot) on Dec 26, 2020 at 17:58 UTC

    Hi,

    "What I am trying to get back is an array of hash references, that's al, to my multiple row query"

    You may just have a dereferencing problem, but anyway I think you are reaching for Slice with an empty value?

    use strict; use warnings; use feature 'say'; use DBD::SQLite; use Data::Dumper; # set up and populate an in-memory DB my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", undef,undef,{ RaiseError => 1 }); $dbh->do(q{ create table data( id integer primary key, last_name text, first_name text, role text) }); my $insert_sth = $dbh->prepare(q{ insert into data values (?,?,?,?); }); while ( my $record = <DATA> ) { chomp($record); my @values = split(' ', $record); $insert_sth->execute(@values); } # Select everything (!) my $ref = $dbh->selectall_arrayref(q{ select * from data }, { Slice => {} }); say Dumper $ref; __END__ 1 Flintstone Fred Father 2 Flintstone Wilma Mother 3 Flinstone Pebbles Child 4 Flintstone Dino Pet 5 Rubble Barney Father 6 Rubble Betty Mother 7 Rubble Bam-Bam Child

    Output:

    $VAR1 = [ { 'first_name' => 'Fred', 'last_name' => 'Flintstone', 'role' => 'Father', 'id' => 1 }, { 'first_name' => 'Wilma', 'last_name' => 'Flintstone', 'id' => 2, 'role' => 'Mother' }, { 'id' => 3, 'role' => 'Child', 'last_name' => 'Flinstone', 'first_name' => 'Pebbles' }, { 'first_name' => 'Dino', 'last_name' => 'Flintstone', 'role' => 'Pet', 'id' => 4 }, { 'last_name' => 'Rubble', 'role' => 'Father', 'id' => 5, 'first_name' => 'Barney' }, { 'first_name' => 'Betty', 'last_name' => 'Rubble', 'id' => 6, 'role' => 'Mother' }, { 'role' => 'Child', 'id' => 7, 'last_name' => 'Rubble', 'first_name' => 'Bam-Bam' } ];

    To print how your post has it:

    for my $row ( @$ref ) { for my $colname ( keys %$row ) { say $colname; say $row->{ $colname }; } }

    Hope this helps!


    The way forward always starts with a minimal test.
Re: Having problems with DBI selectall_arrayref
by Corion (Pope) on Dec 26, 2020 at 17:15 UTC

    ->selectall_hashref does not return an array of hashrefs. You want ->selectall_arrayref:

    my $data_all = $connection->selectall_arrayref($query, { 'Columns' => + {} } );
      Was just editing my question, you can see the update there. It still is confusing on the return.

        Sorry, I posted my reply too hastily. The second parameter you pass to ->selectall_arrayref must be a hash with the key Slice, not Columns:

        my $data_all = $connection->selectall_arrayref($query, { Slice => {} +} );

        Then, each element of $data_all will be a hashref (not a hash as you assume):

        my $currec = $row; foreach my $columns (keys %$currec){ say $columns; say $currec{$columns}; }

        Whenever you're in doubt about a data structure, consider using Data::Dumper to print it:

        use Data::Dumper; say Dumper $row;

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11125748]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (4)
As of 2021-04-10 12:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?