Solved

Perl to delete data automatically

Posted on 2004-08-11
11
171 Views
Last Modified: 2010-03-05
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
Comment
Question by:TPoly
  • 5
  • 5
11 Comments
 
LVL 7

Assisted Solution

by:suramsureshbabu
suramsureshbabu earned 50 total points
ID: 11780439
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
 

Author Comment

by:TPoly
ID: 11780686
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
 
LVL 8

Expert Comment

by:davorg
ID: 11781492
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
 

Author Comment

by:TPoly
ID: 11781518
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
 
LVL 8

Accepted Solution

by:
davorg earned 150 total points
ID: 11781997
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:TPoly
ID: 11790520
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
 
LVL 8

Expert Comment

by:davorg
ID: 11791692
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
 

Author Comment

by:TPoly
ID: 11806601
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
 
LVL 8

Expert Comment

by:davorg
ID: 11807776
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
 

Author Comment

by:TPoly
ID: 11807923
i've tried this statment and it is able to execute but unable to delete any date in
the date column.
0
 
LVL 8

Expert Comment

by:davorg
ID: 11807980
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This video discusses moving either the default database or any database to a new volume.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now