Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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


0
TPoly
Asked:
TPoly
  • 5
  • 5
2 Solutions
 
suramsureshbabuCommented:
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
davorgCommented:
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now