Re: MySQL DBI Problem.
One way to handle this is to validate what you just inserted into the database. Once you are sure of the success of your insert then delete it from the pop server. I don't know how light weight you trying to keep your program. This would certainly add some overhead not only to your database server but also your pop server and network. Or you could store it locally as a backup in case your insert fails. Then put something in place to do a periodic clean up of your files. One thing is certain. If you are dealing with data considered to be critical, you almost always keep it in two places. Another suggestion could be to write these emails out to a file that would enable some sort of batch loading. Set up a naming convention for the batch file. When loading this batch into your database add a field to hold the name of the batch. Archive the batch file somewhere and once again perform some type of maintenance on your archives. Regards, Ian gregg wrote: I have a Perl cron job that reads emails off a pop server once a minute and stores them in a MySQL database on a remote machine. I logged onto the MySQL server today and performed a select * from messages where received > 2003032200; Zero records were returned. I did some more investigating and discovered that no records had been inserted in 4 days. I performed a "flush tables" (Someone suggested it, I don't know what it does) command and the records were still missing, but suddenly the Perl cron job on the other machine, without any changes, was populating the database with new records. The 4 days of messages are still simply lost. Does anyone know what's going on here and how I can prevent this from happening again? Thanks, Gregg Allen
Re: MySQL DBI problem
On Wed, 26 Feb 2003, Paul DuBois wrote: > >I have a few books on Perl that have DBI examples but they are not > >made for MySQL and the examples are very Mickey Mouse compared to > >what I'm trying to do. Any suggestions or directions to potential > >resources will be greatly appreciated. > The webdb distribution here contains lots of sample code. There's also > a sample chapter there. I don't know whether or not you'll consider > it "Mickey Mouse", but I suspect not. I think the book is truly great...in fact I plan to buy a second copy for home. And the examples are generic enuf I use them for Oracle and Postgres as well as MySQL. (With minor changes, of course). Between this tome and Reuven Lerner's CORE Perl a web developer can get off to a flying start. (Unrelated but a very good recent release from Wiley is "Mastering Unix Shell Scripting" by Randal Michael which also has a companion web site and tarball). Anyway, thanks Paul for a terrific book. --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 Freedom is the right to yell theatre in a crowded fire. - Abbie Hoffman
Re: MySQL DBI problem
It would definitely help if you post source-code.
This might not be what you're looking for, but the general syntax for
INSERTs might be something like:
use DBI;
use strict;
my $user = 'username';
my $pass = 'password';
my $dbh = DBI->connect('dbi:MySQL:', $user, $pass);
my $sql = '
INSERT INTO TEMP(
FIELD1,
FIELD2,
FIELD3)
VALUES (?,?,?)';
my $sth = $dbh->prepare($sql);
open(IN, 'emails.txt');
while () {
my ($val1, $val2, $val3) = split("\t");
$sth->execute($val1, $val2, $val3);
$dbh->commit();
}
close(IN);
On Wed, 26 Feb 2003, gregg wrote:
> No. I'm not using placeholders. I'm stuffing all my INSERT variables
> into a "DO" statement. The one example I found on place holders was a
> "any DBI" example for "SELECT" not "INSERT." I changed the syntax and
> I didn't get an error but it wasn't inserted either.
>
> Thanks.
>
>
> On Wednesday, February 26, 2003, at 11:15 AM, PARLEY,THUNDER
> (HP-MountainView,ex1) wrote:
>
> > You are using placeholders, right? ;-)
> >
> > --Thunder
> >
> > -Original Message-
> > From: gregg [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, February 26, 2003 9:11 AM
> > To: [EMAIL PROTECTED]
> > Subject: MySQL DBI problem
> >
> > I am trying to read email off a mail server using perl, parse it, and
> > then store the fields in a MySQL database. I managed to write the
> > first two parts in a couple of days, but now I've spent two weeks
> > trying to get the fields into a the "Messages" database. I have got
> > several fields to go in and then I ran the same script the next day
> > with no changes and it wasn't working any longer.
> >
> > I have run into about every problem imaginable and I can't get a stable
> > script. Once when I thought I had it working, the script started
> > carping because there were embedded quotes in the email body which it
> > interpreted as delimiters. I tried the "qw" function and it was
> > bitching about another syntax problem.
> >
> > I have a few books on Perl that have DBI examples but they are not made
> > for MySQL and the examples are very Mickey Mouse compared to what I'm
> > trying to do. Any suggestions or directions to potential resources
> > will be greatly appreciated.
> >
> > Thanks in advance,
> >
> > Gregg
> >
> >
>
Re: MySQL DBI problem
No. I'm not using placeholders. I'm stuffing all my INSERT variables into a "DO" statement. The one example I found on place holders was a "any DBI" example for "SELECT" not "INSERT." I changed the syntax and I didn't get an error but it wasn't inserted either. Thanks. On Wednesday, February 26, 2003, at 11:15 AM, PARLEY,THUNDER (HP-MountainView,ex1) wrote: You are using placeholders, right? ;-) --Thunder -Original Message- From: gregg [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 9:11 AM To: [EMAIL PROTECTED] Subject: MySQL DBI problem I am trying to read email off a mail server using perl, parse it, and then store the fields in a MySQL database. I managed to write the first two parts in a couple of days, but now I've spent two weeks trying to get the fields into a the "Messages" database. I have got several fields to go in and then I ran the same script the next day with no changes and it wasn't working any longer. I have run into about every problem imaginable and I can't get a stable script. Once when I thought I had it working, the script started carping because there were embedded quotes in the email body which it interpreted as delimiters. I tried the "qw" function and it was bitching about another syntax problem. I have a few books on Perl that have DBI examples but they are not made for MySQL and the examples are very Mickey Mouse compared to what I'm trying to do. Any suggestions or directions to potential resources will be greatly appreciated. Thanks in advance, Gregg
Re: MySQL DBI problem
I am trying to read email off a mail server using perl, parse it, and then store the fields in a MySQL database. I managed to write the first two parts in a couple of days, but now I've spent two weeks trying to get the fields into a the "Messages" database. I have got several fields to go in and then I ran the same script the next day with no changes and it wasn't working any longer. I have run into about every problem imaginable and I can't get a stable script. Once when I thought I had it working, the script started carping because there were embedded quotes in the email body which it interpreted as delimiters. I tried the "qw" function and it was bitching about another syntax problem. I have a few books on Perl that have DBI examples but they are not made for MySQL and the examples are very Mickey Mouse compared to what I'm trying to do. Any suggestions or directions to potential resources will be greatly appreciated. I would guess that you're not inserting the data values into your SQL statements quoted properly. You should use placeholders, which will solve this problem. MySQL-specific DBI examples can be found here: http://www.kitebird.com/mysql-perl/ The webdb distribution here contains lots of sample code. There's also a sample chapter there. I don't know whether or not you'll consider it "Mickey Mouse", but I suspect not. Thanks in advance, Gregg
