CFML Auto Delete

Is ther a way to make coldfusion delete records from Sql Server automaticly.

I would like to keep the database purged of records older than 14 days.

I think I can muster out the code to handel that math but how would you get the application to auto execute this task.

maybe something like this

<cfset utctime = DateConvert('local2utc', NOW())>

<cfset del_date = dateadd("d", - 14, utctime)> 


<cfquery datasource="" name="del_mail">
SELECT MAILID, DATE_SENT
FROM USER_MESSAGES
WHERE DATE_SENT < or = TO DEL_DATE
</CFQUERY>

Code (markup):

Then use the cfqueryparam you gave me

<cfif listLen(form.mailId) gt 0>
   <cfquery datasource="" name="">
     DELETE 
     FROM USER_MESSAGES
     WHERE MAILID IN 
        (
        <!--- or whatever the correct type is for your db --->
       <cfqueryparam value="#form.mailid#" cfsqltype="cf_sql_nvarchar" list="true">
        )
   </cfquery>
</cfif>

Code (markup):

Not totaly sure where to go from here?

HAPPY THANKSGIVING!!!

 

That query should do it

<cfquery datasource="" name="del_mail">
SELECT MAILID, DATE_SENT
FROM USER_MESSAGES
WHERE DATE_SENT < #TheDateFourteenDaysAgo#
</CFQUERY>

Code (markup):

You could put that query in a script and set it up as a scheduled task that runs once a day.

 

You can either create scheduled tasks in the CF Administrator or using the <cfschedule> tag.

 

Do I add this to the application.cfm file?
What other attributes do I need to get this to work.

<cfschedule task="delete_sent" action="run" interval="daily" operation="">

Code (markup):

From what I read at http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-a30.htm I think I need to set up the DELETE query on a different page right.

But the how do I tell the cfschedule tag to exicute that page?

Do I put the file name in the operation attribute?

 

No you don't need to put it in the application.cfm because then it will run on every page request. Just put the cfschedule code on a separate page. Then run it once to create the task. Once you've done that the new task will run automatically on its own (ie daily).

I think all you should need is a few settings:
- name for the task
- the separate cfm page to run (ie page containing the DELETE query)
- start date/time
- interval (daily)

Something like this
<cfschedule action = "update"
task = "NameOfMyDailyTask"
operation = "HTTPRequest"
url = "http://127.0.0.1/thePathTo/yourQueryPage.cfm"
startDate = "11/27/2007"
startTime = "12:25 AM"
interval = "daily"
resolveURL = "Yes">