MySQL+foreach loop.........roy

Hi all..
=20
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, the doing some loop while data are =
sorted..Here is my code.

 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
#!/usr/bin/perl=20


use DBI;
use IO::Socket;

my $path =3D "/home/roime/flow/";
my @folder;
my $file =3D ".flow";

my $dbh =3D DBI->connect('dbi:mysql:ayam','root','');

#Prepare the insert SQL
my $rec =3D $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;


foreach my $file (@folder)
{
my $full_path =3D $path.$file;
open(FILE, $full_path)||die("Could not read file !");
my $file_contents =3D <FILE>;
close(FILE);=09
=09
}



my $counter =3D 0;
@file_array =3D split(/ /, file_contents);

foreach (@file_array)
{=09
my $value1 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value2 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value3 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value4 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value6 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value7 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value8 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value9 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value10 =3D @file_array[$counter];
$counter =3D $counter + 1;
my $value11 =3D @file_array[$counter];=20
$counter =3D $counter + 1;
=09
}=09
 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D

My problem is, the data from text file seems are sorted half, but it's =
display nothing....I mean the range of the data are there, but the data =
actually disappeer....

 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=


Below are the view of my mysql database
+--------+--------+---------+-----------+-----------+---------+---------+=
---------+----------+-------+-----+
| 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 |

 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D

Anybody have any ide about it?....That 's my code have a problem?...
Thanks a lot

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.

Roime bin Puniran <roime@rndtm.net.my> 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

On Tue, 26 Oct 2004, Murphy,  Ged (Bolton) wrote:

> Going back a few years here, but does an SQL statement not have to end
> in a semi-colon as above?

I think it depends on your SQL interpreter.

The MySQL command line interface `mysql` expect semi-colons, as does the
`psql` tool for PostgreSQL. I seem to remember that Oracle's `sqlplus`
did as well, but I'm not sure about other database engines.

As for DBI though, for single-statement queries, it doesn't matter, does
it? My impression was that DBI scripts worked both with or without the
semi-colon, but maybe this depends on the DBD being used. In any case,
I've written DBD::mysql code that worked fine without it...


--
Chris Devers

Roime bin Puniran, wrote:

>#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');");
-----------------------------------------------------------^


Going back a few years here, but does an SQL statement not have to end in a
semi-colon as above?



 ****************************************
********************************
The information contained in this message or any of its
attachments is confidential and is intended for the exclusive
use of the addressee. The information may also be legally
privileged. The views expressed may not be company policy,
but the personal views of the originator. If you are not the
addressee, any disclosure, reproduction, distribution or other
dissemination or use of this communication is strictly prohibited.
If you have received this message in error, please contact
postmaster@exideuk.co.uk
<mailto:postmaster@exideuk.co.uk> and then delete this message.

Exide Technologies is an industrial and transportation battery
producer and recycler with operations in 89 countries.
Further information can be found at www.exide.com

Chris Devers wrote:
> On Tue, 26 Oct 2004, Murphy,  Ged (Bolton) wrote:
> 
>
> I think it depends on your SQL interpreter.
>
> The MySQL command line interface `mysql` expect semi-colons, as does
> the `psql` tool for PostgreSQL. I seem to remember that Oracle's
> `sqlplus` did as well, but I'm not sure about other database engines.

Not technically true, at least for psql and SQL*Plus. For instance, in psql
you can run a query without using a semicolon:

select * from foo
\g

In SQL*Plus, you can do the same as:

select * from foo
/

Using a semicolon in these cases is just a shortcut that means "my statement
is finished; go ahead an execute it". It is not part of the statement itself
and is not sent to the back end, AFAIK.

On Tue, 26 Oct 2004, Bob Showalter wrote:

> Chris Devers wrote: 
>
> Not technically true, at least for psql and SQL*Plus. For instance, in psq
l
> you can run a query without using a semicolon:
>
>    select * from foo
>    \g

MySQL supports this as well:

mysql> show tables
-> \g
Empty set (0.00 sec)

Etc.

I didn't mean that the end-token was literally a part of the statement,
but it is the conventional way to end a statement.

I'm not nearly as familiar with psql & sqlplus as I am with mysql, so
I'm happy to be corrected there.

In any case, the more immediate question is how the database driver for
MySQL expects things in Perl/DBI scripts. And for that, as near as I can
tell, it doesn't really matter if the semicolons are present as long as
statements are being issued one at a time.



--
Chris Devers