I want to insert data from a fifo pipe into a mysql table
my script as follows:
#!/usr/bin/perl
#Script to read data out of a named pipe and write to MySQL database.
$| = 1;
use strict;
use DBI();
my $filename;
my $inputline;
my $linenumber;
my @arr;
$filename = "./SEC_fifo";
open(FIFO, "+< $filename") or die "FIFO error on $filename $!";
my $dbh = DBI->connect("DBI:mysql:database=ecdb;host=localhost",
"user", "[pwd]",
{'RaiseError' => 1});
while (<FIFO>)
{
$inputline = $_;
@arr = split(/,/,$inputline);
# Quit read loop when requested.
last if($inputline =~ /quit/i);
chop $inputline;
$linenumber++;
print "Got: [$inputline], ";
my $sql="";
my $sth="";
my @row;
print
"output.\n".$arr[0],$arr[1],$arr[2],$arr[3],$arr[4],$arr[5],$arr[6],$arr[7],
$arr[8],$arr[9],$arr[10],$arr[11],$arr[12],$arr[13]."\n";
# perl trim function - remove leading and trailing whitespace
my $str = $arr[6] ;
$str =~ s/^\s+//;
$str =~ s/\s+$//;
if($str ne 'Normal')
{
print "arr[6]=".$arr[6]."\n";
$sql = "select Hid from Devices where hostname = '$arr[2]'";
print $sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql
\n";
@row=$sth->fetchrow_array;
my $hid1=@row[0];
$sql = "select Hid from Devices where hostname = '$arr[3]'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
my $hid2=@row[0];
$sql = "select Eid from Event where Eventname = '$arr[8]' and severity =
'$arr[9]' and Trapoid='$arr[10]'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
my $eid1=@row[0];
$sql = "select Eid from Event where Eventname = '$arr[11]' and severity =
'$arr[12]' and Trapoid='$arr[13]'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
my $eid2=@row[0];
$sql = "insert into secresult
(Hid_1,Hid_2,interface_1,interface_2,ifindex_1,ifindex_2,Eid_1,Eid_2,start_t
ime_1,start_time_2,effect_range,description) values
($hid1,$hid2,'$arr[4]','$arr[5]',$arr[6],$arr[7],$eid1,$eid2,'$arr[0]','$arr
[1]','$arr[14]','$arr[15]')";
print $sql."\n";
$dbh->do($sql);
print "inserted it.\n";
}
else
{
$sql = "select Hid from Devices where hostname = '$arr[1]'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
my $hid=@row[0];
$sql = "select Eid from Event where trapoid = '$arr[0]' and severity !=
'Normal'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
my $eid=@row[0];
$sql = "select id from (SELECT * FROM secresult WHERE end_time_1 ='' and
Hid_1=$hid and interface_1 ='$arr[4]' and ifindex_1=$arr[5] ORDER BY
start_time_1 DESC LIMIT 1) result where result.eid_1 = $eid";
print "sql=".$sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
my $id=@row[0];
if($id !="")
{
$sql = "update secresult set end_time_1 = '$arr[2]' where id = '$id'";
$dbh->do($sql);
}
else
{
$sql = "select id from (SELECT * FROM secresult WHERE end_time_2 =''
and Hid_1=$hid and interface_2 ='$arr[4]' and ifindex_2=$arr[5] ORDER BY
start_time_2 DESC LIMIT 1) result where result.eid_2 = $eid";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?
\n\n $sql \n";
@row=$sth->fetchrow_array;
$id=@row[0];
$sql = "update secresult set end_time_2 = '$arr[2]' where id = '$id'";
$dbh->do($sql);
}
}
}
my $sth = $dbh->prepare("SELECT * FROM secresult"); $sth->execute(); while
(my $ref = $sth->fetchrow_hashref()) {
$sth->finish();
$dbh->disconnect();
exit;
i can insert into to database by the script,but when i execute the script to
listening , after few minute get data from fifo ,the script will be shown
the error message "DBD::mysql::st execute failed: MySQL server has gone away
at ./Db_code.pl line 66, line 5"
the line 66 is " $sth->execute || die "Could not execute SQL statement ...
maybe invalid? \n\n $sql \n";"
Any help would be much appreciated! Thanks in advance.
Andrew
------------------------------------------------------------------------------
Time is money. Stop wasting it! Get your web API in 5 minutes.
www.restlet.com/download
http://p.sf.net/sfu/restlet
_______________________________________________
Simple-evcorr-users mailing list
Simple-evcorr-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/simple-evcorr-users
