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

[KevinADC]

I think I misunderstood your question. For some reason I read "overwrites" but you said "rewrites". I guess what you are trying to do is avoid duplicate data in the database. The answer might still be an SQL one so wait and see if someone in the MySQL forum has a suggestion.

[Megi]

I think I misunderstood your question. For some reason I read "overwrites" but you said "rewrites". I guess what you are trying to do is avoid duplicate data in the database. The answer might still be an SQL one so wait and see if someone in the MySQL forum has a suggestion.

Yes, I was saying about re-writing.
Nobody answerd on Mysql , so I have to change my way of work...now for perl..

I have a file temp1.txt and I open it and write it's content into temp1b1.txt - it would be my backup. the whole script is relading each 10s becouse file temp1.txt will be getting new values constantly..

now I need to delete or truncate data from file tem1.txt which are written to temp1b.txt , I have a problem with that , I know truncate function needs length of my file but I don't know how to use it and how to do it..

mayby with that You could help me, would be grateful.Thanks!

#!/usr/bin/perl -w
 
use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
 
while('true')
{
    my $infile = 'temp1.txt';
    my $outfile = 'temp1b1.txt';
 
    open IN, "< $infile" or die "Can't open $infile : $!";
    open OUT, ">> $outfile" or die "Can't open $outfile : $!";
    print OUT <IN>;
    close IN;
    close OUT;
    open IN "> $infile"or die"can't open $infile: $!";
    truncate(....?)
    ?????
    sleep 10;
}
 

the former script will do writing to DB, but this one has to make a backup and delete backuped values...

[eWish]

Can you show the schema for your database ( ie: tables, types, index etc....) one(s) table(s) you are having problems with?

--Kevin

[eWish]

This addresses your second inquiry. The concept behind this is simple. Entering the contents of an array (contents of a file) into the db. I was taught that it is more efficient to replace the entries rather than check to see if an entry already exists. So with that in mind here is how it can be done.

This is untested code, but should work. You will have to modify this to your needs to work as you need it to. I don't know what your data looks like so this is just a generic example. Also, this is executed from the browser. Which is easier for me. You will have to modify if you are running from the command line.

#!/usr/bin/perl -T
 
use strict;
use warnings;
 
use CGI;
use CGI::Carp qw/fatalsToBrowser/;
 
my $q = CGI->new;
 
print $q->header; 
print $q->start_html(); 
 
 
my $db_vars = ( 
    dbname   =>  'xxx',
    servername  =>  'xxx',
    username  => 'xxx',
    password  => 'xxx',
    );
 
 
my $data_source = 'DBI:mysql:' . $db_vars{dbname} . ':' . $db_vars{servername};
my $dbh = DBI->connect( $data_source, $db_vars{username}, $db_vars{password}, {PrintError=>1});
 
 
my $data_file = 'path/to/file';
 
 
my $replace = $dbh->prepare('REPLACE INTO table_name(column1) VALUES(?)');
my $check  = $dbh->prepare('SELECT column1 FROM table_name WHERE column1=?');
 
 
open (my $DATAFILE, '<', $file) || die "Can't open $file: $!";
 while (<$DATAFILE>) {
 
  chomp;
   my @contents = split(/\n/);
 
   foreach my $content(@contents) {
 
        if( my ($existing_count) = $dbh->selectrow_array($check, undef, ( $content ))){
                $replace->execute($content);
         } else {
                $replace->execute($content);
         }
  }   
 
 $check->finish();
 $replace->finish(); 
 
 
 }
 
close ($DATAFILE);
 
print 'Done, go get the db and see if it worked.';
 
print $q->end_html();
 
1;

I hope I understood you problem correctly.

--Kevin

[eWish]

This addresses your last question. You can do the following to clear or truncate an existing file.

   open ($CLEAR_FILE, '>', $temp_file_to_clear) || print "Error clearing $temp_file_to_clear: $!";
close ($CLEAR_FILE);

--Kevin