Perl - Batch file

Question

I have a Perl script which splits a log file and sends a query to a database and I want to execute this Perl script from a bat file in the windows task scheduler. That executes only the first query and I want to execute all of the queries.

My logfile.txt :

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

My Perl script

use DBI; use Sys::Hostname ();

$hostname = Sys::Hostname::hostname();
$hostname_cstep_table = $hostname . '_cstep_table';

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=DB;host=IP", "nameDB", 'psswordDB', {'RaiseError' => 1});

#on peut utiliser insert mais il y aura des doublons et ici on est dans une table unique
my $sth = $dbh->prepare("SHOW TABLES LIKE '$hostname_cstep_table';")
  or die "prepare statement failed: $dbh->errstr()";

$sth->execute() or die "execution failed: $dbh->errstr()";

#affiche 0 ou 1, selon la table qui est trouvé
$row = $sth->rows;

if ($row eq 1) {

    print $hostname_cstep_table . " has created";

}

if ($row eq 0) {
    #on peut utiliser insert mais il y aura des doublons et ici on est dans une table unique avec une clé primaire et un ID incrémenté
    my $sth = $dbh->prepare(" CREATE TABLE `$hostname_cstep_table` (    `ID` TINYINT ( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT ,   `time` datetime NOT NULL,   `cstep` nvarchar(100)NOT NULL,  `time_in_seconde` int NOT NULL,     PRIMARY KEY (`ID`),     UNIQUE KEY (`time`)     ) ENGINE=Aria;")

        or die "prepare statement failed: $dbh->errstr()"; 
    $sth->execute() or die "execution failed: $dbh->errstr()";
    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"
            }

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

            # Connect to the database.
            my $dbh = DBI->connect("DBI:mysql:database=DB;host=IP",             "nameDB", 'passwordDB',
            {'RaiseError' => 1}) ;

            #on peut utiliser insert mais il y aura des doublons et ici on est dans une table unique
            my $sth = $dbh->prepare("REPLACE `$hostname_cstep_table` (time, cstep, time_in_seconde) VALUES('$word5-$word2-$word3 $word4', $word11, $word13);")
                or die "prepare statement failed: $dbh->errstr()";
            $sth->execute() or die "execution failed: $dbh->errstr()";
            print $sth->rows . " rows found.\n";
            $sth->finish;

        }
    }    
}

My batch file

C:\Users\Desktop\perl_32_bits\portableshell.bat 
C:\Users\Desktop\perl_32_bits\test3.pl %*

Thanks for your response.

I think that several lines in your code example have been accidentally joined together with the comments. Can you please [edit your question](https://stackoverflow.com/posts/53317532/edit) to fix the code. — Dave Cross, Nov 15 '18 at 11:24
The simplest way would be to associate perl extension (.pl) with the interpreter... you can then simply run it as `perlscript.pl paramters....` — Gerhard, Nov 15 '18 at 11:26
@jack: You seem to have completely ignored all of the good advice you were given the [last time you showed us this code](https://stackoverflow.com/a/53224475/). — Dave Cross, Nov 15 '18 at 11:30
Possible duplicate of [Perl - don't send double sql request](https://stackoverflow.com/questions/53222621/perl-dont-send-double-sql-request) — Corion, Nov 15 '18 at 11:33
@jack: I've tried to tidy up your code. Please check that it looks correct. Also. **please** be more careful next time. If you're asking a large group of strangers to read and understand your code, then it's only polite to make their job as easy as possible. And ensuring the formatting is correct is an important part of that. — Dave Cross, Nov 15 '18 at 11:42
The first line in batch file must be `CALL C:\Users\Desktop\perl_32_bits\portableshell.bat`. For the reason read answer on [How to call a batch file that is one level up from the current directory?](https://stackoverflow.com/a/24725044/3074564) It explains all methods existing to run or call a batch file from within a batch file. — Mofi, Nov 15 '18 at 12:12
i want use a portable version of perl whish is portableshell.bat — jack, Nov 15 '18 at 13:57
i don't know why the query is send only one time with the windows task sheduler — jack, Nov 15 '18 at 14:52

score 1 · Answer 1 · answered Nov 15 '18 at 15:56

Your question really isn't very clear but I think that (once again) you've confused yourself with the logic in your program. Converted to pseudo-code, your program looks like this:

connect to the database
check for the existence of the table
if the table exists
  say that the table exists
else
  create the table
  open the log file
  for each record in the log file
    parse the record
    insert record into the table
  end for
end if

So let's look at how this works.

The first time you run the program, I assume the table doesn't exist. So the table is created, the log file is processed and a number of records get added to your table.

On every subsequent run of the program, the table already exists. So your program just tells you that the table exists and then exits without processing the log file.

I think you actually want your program to look like this:

connect to the database
check for the existence of the table
if the table exists
  say that the table exists
else
  create the table
end if

open the log file
for each record in the log file
  parse the record
  insert record into the table
end for

I'll leave it as an exercise for you to actually make those changes to your code.

Update: And please consider using some of the good advice you got the last time you showed us this code. We make these suggestions for good reasons :-)

thanks for your response, but when i don't use the task scheduler, only my batch file that works but when i run the task sheduler with my batch file that creat only the table and not other querry — jack, Nov 15 '18 at 16:01

Perl - Batch file

1 Answers1