putting data from file to mysql...

[Megi]

Helo,

I found at forum somebodys post:
I'm trying to parse an input file and then take data from that file and enter it into a MySQL database. I'm separating the fields with tabs '\t' and ending the records with a newline '\n'. I'm using the LOAD DATA function (see the code snippet below).

# Load the data into the history table
my $sql_statmnt2 = "LOAD DATA LOCAL INFILE \'$history_file\'
INTO TABLE History";
print "$sql_statmnt2 \n";
$sth2 = $dbh->prepare($sql_statmnt2);
$sth2->execute();
$sth2->finish();

However, I only get the first record not all of them. I've looked at the file with an editor that shows invisible characters and the tabs and newlines show up.

Thanks!

I have the same problem...

my file is temp1.txt , doses it mean I put it in a script as a variable?just like above? does any one know how to solve it?
when I try this script it doesn't work at all
what am I doing wrong please help!I would be grateful very very much...

my script is...(basing on the above):

#!/usr/bin/perl -w
 
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use DBI;
 
# BD connection----------------------------------------
my $dbh = DBI->connect('dbi:mysql:monitoring','root','passwd')
or die "Connection Error: $DBI::errstr\n";
 
my $sql_statmnt2 = "LOAD DATA LOCAL INFILE \'$temp1.txt\'
INTO TABLE room1_temperature";
print "$sql_statmnt2 \n";
$sth2 = $dbh->prepare($sql_statmnt2);
$sth2->execute();
$sth2->finish();

----------------------------------------------

\'$temp1.txt\' or \'$temp1\' without a file type?

[Megi]

aaaaaaaa what more:

I put the script in /usr/lib/cgi-bin/ as all my perl and cgi scripts...so where to put a file temp1.txt ???I have put it in the same directory as script...

my errors form command line:

madzia@madzia-laptop:/usr/lib/cgi-bin$ perl loader.pl
[Tue Dec  4 14:51:15 2007] loader.pl: Name "main::temp1" used only once: possible typo at loader.pl line 13.
[Tue Dec  4 14:51:15 2007] loader.pl: Use of uninitialized value in concatenation (.) or string at loader.pl line 13.
LOAD DATA LOCAL INFILE '.txt'
INTO TABLE room1_temperature 
[Tue Dec  4 14:51:15 2007] loader.pl: DBD::mysql::st execute failed: File '.txt' not found (Errcode: 2) at loader.pl line 17.
 

would be grateful for help, thanks!!!!

[numberwhun]

aaaaaaaa what more:

I put the script in /usr/lib/cgi-bin/ as all my perl and cgi scripts...so where to put a file temp1.txt ???I have put it in the same directory as script...

my errors form command line:

madzia@madzia-laptop:/usr/lib/cgi-bin$ perl loader.pl
[Tue Dec  4 14:51:15 2007] loader.pl: Name "main::temp1" used only once: possible typo at loader.pl line 13.
[Tue Dec  4 14:51:15 2007] loader.pl: Use of uninitialized value in concatenation (.) or string at loader.pl line 13.
LOAD DATA LOCAL INFILE '.txt'
INTO TABLE room1_temperature 
[Tue Dec  4 14:51:15 2007] loader.pl: DBD::mysql::st execute failed: File '.txt' not found (Errcode: 2) at loader.pl line 17.
 

would be grateful for help, thanks!!!!

Ok, because you are using the -w switch (which is essentially the same as saying "use warnings;", Perl becomes rather picky with regards to syntax and other check points. You will need to correct these issues before your script will move on.

For instance, on line 11, you define $temp1.txt, yet nowhere before this point is $temp1 defined anywhere. This is more than likely causing line 13 to error out since it couldn't completely set the variable from line 11 due to that variable not existing.

You will need to correct these small error(s) before proceeding.

Regards,

Jeff

[Megi]

Helo Jeff,

You are completly right! I have corrected the errors and it works like almost like I wanted to..

here is the script:

#!/usr/bin/perl -w
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use DBI;
$foo = new CGI;
 
my @row;
print $foo->header;
$temp1 = 'temp1.txt';
 
# BD connection----------------------------------------
my $dbh = DBI->connect('dbi:mysql:monitoring','root','passwd')
or die "Connection Error: $DBI::errstr\n";
my $sql = "select * from room1_temperature";
my $sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
 
 $sql_statmnt2 = "LOAD DATA LOCAL INFILE \'$temp1\'
INTO TABLE room1_temperature (temperature)";
print "$sql_statmnt2 \n";
$sth2 = $dbh->prepare($sql_statmnt2);
$sth2->execute();
$sth2->finish();
 

it works great...the only defect is that it rewrites records when I reload the script..and I need to have it refresh all the time becouse it should work like a real time...
How to avoid rewriting the same data to mysql? Is any command that will do it?
Maybe have any idea?????
Thanks a lot...

[numberwhun]

Helo Jeff,

You are completly right! I have corrected the errors and it works like almost like I wanted to..

here is the script:

#!/usr/bin/perl -w
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use DBI;
$foo = new CGI;
 
my @row;
print $foo->header;
$temp1 = 'temp1.txt';
 
# BD connection----------------------------------------
my $dbh = DBI->connect('dbi:mysql:monitoring','root','passwd')
or die "Connection Error: $DBI::errstr\n";
my $sql = "select * from room1_temperature";
my $sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";
 
 $sql_statmnt2 = "LOAD DATA LOCAL INFILE \'$temp1\'
INTO TABLE room1_temperature (temperature)";
print "$sql_statmnt2 \n";
$sth2 = $dbh->prepare($sql_statmnt2);
$sth2->execute();
$sth2->finish();
 

it works great...the only defect is that it rewrites records when I reload the script..and I need to have it refresh all the time becouse it should work like a real time...
How to avoid rewriting the same data to mysql? Is any command that will do it?
Maybe have any idea?????
Thanks a lot...

Unfortunately, that I have no idea about and am hoping one of the experts can assist you with that.

What I would like to say though, is to please be sure and use the proper code tags around your code, that way the Moderators, like myself, do not have to clean up your threads.

Regards,

Jeff

[Megi]

Unfortunately, that I have no idea about and am hoping one of the experts can assist you with that.

What I would like to say though, is to please be sure and use the proper code tags around your code, that way the Moderators, like myself, do not have to clean up your threads.

Regards,

Jeff

Thanks anyway, I will follow Your tips about the code tags.
By the way how can I contact with the experts:)

[KevinADC]

If I knew the answer I would help but I don't. Perl is only the interface to the database, the problem is with your sql statements, not perl. I suggest you ask on the MySQL database forum why the data is getting over-written.

[numberwhun]

Thanks anyway, I will follow Your tips about the code tags.
By the way how can I contact with the experts:)

Just by posting a thread as you have done. As you see, KevinADC has replied.

Regards,

Jeff

[Megi]

If I knew the answer I would help but I don't. Perl is only the interface to the database, the problem is with your sql statements, not perl. I suggest you ask on the MySQL database forum why the data is getting over-written.

I did as You told me,

so I have a different question, I hope You could help me out with that...the same problem but different way...how about using perl ..
1. open file read the data
2. insert it into mysql table
3. after inserting data into mysql table delete data form file which are sended
than new data ocurrs in the file and I would avoid inserting the same, but how know which data has been inserted?

could You help me with the script if it would work at all the way I think....
i am waiting for answer.Thanks a lot.

[numberwhun]

I did as You told me,

so I have a different question, I hope You could help me out with that...the same problem but different way...how about using perl ..
1. open file read the data
2. insert it into mysql table
3. after inserting data into mysql table delete data form file which are sended
than new data ocurrs in the file and I would avoid inserting the same, but how know which data has been inserted?

could You help me with the script if it would work at all the way I think....
i am waiting for answer.Thanks a lot.

Well, imho, if you have data that is coming in, and the same script is reading it to add it to the database, then you would have to have some routine in there to check each line of data against the database to see if the data is identical or not.

Sounds like a new subroutine is in order to me.

Regards,

Jeff