Perl to delete data automatically

hi...
may i know how to delete data from db automatically using perl?
btw i want to be able to delete files that's a year old
eg. year 2000 data to be programmed to be deleted at the beginning of year 2002

the date in the date table is written as 12/3/00

thanks


TPolyAsked:
Who is Participating?
 
davorgConnect With a Mentor Commented:
Yes, I'm talking about a Perl program. DBI is the Perl module that we use to talk to databases. You would need to install the DBI module together with the correct Database Driver for your target database (which in this case would be DBD::mysql).

The program would be very simple. It would look something like this:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $server = 'name of your databse server';
my $db = 'name of the database';
my $user = 'user name used to connect to database';
my $pass = 'connection password';

my $dbh = DBI->connect("dbi:mysql:$db:$server", $user, $pass)
  || die "Cannot connect: " . DBI->errstr;

my $sql = 'some sql statement to delete the correct data';

my $sth = $dbh->do($sql);

If (as you said) you know how to use cron, then it's simple to set up a cron entry using "crontab". See "man crontab" for the details.

hth,

Dave...
0
 
suramsureshbabuConnect With a Mentor Commented:
You can tryout the following software to create a Win32 Perl service,

you can use the Win32::Daemon extension (http://www.roth.net/perl/daemon) and the Win32::Daemon::Simple extension (http://jenda.krynicky.cz), which depends on Win32::Daemon. These extensions provide full Win32 service functionality from a simple Perl script.

http://www.megadodo.demon.co.uk/perl/


the query would be like
delete from table where YEAR(date in the date table) = YEAR(CURRENT_DATE())-2;
0
 
TPolyAuthor Commented:
eh win32?
sorry i forgot ot add that i'm not using window
i'm using debian linux 3.0 and perl 5.6.1
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
davorgCommented:
That's pretty simple. Use DBI to connect to the database and run an SQL query to delete data that is too old. Set the program to run periodically using cron.

Dave...
0
 
TPolyAuthor Commented:
so how to write the program to run periodically?
I do know how to use cron
btw what type of program are you refering to? perl is it?

btw i'm using mysql as my db
0
 
TPolyAuthor Commented:
hi..
 the script u have provide is unable to work...

is it possible that my sql statment is not workable

delete from table where YEAR(12/3/00) = YEAR(CURRENT_DATE())-2;

thanks

0
 
davorgCommented:
Yes, it's quite likely that your SQL is incorrect. I very much doubt that you have a table called "table". And what is the date column called?
0
 
TPolyAuthor Commented:
ok
the sql statement i've put in this:
delete from yield where YEAR(12/3/00)=YEAR(CURRENT_DATE())-2;

Date column is called "date"
0
 
davorgCommented:
Yes, I know that's the SQL statement you used - you put it in your last post too :)

You should probably be asking this in an SQL forum, but I expect you need something like

delete from yield where year(date) <= year(current_date()) - 2

Obviously, you should test that in a transaction first so you can roll it back if there's a problem.

Dave...
0
 
TPolyAuthor Commented:
i've tried this statment and it is able to execute but unable to delete any date in
the date column.
0
 
davorgCommented:
Try experimenting with select statements until you find one that returns the list of rows that you need to delete. Then it's simple to convert it into a delete statement.

But like I said before, this is no longer a Perl question and you should be asking it in an SQL forum.

Dave...
0
All Courses

From novice to tech pro — start learning today.