I have doing some modification on my previous script.
I used foreach loop to read the content of directory. While each file are read, then i
used foreach loop to open the file and imported it into mysql. The script are running
successfull, but in mysql database, it's contain only the range of data, and not the
value..I really2 hit the wall right now and i need someone help..
Here is my code
#!/usr/bin/perl
use DBI;
my $dir="/flow/";
my @files;
my $file;
my $record;
my @log;
my $packet;
my $counter = 0;
my $file_contents;
my @file_array;
my $dbh = DBI->connect('dbi:mysql:ayam','root','roime');
opendir(FILE, $dir) || die("Cannot open directory");
@files= readdir(FILE);
$file_contents = <FILE>;
closedir(FILE);
foreach $file(@files)
{
unless ( ($file eq ".flow") || ($file eq "..") )
{
print "$file\n";
#-------------------------------------------------------
#------------------Read Data File-----------------------
#-------------------------------------------------------
my $full_path = $dir.$file;
open(FILE, "$full_path")||die("Could not read file !");
@file_array = <FILE>;
close(FILE);
my $counter = 0;
@file_array = split(/ /, $file_contents);
foreach $counter(@file_array)
{
{
my $value1 = @file_array[$counter];
$counter = $counter + 1;
my $value2 = @file_array[$counter];
$counter = $counter + 1;
my $value3 = @file_array[$counter];
$counter = $counter + 1;
my $value4 = @file_array[$counter];
$counter = $counter + 1;
my $value6 = @file_array[$counter];
$counter = $counter + 1;
my $value7 = @file_array[$counter];
$counter = $counter + 1;
my $value8 = @file_array[$counter];
$counter = $counter + 1;
my $value9 = @file_array[$counter];
$counter = $counter + 1;
my $value10 = @file_array[$counter];
$counter = $counter + 1;
my $value11 = @file_array[$counter];
$counter = $counter + 1;
}
}
#Prepare the insert SQL
my $rec = $dbh->prepare
("INSERT INTO t_flows
(ipSrc,
ipDst,
pktSent,
bytesSent,
startTime,
endTime,
srcPort,
dstPort,
tcpFlags,
proto,
tos)
VALUES ('$value1',
'$value2',
'$value3',
'$value4',
'$value5',
'$value6',
'$value7',
'$value8',
'$value9',
'$value10',
'$value11')");
$rec->execute;
}
}
And here is my picture of MySQL
+-------+-------+---------+-----------+-----------+---------+---------+---------+----------+-------+-----+
| ipSrc | ipDst | pktSent | bytesSent | startTime | endTime | srcPort | dstPort |
tcpFlags | proto | tos |
+-------+-------+---------+-----------+-----------+---------+---------+---------+----------+-------+-----+
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
| | | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 |
Regards
Roy
-----Original Message-----
From: Charles K. Clarkson [mailto:[EMAIL PROTECTED]
Sent: Tue 10/26/2004 3:31 PM
To: Roime bin Puniran; [EMAIL PROTECTED]
Cc:
Subject: RE: MySQL+foreach loop.........roy
Roime bin Puniran <[EMAIL PROTECTED]> wrote:
: I have a script that read some text file in the directory. All
: the data inside the text file then would be extracted into my
: sql, and i used foreach loop to read the text file, then doing
: some loop while data are sorted. Here is my code.
Your description indicates this order of actions:
1. Retrieve files in directory,
2. Open data file,
3. Retrieve data,
4. Sort data,
5. Insert information into DB,
6. Repeat for each file in directory.
Had your code worked, this is the order your code is written
in:
1. Insert information into DB,
2. Retrieve file names,
2. Open data file,
3. Set values for insert.
As written here's your code:
1. Insert no information into DB,
2. Do not retrieve files,
3. Open no data file (see step 2),
4. Set and discard values for insert.
Perl doesn't "know" that you wrote the code out of order. You
have to write it as you want it to be performed. First write the
code which retrieves the file names in the directory.
Test that code and debug it. Once that is working write code
for the next step, test it, and debug. Then move on to the next
step.
: ================================
: #!/usr/bin/perl
You skipped these modules. Start every script with them. Write
part of the script and test it. Then debug and write some more
code. Debug it and write some more code. Do not write the whole
thing and then debug it.
use strict;
use warnings;
: use DBI;
: use IO::Socket;
This module is not used in this script.
: my $path = "/home/roime/flow/";
: my @folder;
Declare this when you define it. It will help you avoid
using it before defining it as you have done here.
: my $file = ".flow";
This variable is not used in this script.
: my $dbh = DBI->connect('dbi:mysql:ayam','root','');
:
: #Prepare the insert SQL
: my $rec = $dbh->prepare("INSERT INTO t_flows(ipSrc,
: ipDst, pktSent, bytesSent, startTime, endTime, srcPort,
: dstPort, tcpFlags, proto, tos) VALUES ('$value1',
: '$value2', '$value3', '$value4', '$value5', '$value6',
: '$value7', '$value8', '$value9', '$value10', '$value11')");
: $rec->execute;
$value1 through $value11 have not been set to anything yet.
You can't insert their values until *after* you set their values.
When you find yourself numbering variables consecutively, think
"array".
Use white space when writing SQL. This is easier to read and
change.
my $dbh = DBI->connect(
'dbi:mysql:ayam',
'root',
''
);
my $record = $dbh->prepare( qq~
INSERT
INTO
t_flows(
ipSrc,
ipDst,
pktSent,
bytesSent,
startTime,
endTime,
srcPort,
dstPort,
tcpFlags,
proto,
tos
)
VALUES (
'$value1',
'$value2',
'$value3',
'$value4',
'$value5',
'$value6',
'$value7',
'$value8',
'$value9',
'$value10',
'$value11'
)~
);
Or, more likely:
my $dbh = DBI->connect(
'dbi:mysql:ayam',
'root',
'',
{
PrintError => 0,
RaiseError => 1,
AutoCommit => 1,
},
);
my $sth = $dbh->prepare( q~
INSERT
INTO
t_flows(
ipSrc,
ipDst,
pktSent,
bytesSent,
startTime,
endTime,
srcPort,
dstPort,
tcpFlags,
proto,
tos
)
VALUES ( ?, ?, ?,
?, ?, ?,
?, ?, ?,
?, ?
)~
);
You need to read the documentation for DBI.pm. There are
plenty of examples and pointers to more resources.
: foreach my $file (@folder)
: {
@folder has nothing in it. This loop runs 0 times. What do
you want @folder to hold? You need to write the code to put
something in there.
: my $full_path = $path.$file;
: open(FILE, $full_path)||die("Could not read file !");
Use the built-in error variable ($!) in error messages. I
have an editor macro for file openings. This is one of the most
common idioms I use.
my $file = "$path$file";
open FH, $file or die qq(Cannot open "$file": $!);
#...
close FH;
: my $file_contents = <FILE>;
: close(FILE);
$file_contents is local to this loop. If the loop did run,
$file_contents would be undefined after the loop ends.
: }
:
:
:
: my $counter = 0;
: @file_array = split(/ /, file_contents);
$file_contents and @file_array are out of scope here. They
must be declared in this scope to avoid errors. @file_array is
obviously an array. Why not call it @files?
:
: foreach (@file_array)
: {
: my $value1 = @file_array[$counter];
: $counter = $counter + 1;
: my $value2 = @file_array[$counter];
: $counter = $counter + 1;
: my $value3 = @file_array[$counter];
: $counter = $counter + 1;
: my $value4 = @file_array[$counter];
: $counter = $counter + 1;
: my $value6 = @file_array[$counter];
: $counter = $counter + 1;
: my $value7 = @file_array[$counter];
: $counter = $counter + 1;
: my $value8 = @file_array[$counter];
: $counter = $counter + 1;
: my $value9 = @file_array[$counter];
: $counter = $counter + 1;
: my $value10 = @file_array[$counter];
: $counter = $counter + 1;
: my $value11 = @file_array[$counter];
: $counter = $counter + 1;
:
: }
This loop doesn't do anything. All the variables are scoped to
this code block and will not be defined outside the foreach loop.
Even if they had been defined, the program ends here and their
values are useless. @file_array[$counter] is better written as
$file_array[$counter].
This loop could be written as:
{
my( $value1, $value2, $value3, $value4,
$value5, $value6, $value7, $value8,
$value9, $value10, $value11, ) = @file_array;
}
my $counter = 11;
HTH,
Charles K. Clarkson
--
Mobile Homes Specialist
254 968-8328
This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended recipient,
please notify the sender immediately by return e-mail, delete this
e-mail and destroy any copies. Any dissemination or use of this
information by a person other than the intended recipient is
unauthorized and may be illegal.
