http://qs321.pair.com?node_id=11122192


in reply to Retrieving column names from SQL with DBI

If you use a hashref as the slice arg to fetchall_arrayref you will receive an AoH rather than an AoA. Then it becomes trivial:

my $query = $connect->prepare ("SELECT * FROM TableName"); $query->execute (); my $data = $query->fetchall_arrayref ({}); for my $row (@$data) { for my $key (keys %$row) { print "Column name is $key, column value is $row->{$key}\n"; } }

🦛

Replies are listed 'Best First'.
Re^2: Retrieving column names from SQL with DBI
by Tux (Abbot) on Sep 25, 2020 at 09:16 UTC

    Note that fetching hashrefs is (much) slower than fetching arrayrefs. Most often this is marginal compared to the time spent in getting the data from the database, but in many cases, you can bring the speed back by using bind_columns:

    my $sth = $dbh->prepare ("select * from foo"); $sth->execute; my (@fields, %r) = @{$sth->{NAME_lc}}; $sth->bind_columns (\@r{@fields}); while ($sth->fetch) { # Do something with the record. Fields are stored in %r printf "%-16s: %s\n", $_, $r{$_} // "--undef--" for @fields; }

    Enjoy, Have FUN! H.Merijn
Re^2: Retrieving column names from SQL with DBI
by Krillian (Acolyte) on Sep 25, 2020 at 18:42 UTC
    Dear all, Thank you very much for all your suggestions. Went with fetchall_arrayref for now and does exactly what I need. Will try out the other solutions later as well. Again, thank you!