How to fetch multiple columns from SQL query and store them in 2d array in perl

Question

Suppose I have a query like:

my $sql = "SELECT dev_name,
                  OID_name,
                  Obj_Val
           FROM dev_logs";

where the table has values like

+----+------------+----------------------+---------+---------------------+
| id | dev_name   | OID_name             | Obj_Val | timeStamp           |
+----+------------+----------------------+---------+---------------------+
|  1 | iKazatseva | ubntWlStatRssi       | 29      | 2017-07-22 15:18:34 |
|  2 | iKazatseva | ubntWlStatSignal     | -67     | 2017-07-22 10:12:32 |
|  3 | iKazatseva | ubntWlStatCcq        | 91      | 2017-07-22 15:18:34 |
|  4 | iKazatseva | ubntWlStatNoiseFloor | -96     | 2017-07-27 16:45:24 |
+----+------------+----------------------+---------+---------------------+

How can I store the values returned by the query in a 2D-array like the following?:

+------------+----------------------+---------+
| dev_name   | OID_name             | Obj_Val |
+------------+----------------------+---------+
| iKazatseva | ubntWlStatRssi       | 29      |
| iKazatseva | ubntWlStatSignal     | -67     |
| iKazatseva | ubntWlStatCcq        | 91      |
| iKazatseva | ubntWlStatNoiseFloor | -96     |
+------------+----------------------+---------+

I have tried some things but all I could get was either bind them in a 1d array like:

my @devLogsArr = $dbh->selectall_arrayref($sql);

or

my @OID_names= map {$_->[1]}
  @{$dbh->selectall_arrayref($sql)};

or bind them in variables like:

$sth->bind_col(1, \$devname);
$sth->bind_col(2, \$OID);
$sth->bind_col(3, \$value);

print "$devname\t$OID\t$value\n" while $sth->fetchrow_arrayref;

my @devLogsArr;
push(@devLogsArr, (devname=> $devname, OID=> $OID, value=> $value))
while $sth->fetchrow_arrayref;

but its far from what I would like to do. I know that i can be done by querying the DB for each individual column but that would be redundant.

Is what I am asking here feasible?

Thanks in advance.

Answer 1

The selectall_arrayref() returns an array reference. See it in DBI. Thus we can use it as

my $all_rows = $dbh->selectall_arrayref($sql);

assigning it to a variable, a scalar, $all_rows. To dereference this and create an array

my @all_rows = @{ $all_rows };

where I use the same name ("all_rows") only to show that the scalar ($) and the array (@) with that same name are different variables. That is not the practice I'd recommend though, as confusion may result; choose names carefully. In this case the { } aren't needed and @$all_rows is fine.

Now you can print the contents

foreach my $row (@all_rows) { 
    print "@$row\n";
}

where each $row retrieved from the array is itself an arrayref so we dereference it (@$row), or

print "@$_\n" for @all_rows;

References are very convenient for work, so there may not be much of a reason to create the actual array with rows; just keep it in $all_rows. To print them out

print "@$_\n" for @$all_rows;

where @$all_rows dereferences $all_rows and creates a list that for iterates over, putting each element in the special $_ variable. That is then dereferenced @$_, and interpolated "@$_" in printing so that we get spaces between the elements for a legible readout.

Literature: tutorial perlreftut, cookbook perldsc, and reference perlref.

---

Now, in order to add column names in some way to this it is important to first articulate the purpose of doing that. If it's for a lookup, then what should be looked up by what? If you query by column name, then how do you want to be able to access elements of a row? By index, or again by name somehow? Do you want to be able to conveniently iterate through it as well? Maintain the oder of columns?

At this point we are practically implementing a relational database style functionality.

So perhaps just keep it simple. The arrayref has information, and for display you can simply print column names first. Formatting can be taken care of via printf, with details available in sprintf.