UNIX for Dummies Questions & Answers

Hello evreyone,

this is my first post, and to say i'm new to this is an understatement.
I know very little about perl scripts and hope some one can help me.
i'm looking to get a script that a cron job can execute.

what the script needs to to is
1) connect to a mysql database
2) go to a table called products
3) grab the products marked as free and 1 week old and delete that record from the table

Since the products are added on a daily bassis, it would be nice to run the cron job every day.

the database reads
free products are marked as ( prodtype )
creation date is marked as ( proddate )
and there is one called ( timetolive )

So far by reading what i can i have the perl script started.

-----------------------------------------
#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql

# CONFIG VARIABLES
$platform = "mysql";
$database = "nameofdatabase";
$host = "mysql.domainname.com";
$port = "3306";
$tablename = "products";
$user = "myusername";
$pw = "password";

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:$host:3306";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

}

-------------------------------------------

Now from what i know this could be all wrong

the path to the file that the cron job follows is
/home/myusername/domainname.com/cgi-bin/nameofscript.pl

i also need to use a crontab to set up the daily time to execute the perl script.


please be gentle, I bow down to Newbies

Exader

So far so good. You've connected to the DB...

Is the deletion business logic simple enough to be done in a single DML statement? For example...

DELETE from TABLENAME T where T.SOMEDATE <= (sysdate() - 7) and T.FREEFLAG = TRUE;

I don't know mysql so I'm giving a generic kind of syntax here... If the issue is not that simple, then you need to issue an appropriate SELECT statement, capture the results in perl, analyze them, and then issue delete statements on the appropriate rows... I haven't used the DBI in a while, so I don't remember the exact syntax of passing DML statements to the DBI, but in PERL, the results of the statement can be captured automatically in an array... Again I'm going to illustrate with "pseudo syntax". You translate into something that actually works...

$statement_to_exec = "SELECT X,Y,Z from T where Z...;"
(@myARRAY) = $myconnect->execute($statement_to_exec);

Now suppose you have properly set up your select statement so that the data comes out like this....

A,B,C
D,E,F
G,H,I

etc... That is, fields separated by commas (just as an example) and each record on its own line... So guess what.... $myARRAY[0] = "A,B,C" and $myARRAY[1] = "D,E,F" etc. That is each record is put in a separate element of @myARRAY! I'm assuming here that you've left perl's default line ending character alone, that the lines end with that (usual) character, etc. You can control all of this, but usually it isn't necessary....

OK, so now you can iterate over each line...
while ($line = shift @myARRAY) { ... logic here ... }

And then, you can split line into it's separate fields with....

($field1, $field2, $field3) = split /,/ , $line;

Now $field1 = "A", $field2 = "B", etc.

This is how you proceede... When you decide which lines must be deleted, say the line in $myARRAY[2] (the third line), then you issue a DELETE via the DBI for that line only....

As for the cron line, that is very easy. Just edit a file in your home directory called myCRONTAB (sysadmins might want you to use a system CRONTAB, or some other established one) and put a line in it like....

0 0 * * * /path/to/your/script.pl (says run at 0 min of 0 hr every day, I'll let you look up cron syntax for yourself)...

Then set up the job by entering cron myCRONTAB and if there are no syntax errors in the file, your good to go...

Oh... One more thing to remember.... when you run a script from cron it has NO ENVIRONMENT... So, you can't rely on PATH, or anything else. You have to set all that stuff up in your perl script....

Long I know, but I hope it helps....

Thank you for the info Quine

i'm going to try your suggestions