One way to use queryparam AND cachedwithin/after would be to use a Query of Query with catch/try:
If the QofQ is cached then everything is fine. If there is no cache (ie either doesn't exist or doesn't match the cachedwithin/after attribute) then the error is cfcaught, dB is hit, and this recordset is cached in the QofQ.
<cftry>
<cfquery name="myQ" dbtype="query" cachedwithin="...">SELECT * FROM myQ WHERE '#hash(url.id)# = '#hash(url.id)#</cfquery>
<cfcatch>
<cfquery name="myQ" datasource="#request.dsn#">
SELECT *
FROM myTable
WHERE ID = <cfqueryparam value="#url.id#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfquery name="myQ" dbtype="query" cachedwithin="...">SELECT * FROM myQ WHERE '#hash(url.id)#' = '#hash(url.id)#</cfquery>
</cfcatch>
</cftry>
I've sucessfully used this technique to cache results from verity, to allow paging through large recordsets without hitting verity again, and recordsets returned from CFDIRECTORY, and I don't see why it wouldn't work for normal SQL statements.
In practice though, for the above example, I would just validate #url.id# up to the gills to make sure it was an integer, then run the query without cfqueryparam.
Cheers
Bert
________________________________
From: Philip Arnold [mailto:[EMAIL PROTECTED]
Sent: 16 June 2004 13:47
To: CF-Talk
Subject: Re: SQL query style (WAS: SQL search query)
On Wed, 16 Jun 2004 14:27:24 +0200, Pascal Peters wrote:
>
> > You should ALWAYS use CFQUERYPARM on EVERY query, no matter what
>
> I agree in theory, but you can't use it with cached queries.
Store the queries in a persistant scope, such as Application - it's
simple enough and gives you just as much control
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
