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
– zdim, Commented Jul 29, 2017 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
– BugShotGG, Commented Jul 29, 2017 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
– Ron Bergin, Commented Jul 29, 2017 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
– zdim, Commented Jul 29, 2017 at 19:56
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
– zdim, Commented Jul 29, 2017 at 20:06

zdim · Accepted Answer · 2017-07-29 21:11:08Z

3

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.

edited Jul 29, 2017 at 21:11

answered Jul 29, 2017 at 20:22

zdim

67.2k5 gold badges59 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BugShotGG Over a year ago

Thanks. I believe I should try a tutorial for perl :)

zdim Over a year ago

@BugShotGG Welcome. Yes, that would probably be good :). i list docs for references, for other stuff browse perldoc.perl.org

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related