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.

The 2D output array ... do you want those column names to somehow be there? That would make it hard to work with. (Or are they shown just to clarify what output you want?) — zdim, Jul 29 '17 at 19:45
@zdim Not really but, for learning purpose it would be nice to see if there is a way to have titles on the colums maybe throught a special variable binding or something. :) — BugShotGG, Jul 29 '17 at 19:48
Maybe it would be better to use a HoA or HoH instead of an array? Especially if you want to use the field names as titles in your output. — Ron Bergin, Jul 29 '17 at 19:51
OK. If you want to have column names: what is the purpose -- to print conveniently, like you show? (Then, do you need to maintain order?) Or to retrieve data by name, use as lookup? (Then, how? Look up what by what?) The `$dbh->selectall_arrayref` returns precisely a "2D" array already. — zdim, Jul 29 '17 at 19:56
@zdim Its ok then we can skip the titles, as I said it doesnt matter. Btw, I tried with `selectall_arrayref` but I couldnt merge all columns in one 2d-array while fetching the result. — BugShotGG, Jul 29 '17 at 20:00
The `$selectall_arrayref` returns a reference to an array. That elements of that array are references, each to an array with values for a row. So you have a "2D array" already, it's `my $all_rows = $dbh->selectall_arrayref($sql);` So `for (@$all_rows) { print "@$_\n" };` prints the table, just without headers, in the right order. Is this what you need? — zdim, Jul 29 '17 at 20:06
There are at lease a few ways to store this _with_ column information. But then in order to choose how to do it one should decide what is that new data structure intended for, what should be easy to do with it. — zdim, Jul 29 '17 at 20:11
@zdim Yes that worked! when i have tried that i used `@` instead of `$` in `my $all_rows = $dbh->selectall_arrayref($sql);` and it didnt work. I think that confused me and so I opened this question here... Can u explain why it doesnt work with @? — BugShotGG, Jul 29 '17 at 20:11

zdim · Accepted Answer · 2017-07-29T21:11:08.267

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.

@BugShotGG Welcome. Yes, that would probably be good :). i list docs for references, for other stuff browse [perldoc.perl.org](https://perldoc.perl.org/index.html) — zdim, Jul 29 '17 at 20:31

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

1 Answers1