#!/usr/bin/perl -C0
use strict;
use warnings;
use DBI;
sub agg_verification
{
my ($list,$tblname,$values);
my $res1=undef;
my $res2=undef;
#First DB Connection******
my $connstr = "ENG=xxx;CommLinks=tcpip{port=xxx};UID=xxx;PWD=xxx";
my $dbh = DBI->connect( "DBI:SQLAnywhere:$connstr", '', '', {AutoCommit => 0} ) or warn $DBI::errstr;
my $stmt="select col1||':'||col3 as a, col2||'('||col3||')' as b from table where col1 like '%xxxx:((15))%'"; #making 'a' as primary key for fetchall_hashref() later.
my $sth=$dbh->prepare($stmt) or warn $DBI::errstr;
$sth->execute() or warn $DBI::errstr;
#Second DB Connection******
my $connstr1 = "ENG=xxx;CommLinks=tcpip{port=xxx};UID=xxx;PWD=xxx";
my $dbh1 = DBI->connect( "DBI:SQLAnywhere:$connstr1", '', '', {AutoCommit => 0} ) or warn $DBI::errstr;
my ($sth1,$stmt1,$stmt2);
#creating, opening and writing the result in a file
open my $fh, '+>>', "/eniq/home/dcuser/output.txt" or warn "Cannot open output.txt: $!";
my $res = $sth->fetchall_hashref('a');
foreach my $key(sort keys %$res) {
my @col1 = $res->{$key}->{'a'};
$list=\@col1;
foreach my $item(@$list)
{
my @values=${$res}{$item}->{'b'};
$values=\@values;
my @tblname=$item=~ m/[(\w*)(\:)](DC\w*)/; #trimming table name
$tblname =\@tblname;
#print $fh "TABLENAME :@$tblname\n";
foreach my $raw(@$tblname) #Extracting _RAW data*********************
{
$raw.="_RAW";
chomp($raw);
$stmt1 = "select @$values from $raw";
$stmt2 = "";
$sth1=$dbh1->prepare($stmt1) or warn $DBI::errstr;
$sth1->execute() or warn $DBI::errstr;
my $max_rows1 = 5_000;
$res1 = $sth1->fetchall_arrayref(undef,$max_rows1);
}
foreach my $day(@$tblname) #Extracting _DAY DATA********************
{
$day =~ s/(\_RAW)//;
chomp($day);
$day .="_DAY";
$stmt2 = "select @$values from $day";
$sth1=$dbh1->prepare($stmt2) or warn $DBI::errstr;
$sth1->execute() or warn $DBI::errstr;
my $max_rows = 5_000;
$res2 = $sth1->fetchall_arrayref(undef,$max_rows);
}
if(@$res1 == @$res2)
{
print $fh "PASS at @$values\n";
}
else
{
print $fh "FAIL at @$values\n";
}
}
}
close $fh or warn "Couldn't close file properly";
$sth1->finish();
$sth->finish();
$dbh1->disconnect;
$dbh->disconnect;
}
agg_verification();
Guys, i want to compare the numerical values coming in $res1 and $res2 but i am not getting the result on which it is passing or failing. My output is all "pass" irrespective of the changes. Please suggest how can i compare values from array references in my code above without using external CPAN library as i don't have permissions to update or add libraries.
My output is all "pass" irrespective of the changes
That is because of the below condition, which is just comparing the number of elements in both de-referenced arrays.
if(@$res1 == @$res2)
If you want to compare the content of both array references (which are numerical as per your question) then you can do
#!/usr/bin/perl
use strict;
use warnings;
use Test::More 'no_plan';
my $res1 = [5,8,10,12];
my $res2 = [5,8,10,12];
is_deeply( $res1, $res2, 'Compare arrayref' );
Output for above case:
chankey@pathak:~/Desktop$ perl test.pl
ok 1 - Compare arrayref
1..1
If they are unequal like:
my $res1 = [5,8,10,12];
my $res2 = [3,8,10,12];
Then you will get below detailed output where you can easily check which value was unequal
chankey@pathak:~/Desktop$ perl test.pl
not ok 1 - Compare arrayref
# Failed test 'Compare arrayref'
# at test.pl line 7.
# Structures begin differing at:
# $got->[0] = '5'
# $expected->[0] = '3'
1..1
# Looks like you failed 1 test of 1.
I showed you one method on how to compare 2 arrayrefs, you might want to see other methods answered in below questions (just make sure you de-reference your arrays first then do the comparison):
Solution without using any module
#!/usr/bin/perl
use strict;
use warnings;
my $res1 = [5,8,10,12];
my $res2 = [3,8,10,12];
foreach my $index (0..$#{$res1}){
if ($res1->[$index] == $res2->[$index]){
print "Index: $index, Equal: YES";
}
else{
print "Index: $index, Equal: NO";
print " [Expected: $res1->[$index], GOT: $res2->[$index]]";
}
print "\n";
}
Output:
chankey@pathak:~/Desktop$ perl test.pl
Index: 0, Equal: NO [Expected: 5, GOT: 3]
Index: 1, Equal: YES
Index: 2, Equal: YES
Index: 3, Equal: YES
As per the discussion on chat:
Since there are arrayrefs at each index of $res1 and $res2. Therefore you should use something like below:
#!/usr/bin/perl
use strict;
use warnings;
my $res1 = [[5],[4],[3],[2]];
my $res2 = [[5],[4],[3],[1]];
foreach my $index (0..$#{$res1}){
foreach my $inner_index (0..$#{$res1->[$index]}){
if ($res1->[$index]->[$inner_index] == $res2->[$index]->[$inner_index]){
print "Equal!! expected: $res1->[$index]->[$inner_index] got: $res2->[$index]->[$inner_index]\n" ;
}
else{
print "Not Equal!! expected: $res1->[$index]->[$inner_index] got: $res2->[$index]->[$inner_index]\n"
}
}
}