I suspect its been fixed in MX, what with the improved support for mySQL and all.
However the root cause of the problem was mySQL's failure to support subqueries, so
this may not be the case.
I CFSCHEDULE the template below, which I got way back when from the Allaire KB. Works
like a charm. Its set up as a custom tag and whacks vars whose lastvisit value is
over 10 days old.
<!---
mysql_expire_client_variables.cfm
As of the release of ColdFusion 4.5.1, the mySQL database did
not support sub-selects. The ColdFusion executive (cfexec) uses
sub-selects to expire client variables from any configured client
variable storage data source.
Here is the SQL it will attempt to execute:
DELETE FROM CDATA
WHERE CDATA.cfid in
(SELECT CGLOBAL.cfid FROM CGLOBAL WHERE CGLOBAL.lvisit < <expire_date>);
DELETE FROM CGLOBAL WHERE CGLOBAL.lvisit < <expire_date>;
<expire_date> is an ODBC formatted date/time string constructed by
subtracting the number of days set for the client variable storage
in the ColdFusion administrator to the current date and time.
This CFML template can be run either manually or automatically via the
ColdFusion scheduler to expire Client variables stored in a mySQL database.
You must configure the datasource name via a URL paramter. An example
URL for a client data source named "mysql_clients" would look like this:
http://localhost/mysql_expire_client_variables.cfm?DSN=mysql_clients
--->
<!---
<CF_CVExpire
DSN="DSNName">
--->
<!--- get the timeout --->
<CFPARAM NAME="Timeout" Default="10">
<!--- construct the ODBC timestamp for the timeout --->
<CFSET variables.ExpireDate=CreateODBCDateTime(Now()-CreateTimeSpan(Timeout, 0, 0, 0))>
<!--- Run the query to find the IDs which should be expired --->
<CFQUERY Name="Expired" Datasource="#attributes.DSN#">
SELECT CGLOBAL.cfid FROM CGLOBAL WHERE CGLOBAL.lvisit < #ExpireDate#
</CFQUERY>
<!--- loop over query and delete records from CDATA table --->
<CFLOOP Query="Expired">
<CFQUERY Name="Deleted" DataSource="#attributes.DSN#">
DELETE FROM CDATA WHERE CDATA.cfid='#Expired.cfid#'
</CFQUERY>
</CFLOOP>
<!--- Now delete records from CGLOBAL table --->
<CFQUERY Name="Deleted" DataSource="#attributes.DSN#">
DELETE FROM CGLOBAL WHERE CGLOBAL.lvisit < #ExpireDate#
</CFQUERY>
<CFSET caller.TextMsg="Purged client data from Data Source '"&#attributes.DSN#&"' last
visit older than "&#ExpireDate#&" Records Deleted: "&#Expired.RecordCount#&".">
---------------------------------------
Matt Robertson [EMAIL PROTECTED]
MSB Designs, Inc., www.mysecretbase.com
---------------------------------------
---------- Original Message ----------------------------------
from: "James Smith" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
date: Sun, 27 Oct 2002 12:12:31 -0000
>I seem to remember a problem with CF4 flushing client variables from a MySQL
>database. Does anyone know if this problem still exists with CF5 and CFMX
>and if so, is there a workround?
>
>--
>Jay.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
