#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Question Compare TWO Select statement Result Set


    Hello There,

    Code:
    use strict;
    use DBI;
    
    
    my $DBFirst = <>;
    my $DBSecond = <>;
    my $DBTable = <>;
    my $CompareColumn = <>;
    
    if (!$DBFirst || !$DBSecond || !$DBTable || ! $CompareColumn) {
       print "Usage (you need all these columns):\n\n";
       print "-f first database name (i.e., eq)\n";
       print "-s second database naem (i.e., eq_lucin)\n";
       print "-t Table you are comparing (i.e., npc_types)\n";
       print "-c Column you are comparing (i.e., id)\n\n";
       print "Also, don't forget to edit this file to include your database server's ip, username and password\n\n";
       exit;
    }
    
    my $DBHost = "localhost";
    my $DBUser = "root";
    my $DBPassword = "";
    
    my $Temp = "Select $CompareColumn from $DBTable";
    
    
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root') || die "Could not connect to database: $DBI::errstr";
    #dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1});
    my $sth = $dbh->prepare($Temp);
    $sth->execute();
    
    while (my $ref = $sth->fetchrow_hashref()) {
       my $EQFirst{$ref->{"$CompareColumn"}} = 1;
       my $counter++;
    }
    $sth->finish();
    $dbh->disconnect();
    
    print "First Database:  $counter total records\n";
    
    $dbh = DBI->connect('DBI:mysql:classicmodels', 'root') || die "Could not connect to database: $DBI::errstr";
    $sth = $dbh->prepare($Temp);
    $sth->execute();
     
    while ($ref = $sth->fetchrow_hashref()) {
       my $EQSecond{$ref->{$CompareColumn}} = 1; 
       my $counter1++; 
    } 
    $sth->finish();
    $dbh->disconnect(); 
    
    print "Second Database:  $counter1 total records\n"; 
    
    foreach $tmp (sort keys %EQSecond) {
       if (!$EQFirst{$tmp}) {
          print "$DBSecond row not found in $DBFirst:  $tmp\n";
          $counter2++;
       }
    }
    
    print "$counter2 total items not in the $DBFirst database\n";
    Iím trying to automate the ETL process were in the above code , taking two select statement results and the compare the each row results with primary key common between them.
    But I could not be able to figure out what Iím doing wrong here Somebody could help me to fix this ?
    Exception list for your Reference

    syntax error at ./Compare.pl line 33, near "$EQFirst{"
    syntax error at ./Compare.pl line 33, near "}}"
    syntax error at ./Compare.pl line 35, near "}"
    Global symbol "$ref" requires explicit package name at ./Compare.pl line 45.
    syntax error at ./Compare.pl line 46, near "$EQSecond{"
    Global symbol "$ref" requires explicit package name at ./Compare.pl line 46.
    syntax error at ./Compare.pl line 46, near "}}"
    syntax error at ./Compare.pl line 48, near "}"
    Global symbol "$tmp" requires explicit package name at ./Compare.pl line 54.
    Global symbol "%EQSecond" requires explicit package name at ./Compare.pl line 54.
    Global symbol "%EQFirst" requires explicit package name at ./Compare.pl line 55.
    Global symbol "$tmp" requires explicit package name at ./Compare.pl line 55.
    Global symbol "$tmp" requires explicit package name at ./Compare.pl line 56.
    Global symbol "$counter2" requires explicit package name at ./Compare.pl line 57.
    Global symbol "$counter2" requires explicit package name at ./Compare.pl line 61.
    Execution of ./Compare.pl aborted due to compilation errors.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,947
    Rep Power
    1225
    Take it one step at a time. Start with fixing the first error then run the script again and troubleshoot/fix the next error it produces.

    Often a single coding error can generate multiple error messages.

    syntax error at ./Compare.pl line 33, near "$EQFirst{"
    To fix that error, you need to declare the hash prior to the loop. You will also want to declare $counter outside of the loop.
    Code:
    my (%EQFirst, $counter);
    while (my $ref = $sth->fetchrow_hashref) {
       $EQFirst{ $ref->{$CompareColumn} } = 1;
       $counter++;
    }
    That will fix the first 3 syntax error messages. Try fixing the next error(s) on you own and post back when you get stuck.

IMN logo majestic logo threadwatch logo seochat tools logo