Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Import log flie data - shell script

  1. #11
    Join Date
    Jun 2010
    Location
    Manchester
    Beans
    88
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: Import log flie data - shell script

    hi

    If I can just stay on topic a little while longer. I made this part of the assignment a little more complicated with a normalised database.
    My database is now as : http://acookson.org/wp-content/theme...sms_voting.png

    And I'm trying to insert records into the voting table; making use of a switch statement to dynamically define foreign keys in order to populate the table. My new Perl script is:

    Code:
     #!/usr/bin/perl 
    
      use warnings;
      use strict;
      use Switch;
      use DBI();
    
     # Connect to the database.
     my $dbh = DBI->connect("DBI:mysql:database=sms_voting;host=localhost",
                            "cookie", "**********",
                            {'RaiseError' => 1});
    
        my $sth = $dbh->prepare("INSERT INTO voting (epoch, validity, choice, campaigns_id, candidates_id ) VALUES (?,?,?,?,?)");
    
        open (LOGFILE, '/var/www/voting/votes.txt') or die("Could not open log file.");
    
        my $errors = 0;
        
     foreach my $line (<LOGFILE>) {    
        
            my ($vote, $epoch, $campaign, $validity, $choice, $CONN, $MSISDN, $GUID, $Shortcode) = split(' ', $line);
    
        # parse the field:value entries...
        $campaign = substr $campaign, 8, 11, '';
        $validity = substr $validity, 9, 6, ''; # during
        $choice = substr $choice, 7, 10, ''; # Brown
    
        # case statements to define correct foreign keys...
    
        my $campaign_id = 0;
        my $candidate_id = 0;
    
         switch ($campaign) {
                case ("ssss_uk_01B")     { $campaign_id = 1 } 
                case ("ssss_uk_01C")     { $campaign_id = 2 } 
                case ("ssss_uk_01D")     { $campaign_id = 3 } 
                case ("ssss_uk_01E")     { $campaign_id = 4 } 
                case ("ssss_uk_01F")     { $campaign_id = 5 } 
                case ("ssss_uk_01G")     { $campaign_id = 6 } 
        }
        
        switch ($choice) {
                case ("Brown")         { $candidate_id = 1 } 
                case ("Cameron")     { $candidate_id = 2 } 
                case ("Balls")         { $candidate_id = 3 } 
                case ("Green")         { $candidate_id = 4 } 
                case ("Boring")     { $candidate_id = 5 } 
                case ("Tupele")     { $candidate_id = 6 } 
        }
    
        if ($epoch && $validity && $choice && $campaign_id && $candidate_id ) {
    
              $sth->execute($epoch, $validity, $choice, $campaign_id, $candidate_id);
            # debug
            print "$epoch $validity $choice \n"; # 1161048980 during Green
            next;
        } 
    
        $errors++;
    
     }
    
    close (LOGFILE);
    
    
    # debug
    print qq(errors=$errors\n);
    The script should now insert records into 'voting' table like so:

    Code:
    INSERT INTO voting (epoch, validity, choice, campaigns_id, candidates_id ) VALUES ('1161048980', 'during', 'Brown', 1, 1 )
    It doesn't!

    Code:
    mysql> select * from voting;
    Empty set (0.00 sec)
    confirms this.


    The script has effectively just looped through the votes.txt file without inserting records?

    No errors are reported so it isn't a syntax error. Where has my logic gone amiss?

  2. #12
    Join Date
    Jun 2010
    Location
    Manchester
    Beans
    88
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: Import log flie data - shell script


Page 2 of 2 FirstFirst 12

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •