Trouble with Perl Data structures HoA breaking or HoH key not created

Question

This is a question about Perl data structures, but first a breif description of some details involved. I have a perl script that uses DBI and performs three queries against a mysql database ($q1 $q2 $q3). Each query returns 2 or 3 fields (which is subject to change depending upon requirements) and any number of rows. My final output needs to be a csv of all the fields and their values.

The following represents the fields and rows returned from the database.

if $q1 returns

field1   field2
id1      val_a
id2      val_w

$q2

field3               #note $q2 has returned one row
000

$q3

field4    field5    field6
val_b     val_c     val_d
val_x     val_y     val_z

Then the csv will be

field1,field2,field3,field4,field5,field6
id1,val_a,000,val_b,val_c,val_d
id2,val_w,,val_x,val_y,val_z

I attempted to collect the data with a hash of arrays like this

my @statements = ($q1,$q2,$q3);
my %HoA;
for (@statements) {

    my $sth = $dbh->prepare($_);
    $sth->execute;

    my $i=0;
    while(my @row = $sth->fetchrow_array) {
        push ( @{ $HoA{$i} }, @row[0..$#row] );
    $i++;
    }

}

I am still learning so I am not sure if this was the best choice, though it has been working for me except when one or more of the queries returns less rows than the others, this is illustrated in the above example of the fields and rows returned. And this causes the Data structure to break, the rows will run into one another. I have also tried a hash of hashes, using the fields for the keys of the inner hash

my @statements = ($q1,$q2,$q3);
my %HoH;
for (@statements) {

    my $sth = $dbh->prepare($_);
    $sth->execute;

    my $fields_ref = $sth->{NAME_lc};

    my $i=0;
    while(my @row = $sth->fetchrow_array) {
        my $v=0;
        for my $field (@$fields_ref) {
                    $HoH{$i}{$field}=$row[$v];
        $v++;
        }
    $i++;
    }
}

This runs into another problem, the inner key for the field of the row that does not exist will not be created. So a print with Data::Dumper (for the example at the beginning of my question) would look like this:

      '0' => {
               'field1' => 'id1',
               'field2' => 'val_a',
               'field3' => '000',
               'field4' => 'val_b',
               'field5' => 'val_c',
               'field6' => 'val_d',
             },
      '1' => {
               'field1' => 'id2',
               'field2' => 'val_w',   # no field3
               'field4' => 'val_x',
               'field5' => 'val_y',
               'field6' => 'val_z',
             },

So I am kinda of stuck and not sure what to try next... Maybe there is a way to retain the key for field3 and give it something like a null value? Any help please?

Answer 1

Given two tables and a legimate reason for de-normalizing them into one .csv, you should use your DBMS and not try to roll your own. There is JOIN for combining columns 'in sync' and LEFT JOIN if Eve has no car. In code:

use Modern::Perl;
use DBI;

my $dbh = DBI->connect("dbi:mysql:sotrials", "eh", "ohbnbwrg")
          || die "Could not connect to mysql:sotrials: $DBI::errstr";
$dbh->do("DROP TABLE IF EXISTS Car");
$dbh->do("DROP TABLE IF EXISTS Person");

$dbh->do("CREATE TABLE Person (Id INTEGER PRIMARY KEY, FName VARCHAR(30))");
$dbh->do("INSERT INTO Person VALUES (1, 'Adam')");
$dbh->do("INSERT INTO Person VALUES (2, 'Eve')");
$dbh->do("INSERT INTO Person VALUES (3, 'Abel')");
$dbh->do("CREATE TABLE Car (Id INTEGER PRIMARY KEY, Make VARCHAR(30), Owner INTEGER, FOREIGN KEY(Owner) REFERENCES Person(Id))");
$dbh->do("INSERT INTO Car VALUES (1, 'BMW', 1)");
$dbh->do("INSERT INTO Car VALUES (2, 'Honda', 3)");

my $sth;
$sth = $dbh->prepare("SELECT * FROM Person");
$sth->execute();
$sth->dump_results();
$sth = $dbh->prepare("SELECT * FROM Car");
$sth->execute();
$sth->dump_results();

my $fn = "./out.csv";

open(my $fho, '>', $fn) or die "Could not write open file '$fn' $!";
print $fho "FName;Car\n";
$sth = $dbh->prepare("SELECT P.FName, C.Make FROM Person P LEFT JOIN Car C On P.Id = C.Owner");
$sth->execute();
foreach my $row (@{$sth->fetchall_arrayref()}) {
    printf $fho "\"%s\";\"%s\"\n", @{$row};
}
close($fho);

$dbh->disconnect();

open(my $fhi, '<', $fn) or die "Could not read open file '$fn' $!";
print <$fhi>;
close($fhi);

output:

perl 21323885.pl
'1', 'Adam'
'2', 'Eve'
'3', 'Abel'
3 rows
'1', 'BMW', '1'
'2', 'Honda', '3'
2 rows
Use of uninitialized value $row in printf at 21323885.pl line 32.
FName;Car
"Adam";"BMW"
"Eve";""
"Abel";"Honda"