Perl - don't send double sql request

Question

i have a perl script and i don't want to send a double request :

the request is '2018-03-15 12:30:00', 'Metric A', 62 and i want send only one time and not more :

in my mariadb bdd i have double line :

 SELECT time, measurement, valueOne FROM `metric_values`;

results :

+---------------------+-----------------+----------+ 
| time                | measurement     | valueOne |
+---------------------+-----------------+----------+ 
| 2018-03-15 12:30:00 | Metric A        |       62 | 
| 2018-03-15 12:30:00 | Metric A        |       62 |

my perl scipt :

use DBI;
open (FILE, 'logfile');
while (<FILE>) {

($word1, $word2, $word3, $word4, $word5, $word6, $word7, $word8, $word9, $word10, $word11, $word12, $word13, $word14) = split(" ");

$word13 =~ s/[^\d.]//g;


    if ($word13 > 5) {

        if ($word2 eq "Jan") {

        $word2 = "01"
        }

        if ($word2 eq "Feb") {

        $word2 = "02"
        }

        if ($word2 eq "Mar") {

        $word2 = "03"
        }

        if ($word2 eq "Apr") {

        $word2 = "04"
        }

        if ($word2 eq "May") {

        $word2 = "05"
        }

        if ($word2 eq "Jun") {

        $word2 = "06"
        }

        if ($word2 eq "Jul") {

        $word2 = "07"
        }

        if ($word2 eq "Aug") {

        $word2 = "08"
        }

        if ($word2 eq "Sep") {

        $word2 = "09"
        }

        if ($word2 eq "Oct") {

        $word2 = "10"
        }

        if ($word2 eq "Nov") {

        $word2 = "11"
        }

        if ($word2 eq "Dec") {

        $word2 = "12"
        }

        print "'$word5-$word2-$word3 $word4', $word11, $word13 \n";

              }
    # Connect to the database.
    my $dbh = DBI->connect("DBI:mysql:database=db;host=ip",
                           "titi", 'mp!',
                           {'RaiseError' => 1}) ;


    my $sth = $dbh->prepare(

        "INSERT `metric_values` (time, measurement, valueOne) VALUES('$word5-$word2-$word3 $word4', $word11, $word13);")#result is ('2018-03-15 12:30:00', 'Metric A', 62)

        or die "prepare statement failed: $dbh->errstr()";
    $sth->execute() or die "execution failed: $dbh->errstr()";
    print $sth->rows . " rows found.\n";

    $sth->finish;

my log file:

Wed Oct 17 04:57:08 2018 : Resource = 'toto' cstep= 'titi' time =23.634s 
Wed Oct 17 04:57:50 2018 : Resource = 'toto' cstep= 'titi' time =22.355s 

thanks for your response

Answer 1

In a comment, you say this:

i execute this script every 5 minute and that create many same line in the table, i don't want same line in my table

I think this is what is happening.

Every five minutes you run your program. Each time you run the program you use exactly the same log file as input. So the same records get processed every time and new copies of the data are inserted on each run.

There's nothing wrong with your existing code. It's doing exactly what you've asked it to do. It's just not clever enough. You need to make it cleverer. You have a few options.

1. Remove from the log file the records that have been processed. That way you only insert each record once.
2. Add a flag to each record in your log file which indicates that it has been added to the database. You can then check that flag when processing the file and only insert records that don't have the flag.
3. Add an index to your table to ensure that it can only contain one copy of each record. You'll then need to change your code so it ignores any duplicate data errors that you get back from the database.
4. Use REPLACE instead of INSERT and ensure you have the correct primary key on your table to ensure that duplicate records aren't inserted.

Without knowing a log more about your particular application, it's hard to know which of these options is the best approach for you. I suspect you'll find the REPLACE option the easiest to implement.

Update: I hope you'll find some general comments on your code to be useful.

Your code to open the file works, of course, but it is some distance from current best practice. I recommend a) using a lexical filehandle, b) using the three-arg version of open() and c) checking the return value from the call.

open my $fh, '<', 'logfile'
  or die "Could not open 'logfile': $!\n";

Using variables called $word1, $word2, etc is a terrible idea. A better idea would be to use an array:

my @words = split ' ',

If you really want individual variables, then please give them better names:

my ($day, $mon, $date, $time, $year, ... ) = split(' ');

Personally, I'd turn each record into a hash.

my @cols = qw[day mon date time year ... ];

# and then, in your loop
my %record;
@record{@cols} = split ' ';

Converting the month to a number the way you do it is clunky. Consider setting up a conversion hash.

my %months = (
  Jan => 1,
  Feb => 2,
  ...
);

Then your code becomes (assuming $mon instead of $word2):

$mon = sprintf '%02d', $months{$mon}
  or die "$mon is not a valid month\n";

But, actually, you should use something like Time::Piece to deal with dates and times.

my $timestamp = "$day $mon $date $time $year";
my $tp = Time::Piece->strptime($timestamp, '%a %b %d %H:%M:%S $Y');
say $tp->ymd, ' ', $tp->hms;