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.