[thelist] CF: Clearing a cached query?

[Morgan Kelsey]

Raymond,

----- Original Message -----
From: "Raymond Camden" <jedimaster at macromedia.com>
To: <thelist at lists.evolt.org>
Sent: Friday, October 11, 2002 8:21 AM
Subject: RE: [thelist] CF: Clearing a cached query?


> In order to clear a cached query, you simply rerun the query with a
> timespan cache of 0,0,0,0.
>
> HOWEVER...
>
> The query must be the EXACT same SQL. In other words, if the original
> query looked like so:
>
> select * from foo
>
> and your code to clear the cache looks like so:
>
> select * from foo
>
> you will NOT be able to clear the cache? Why? Because the white space
> has changed.
>

are you sure about that?

<cf_wives_tale myth="queries with different white space will not clear the
cache">

:p


the setup : win2K, cfMX, sqlserver2000 (level 4 JDBC driver), ODBC access
and foxpro drivers


// SQL SERVER TEST 1 \\\\\\\\\\\\\\\\\\\

<cfquery name="testWhite1"
datasource="some_sql2000DB"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT * FROM blog_entries
</cfquery>

DEBUG OUTPUT:

testWhitey (Datasource=some_sql2000DB, Time=0ms, Records=8) in
E:\0sites\_loserasshole_com\_www\test.cfm @ 09:26:53.053

SELECT * FROM blog_entries

// SQL SERVER TEST 2 \\\\\\\\\\\\\\\\\\\

<cfquery name="testWhitey"
datasource="some_sql2000DB"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT

*

FROM


blog_entries
</cfquery>

DEBUG OUTPUT:

testWhitey (Datasource=some_sql2000DB, Time=16ms, Records=8) in
E:\0sites\_loserasshole_com\_www\test.cfm @ 09:27:28.028

SELECT

*

FROM



blog_entries




// \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

// ACCESS DS TEST 1 \\\\\\\\\\\\\\\



<cfquery name="testWhitey"
datasource="some_accessDB"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT prod_name, prod_desc, prod_specs
FROM
products
</cfquery>

DEBUG OUTPUT:


testWhitey (Datasource=some_accessDB, Time=172ms, Records=20) in
E:\0sites\_loserasshole_com\_www\test.cfm @ 09:29:54.054

SELECT prod_name, prod_desc, prod_specs

FROM

products



// ACCESS DS TEST 2 \\\\\\\\\\\\\\\


<cfquery name="testWhitey"
datasource="some_accessDB"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT prod_name,prod_desc,prod_specs FROM products
</cfquery>

DEBUG OUTPUT:

testWhitey (Datasource=some_accessDB, Time=15ms, Records=20) in
E:\0sites\_loserasshole_com\_www\test.cfm @ 09:30:48.048

SELECT prod_name,prod_desc,prod_specs FROM products

// \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\



// FOXPRO TEST 1 \\\\\\\\\\\\\\\\\\\

<cfquery name="testWhitey"
datasource="some_crappy_foxproDB"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT title, owner, caption
FROM gallery
</cfquery>

testWhitey (Datasource=some_crappy_foxproDB, Time=16ms, Records=27) in
E:\0sites\_loserasshole_com\_www\test.cfm @ 09:37:03.003

SELECT title, owner, caption

FROM gallery

// FOXPRO TEST 2 \\\\\\\\\\\\\\\\\\\

<cfquery name="testWhitey"
datasource="some_crappy_foxproDB"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT title,owner,caption FROM gallery
</cfquery>

testWhitey (Datasource=some_crappy_foxproDB, Time=0ms, Records=27) in
E:\0sites\_loserasshole_com\_www\test.cfm @ 09:37:51.051

SELECT title,owner,caption FROM gallery


// END TESTS \\\\\\\\\\\\\\\\\\\\\\\\\\\\


So, these tests show that different whitespace DOES INDEED clear the cache.



> A simple way to get around this is to take your query and place it in
> either a custom tag, UDF, or CFC. Add an option that specifies the
> timeout. That way no matter how you use the query (public side or
> admin), the SQL will remain the same and you won't have to worry.
>


no offense, but ewww.

unless I'm reusing the query (which is another avoidable problem), putting a
query in an include seems to add to disorganization, when there are
perfectly viable solutions (like the one I presented).




nagrom