Expirey Date and CFMAIL 2

[DrDan1]

Hey. I'm trying to sort something out here but getting nowhere slowly. I have a form where someone can upload details to the db, but it has to expire in month and get deleted... say 28 days. The access db will have a record of the expirey date of 28 days after the date the record was created.

I assume I have to amke some sorta condition where if today's date Equals or is greater than the Expirey Date then it gets deleted. I also want an automatic email sent 5 days prior to deletion to the person who entered the record. I assume I use CFMAIL for this.

How do I do this whole expirey date thing. Help would be much much much MUCH appreciated. Thanks.

--Dan

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[csteinhilber]

Well... you either have a small chunk of CFML at the top of a page or every page (most conveniently, add it to your Application.cfm file)... so basically you're using each hit to your page(s) as the instigator of the event.

Or, you use CFSCHEDULE and/or the scheduler in CF Administrator to set up a schedule task that runs once a day and cleans out all the expired data.

The second solution is the more efficient way to do it... but certainly requires that you have access to the Administrator, or, at least, can set your server up to allow scheduled events. If you don't have that, then you're probably stuck with the first solution.

By "small chunk of code", what I meant was you probably don't want to do all your deleting every time the code is hit. What you can do is something like:

<CFLOCK scope=&quot;APPLICATION&quot; type=&quot;READONLY&quot;...>
   <CFSET dtLastCleanup = APPLICATION.lastCleanup>
</CFLOCK>

<CFIF DateCompare(dtLastCleanup, now(), &quot;d&quot;) GT 1>
    ... do your expired data deletion/cleanup
    <CFLOCK scope=&quot;APPLICATION&quot; type=&quot;EXCLUSIVE&quot;...>
       <CFSET APPLICATION.lastCleanup = now()>
    </CFLOCK>
</CFIF>
    :

so, even though the code is called repeatedly (with every page view), the bulk of your processing only runs once a day. So you're not likely to see much of a hit on performance.



-Carl

 

[DrDan1]

Thanks a bunch.
Just one problem. I'm using the SCHEDULE option but I'm having a a problem with dates. My database has an automatic date of one month after the record was made. Once this date is reached it will delete. I keep getting a type mismatch.

I can't copy and paste the code as it's on a different PC but this is the jist of it.

Delete when EpireDate = #DateFormat(Now())#
Date format returns the date in the format dd-mm-yy
I can't get the same in my access db. it's yyyy-mm-dd
Is there a way to make them the same? Either way. In the db I selected the option of having the data dd-mm-yy but when the cell is selected it reverts to hte long version and when the cell is deselected it goes back to the dd-mm-yy. Thsi is really frustrating me.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[jalbao]

The DateFormat() function does more than you are aware of.

Check it out: #DateFormat(now(),"yyyy-mm-dd&quot# - you can define the date format using a 'mask'.

On a side note, when storing dates in a database, it is generally best to use the ODBC date format. Not suprisingly, Coldfusion has a built in function for this:
CreateODBCDate() and CreateODBCDateTime().

 

[DrDan1]

ThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankyouThankYOU!!!!!!!!!!!!



----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!