Solved

Perl to delete data automatically

Posted on 2004-08-11
11
174 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

813 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

18 Experts available now in Live!

Get 1:1 Help Now