How to create Hash with single key and multiple values?

user_19245937 0
Viewed 53

I have the database which I am accessing, my goal is to store every row in hash where id would be key and other things present in the row i.e. other columns as values. Actually I am parsing the XLSX sheet and storing its values, after that have to apply logic to fetch database values and later on compare values of XLSX sheet and DB, if they are equal then test is pass else fail. I almost spent 3 days but not getting any results.

Output
@carrierValuesAll[0] = 1 1 https://au-sbc.trustidinc.com/tid sbcLabStub sbcLab SKY 0 2019-11-07 20:10:43 2021-07-02 04:39:43 TrustID Lab Oracle Y Y Y ivr.localdomain Y trustid

@carrierValuesAll1 = 2 1 https://au-sbc.trustidinc.com/tid sbcLab sbcLab SKY 2 2019-11-07 20:10:43 2020-12-14 06:24:17 TrustID Lab Oracle Y Y Y ivr.localdomain Y admin

Desired Output
Hash --> Key = 1
Values = 1,https://au-sbc.trustidinc.com/tid,sbcLabStub,sbcLab,SKY,0,2019-11-07,20:10:43,2021-07-02,04:39:43, TrustID, Lab Oracle, Y, Y, Y, ivr.localdomain, Y, trustid
Similarly for other iterations or id's in loop where id 2 will be key 2 and its values are key 2 values.This is The table which I am accessing and how it looks like

Table looks like

The code I am using for this is below:

    #!/usr/bin/perl
    use DBI;
    use strict;
    
    use warnings;
    use Switch;
    my @tstInfo;
    my %DbHash;
    
    my @carrierValuesAll=();
    my @carrierValuesAllGet="";
    
    if($dbType eq "mysql"){
    #$database = "trustid";
    $dsn = "DBI:$dbType:database=$database;host=$host:port=3306,DBSOCK = '/var/run/mysqld/mysqld.sock'";
    $userid = "XXXX";
    $password = 'XXXX';
    }else{
    $dbType="postgres";
    $userid = "postgres";
    $password = "postgres";
    $dsn = "DBI:$dbType:database=$database;host=$host:port=5432";
    }
    
    my $dbcon = DBI->connect($dsn, $userid, $password) or die "Can't connect to database: $DBI::errstr\n";
    print "connected to the database\n";
    
    my $notables = $dbcon->tables();
    print "No of tables : $notables"."\n" ;
    my @tables = $dbcon->tables(undef, undef, undef, 'TABLE');
    
    my $len = @tables;
    for (my $i = 0; $i < $len; $i = $i + 1)
        {
    print ("\@tables[$i] = $tables[$i]\n");
    print("\n");        
    } 
    my $sth = $dbcon->prepare('select * from active_table_set');
    
    $sth->execute();
     
    while (my @row = $sth->fetchrow_array()) {
       $database=join(',', @row);
       print($database."\n");
       if($dbType eq "mysql"){
    #$database = "trustid";
    $dsn = "DBI:$dbType:database=$database;host=$host:port=3306,DBSOCK = '/var/run/mysqld/mysqld.sock'";
    $userid = "XXXX";
    $password = 'XXXX';
    }else{
    $dbType="postgres";
    $userid = "postgres";
    $password = "postgres";
    $dsn = "DBI:$dbType:database=$database;host=$host:port=5432";
    }
    $dbcon = DBI->connect($dsn, $userid, $password) or die "Can't connect to database: $DBI::errstr\n";
    print "connected to the database\n";
    @tables = $dbcon->tables(undef, undef, undef, 'TABLE');
    $len = @tables;
    for (my $i = 0; $i < $len; $i = $i + 1)
        {
    print ("\@tables[$i] = $tables[$i]\n");
    print("\n");
    if($tables[$i] eq '`trustid_b`.`carriers`'){
        $sth = $dbcon->prepare('select * from carriers');
    
    $sth->execute();
    while (my @row = $sth->fetchrow_array()) {
        push(@carrierValuesAll, [ @row ]);
    }
    }
    
    } 
    }
    $len = @carrierValuesAll;
    for (my $i = 0; $i < $len; $i = $i + 1) {
        print ("\@carrierValuesAll[$i] = @{$carrierValuesAll[$i]}\n");
        print("\n"); 
        
        push(@tstInfo,\@carrierValuesAll[$i]);
    }
    for(my $i = 0; $i <= $#tstInfo; $i++){
    # push the test data  on the array
         $DbHash{$i+1} =  $tstInfo[$i];
    }
    print(%DbHash).("\n");



Can I create the Hash Here

while (my @row = $sth->fetchrow_array()) {
        push(@carrierValuesAll, [ @row ]);
    }

Yes, Then How?

What I was doing

Storing each row in scalar which was giving me output as below:
Output after running the perl script2 Where, I was trying to push my each row in @carrierValuesAll and then pushing @carrierValuesAll in @tstInfo and trying to populate hash after that. I don't know how to access this, Whether this is correct or not. Please help me.

dbi perl
1 Answers

I have the database which I am accessing, my goal is to store every row in hash where id would be key and other things present in the row i.e. other columns as values

That's probably not what you want. What you actually want is a two-level hash, where the first level has the id as a key and a hash reference as the value. The second level hash would have column names as the keys mapping to the associated values.

One easy way to do that is to use fetchrow_hashref() instead of fetchrow_array().

my %carriers;

while (my $row = $sth->fetchrow_hashref) {
  $carriers{$row->{id}} = $row;
}
user_72310
Related