ColdFusion CFQueryParam Binding vs. SQL Execution
I ran into a really frustrating "bug" last night that took me over an hour to debug! I was trying to run this massive ColdFusion query and kept getting an error about a data type not casting property in a CFQueryParam tag. The thing that was frustrating me so much, and making the error so hard to debug, was that the CFQueryParam tag was not even in a chunk of SQL that was supposed to be executing!!
Now, this query was massively huge (over 1,000 lines) and could certainly have been refactored into several small queries; but, it was one of those queries that started out small and grew very quickly as the client asked for more and more functionality to be added to the same page. But don't get bogged down with that aspect - instead, notice that what I am demonstrating in this dumbed down version of the query is a complete misunderstanding between ColdFusion CFQueryParam binding and SQL execution:
<!--- Create a query to hold our insert or update data. --->
<cfset qContact = QueryNew(
"id, first_name, last_name, age",
"integer, varchar, varchar, integer"
) />
<!--- Add a row to the update data. --->
<cfset QueryAddRow( qContact ) />
<!---
Update new row cells. NOTICE that I am only setting values
for ID, first name, and last name; I am leaving the AGE
attribute blank. That is because this is an UPDATE data set,
not an INSERT data in which the data would all be required.
--->
<cfset qContact[ "id" ][ 1 ] = JavaCast(
"int",
4
) />
<cfset qContact[ "first_name" ][ 1 ] = JavaCast(
"string",
"Anna"
) />
<cfset qContact[ "last_name" ][ 1 ] = JavaCast(
"string",
"Banana"
) />
<!--- !! LEAVING AGE FIELD NULL !! --->
<!--- Update the database. --->
<cfquery name="qUpdate" datasource="#REQUEST.DSN.Source#">
DECLARE @contact TABLE (
id INT,
first_name VARCHAR( 30 ),
last_name VARCHAR( 30 ),
age INT
);
DECLARE @id INT ;
<!---
Put one conact in our table.
NOTE: This table would not have to be populated
ordinarilly. I am only doing this so that the
example will work. Normally, this contact table
(or whatever table I was targetting), would be a
concrete table.
--->
INSERT INTO @contact (
id,
first_name
)(
SELECT
4,
'Anna'
);
<!---
Set the internal which we are going to use to see
if we are updating or inserting a record.
--->
SET @id = ISNULL(
(
SELECT
id
FROM
@contact
WHERE
id = <cfqueryparam value="#qContact.id#" cfsqltype="cf_sql_integer" />
),
0
);
<!--- Check to see if the ID was found or not. --->
IF (@id = 0)
BEGIN
<!---
The ID we have in our incoming data did NOT
correspond to an ID existing in our database;
therefore, we need to insert a new contact.
--->
INSERT INTO @contact
(
id,
first_name,
last_name,
age
) VALUES (
<cfqueryparam value="#qContact.id#" cfsqltype="cf_sql_integer" />,
<cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />,
<cfqueryparam value="#qContact.age#" cfsqltype="cf_sql_integer" />
);
<!--- Store the new ID. --->
SET @id = @@Identity;
END
ELSE
BEGIN
<!---
The ID we have in our incoming data DID
correspond to an ID in the existing dataase;
therefore, we are just going to update the row
based on the available data in the query.
--->
UPDATE
@contact
SET
<!--- Check for first name data. --->
<cfif Len( qContact.first_name )>
first_name = <cfqueryparam value="#qContact.first_name#" cfsqltype="cf_sql_varchar" />,
</cfif>
<!--- Check for last name data. --->
<cfif Len( qContact.last_name )>
last_name = <cfqueryparam value="#qContact.last_name#" cfsqltype="cf_sql_varchar" />,
</cfif>
<!--- Check for age data. --->
<cfif IsNumeric( qContact.age )>
age = <cfqueryparam value="#qContact.age#" cfsqltype="cf_sql_integer" />,
</cfif>
id = @id
WHERE
id = @id
;
END
;
</cfquery>
There's a lot going on here, but basically, we want to either insert or update a row in the Contact datatable. To determine whether we are updating or inserting, we take the given ID and try to match it up to a record in the database. If the ID exists, we are updating the row with ONLY the data that is available. If the ID does not exist, we are inserting the new row with all the required data fields.
Now, this should be broken down into different queries, probably, but again, not the point of the post. When I run the code above, we should expect to update the only row in the table, but instead, we get the following ColdFusion error:
Invalid data for CFSQLTYPE CF_SQL_INTEGER. coldfusion.sql.Parameter$DataTypeMismatchException: Invalid data for CFSQLTYPE CF_SQL_INTEGER.
Ok, so there's a problem with the integer value. But which CFQueryParam tag? Well, since I know that we have an ID that lines up with an existing ID, I know that we are running the UPDATE portion of the SQL, not the INSERT. And, if you look in the update portion, you will see that the only CF_SQL_INTEGER value is the age field. However, you will also notice that I am only updating that field if the age value is numeric (which it is not since we left it NULL in our incoming query).
So, WTF?!? The only other CF_SQL_INTEGER is in the INSERT portion of the SQL statement, but that code is not even executing! Furthermore, when I comment out the INSERT portion, the code runs the UPDATE portion without error. At this point in debugging, I was seriously losing my mind; I had code that was erroring out, but when I commented out a piece of code that wasn't even being executed, the rest of the code ran perfectly well.
I was just about to start rewriting this massive query when I had my eureka moment! It suddenly become so obvious: ColdFusion CFQueryParam data binding does NOT happen at the same time as the execution of the SQL code. Furthermore, CFQueryParam data binding doesn't even depend on the path of SQL execution.
Think about the ColdFusion CFQuery tag as a function call and the CFQueryParam tags are your function arguments. Arguments need to be defined and set no matter what the internals of the function are doing; the same is true for the CFQueryParam tags. My error in thinking in the above query was that the CFQueryParam tags within the INSERT portion were not being evaluated since that part of the SQL was not being executed. This was dead wrong. All of the CFQueryParam tag data bindings were being evaluated, but only some of those bindings were then put into use.
And, since all of the CFQueryParam tags were being evaluated, the CFQueryParam tag for AGE in the INSERT portion was also being evaluated. However, since the age column in the incoming data set was NULL, ColdFusion could not properly convert it into an INTEGER value (hence the data conversion error). As a quick fix for this, I put a Val() around the value:
<cfqueryparam
value="#Val( qContact.age )#"
cfsqltype="cf_sql_integer"
/>
Now, even though that part of the SQL was not being executed, the CFQueryParam tag was evaluated properly.
It is rare that I have a query with such complicated logic that combines both ColdFusion and SQL conditional statements; but, when it does come up, it is very important to understand when ColdFusion CFQueryParam tag data binding occurs and to know that this happens regardless of SQL statement execution. In fact, you should probably think of all of your CFQueryParam tag bindings as taking place as the first step in the ColdFusion CFQuery tag; then, once all data bindings have been implemented, your full SQL statement will executed.
Want to use code from this post? Check out the license.
Reader Comments
Can I suggest something to try?
I found that overall unless very few of the statements will actually be an update on the table that performing the update statement then checking @@rowcount (SQL Server specific of course) to check the number of rows affected then doing an insert if neccisary at that point is quite a bit faster than doing a select then doing the update or insert depending on the select.
Or if 99% of the statements are going to be inserts (it really has to be nearly all of them) than trying the insert and catching the duplicate key errors (of course that almost never happens) and updating at that point can also be faster if you are performing a significant number of inserts.
Word to the wise and heed my warning:
It's a very common misconception in the CF world that using val() around a value will ensure the value is an integer.
THIS IS WRONG!
Val() returns the numeric value of a string.
val("3.2456") => 3.2456
val("1234") => 1234
If you're application calls strictly for integer values (such as primary keys in a database), you need to use the Int() in conjunction with val() to ensure an integer value is returned.
int(val("3.2456")) => 3
int(val("1234")) => 1234
Damn browser. Finishing my previous comment.
As such your cfqueryparam:
<cfqueryparam value="#Val( qContact.age )#" cfsqltype="cf_sql_integer" />
Should be:
<cfqueryparam value="#Int(Val( qContact.age ))#" cfsqltype="cf_sql_integer" />
@Mark,
I like that. I never thought about checking the @@RowCount after an update and then performing an optional insert. Very clever.
@Tony,
You make a good point about the Val() method in general. However, as far as it goes when dealing with the CFQueryParam tag, ColdFusion (I think) should convert the data type for you based on the cfsqltype attribute (I think!?!).
To test, I ran this:
<cfquery name="qTest" datasource="#REQUEST.DSN.Source#">
SELECT
( <cfqueryparam value="3.5" cfsqltype="cf_sql_integer" /> ) AS test
</cfquery>
In the final query, the "test" column is "3", not "3.5". I don't know if ColdFusion is doing the conversion, or if SQL is going the converstion, but somehow, the CF_SQL_INTEGER type is making the conversion happen.
@Ben,
Actually it's the driver that is doing the conversion. The only reason I stress the point about doing the conversion within CF with int() is because the Oracle driver I'm using doesn't and throws an error.
@Tony,
Very interesting! I wish the documentation was a bit more obvious on this matter (maybe I just missed it). Thanks for pointing that out.
Jesus Christ on a stick.
He also likes being on a bicycle :)
Seems like a prime example of code that belongs in a stored procedure to me.
@Eric,
You might be right. I know very little about stored procedures; but, this is certainly a place where ALL speed boosts would be helpful. And, since parameters need to be passed into a stored procedure, certainly, we wouldn't run into the problem of not understanding what data is where, when.
I am finding CFQueryParam weirdness of a different sort. I have this part of a query
AND lt.ltName = <CFQUERYPARAM VALUE="#REQUEST.thisProp#" CFSQLTYPE="CF_SQL_VARCHAR">
Where ltName is a varchar(50), and REQUEST.thisProp is "hartstone".
The result is an "Unsupported data conversion" error. Even if I do this it fails:
AND lt.ltName = <CFQUERYPARAM VALUE="hartstone" CFSQLTYPE="CF_SQL_VARCHAR">
To fix it, I just did this:
AND lt.ltName = 'hartstone'
I have CFQueryParam doing the identical thing throughout the application, and it is only in this query it blow up.
Not directly seeking answers, but wanted to put this out there, since it was my own hour of debugging along the same lines.
@Paul,
Did you recently change the structure of the database table that is referencing?
If so, the CFQueryParam will get ColdFusion to use a cached structure of the query, which can cause problems. This is usually what is happening if CFQueryParam causes a data-type-conversion error.
Also, please please please tell me you are not using SELECT *.
It is a SELECT *, and I had added a column in the past day, but it's not a cached query. Would CF still cache anyway?
I'm trying to remember the other times this has happened (it's uncommon, and my previous troubleshooting was rushed), and it's possible I had made changes around then, too.
I suppose I'm also confused since I've made changes to other tables that have SELECT * queries w/ CFQueryParm, and have not seen this before.
@Paul,
1. By using CFQueryParam, ColdFusion automatically caches the query (or at least tries to).
2. Don't use "SELECT *"... period :)
1) I certainly never knew that, and wouldn't have guessed it, either, since I use it in updates, inserts, etc. I also suppose only having gotten the error in this one case, based on all of my uses of it, makes it even more suprising.
I am espcially suprised because the error started hours after I made the change. I'm going to assume this is a long cache.
2) I know, it's lazy in a lot of ways, but I found it necessary over time. We have a lot of code we reuse on other sites, but the clients customize the data the site will hold. I ended up having to change too many pages; adding values in some, and removing unused ones in others.
I have tried to think of a custom app to address this, and I suppose one is possible, but I get the feeling it would have nearly as much overhead to maintain the calls as it would to do what I am doing now.
If I start running into the same problem elsewhere, I will probably change my wicked ways ;)
@Paul,
I don't really understand how the underlying caching mechanism works. I think it works based on the actual text in the query. So, if you are getting this, you can probably add some spaces or a line return to the query string and ColdFusion will *think* it is a different query since it has different characters.
I hear you about the select * in a big app, especially when its being used in a lot of places. I don't really have any good suggestions for that :)
I think it must be more than that, because I changed the query considerably when degugging. I took out different parts to narrow down what was the cause, and would error in different states. If it's a cache, it behaves much differently than a standard cache.
If it weren't for the fact our customers like when our sites work ;), I would put it back and try to force the error (I've never been able to get it to happen locally). That way I would change the SELECT * aspect and see how it behaves.
If I run into it again, I'll desconstuct it differently when debugging so I can poke it w/ a stick w/ all this in mind. I'll let you know if I come up w/ anything insightful.
Thx for the feedback. Btw, thx for the site, too, and pls keep it up. It's been a resource on more than one occasion.
@Paul,
Yeah, keeping those clients happy is always a good priority to have :)
Glad you like the site!
Nice article; I have helped a few developers that have had troubles understanding this too. The way that I help them is through examples; think of your query in cfquery like html in cfsavecontent. Any SQL that you have in the cfquery tag while get sent, unless you have cfif (or cfswitch) excluding that portion. This is similar to html being saved in cfsavecontent. Once you can separate coldfusion and SQL code logic in your mind, you will find you can debug these issues a lot quicker.
I have also found the use of parsing of XML into a variable table very useful (it seems to be exclusive to MS SQL). By parsing XML into a variable table, you can send multiple rows to be added, updated & deleted in the one parameter.
With the unsupported data conversion error, I have found a coldfusion restart refreshes the cache.
Ran into this again today, but not questioning the problem itself. I'm actually adding to the last post, about clearing the cache.
Is there a way to clear the query cache, similar to the clear template cache button in CF admin? I'd like to be able to do this w/o a restart when the error comes up.
You could always change the query slightly. As one of the previous posts suggest, add an alias.
Re: Clearing the query cache - set the max number of cached queries in CF administrator to zero. I don't think you even have to reload the page once you do this, though I generally do to make certain. I find this a heck of a lot easier than making edits to my queries (I am picky about whitespace) every time I want the query to be refreshed. CF supposedly looks at BOTH the name of the query and the query SQL when determining caching so if you change either it won't be cached any more. But I prefer the CF admin route as it doesn't require any changes to my code. Hope this helps.
I think using "SELECT *" can slow down your queries as the database will first have to check which columns it should be returning before returning them. I generally only use SELECT * in CF query of queries.
I have stopped using CFQUERYPARAM on 'safe' values, meaning only VAL()'d data.
As far as I can tell #Int(Val(qContact.age))# is equally as safe and uses less resources than <cfqueryparam value="#Int(Val(qContact.age))#" cfsqltype="cf_sql_integer" />.
I don't believe there is any SQL injection which can get through a VAL() function. Am I wrong?
If the query requires an integer, how is the cfqueryparam any safer than a real verified integer? Additionally, using #Int(Val(qContact.age))# defaults to zero, so it can't be Null and won't throw an exception.
My simply saying 'it hasn't bitten me yet' only goes so far. I am very careful when I don't use cfqueryparam. I have been called in to clean up a SQL injection mess in the database and secure all the code when a previous developer didn't do their job. Not fun. I clearly understand the importance of cfqueryparam. I have also learned that many cfqueryparams can weigh on the load time, so I try to only use it when required - on text and date values (anything not VAL()'d). But if I'm VAL()'ing it then I don't use cfqueryparam. Asking you and the community, is this a bad practice?
@Bagar,
I believe you are correct that Val() will cut out any SQL injection attacks as it strips the non-numeric values *Before* the SQL statement is even finished being designed.
The only thing that I can say is that I have been taught that CFQueryParam offers some performance benefits due to the way SQL statements get executed with variable bindings. I don't know the internal mechanisms of this, but it's what I've been told.
That said, I have stopped using it for static values, such as:
is_deleted = 1
... as I found this to be easier to read / write.
There are performance gains associated with CFQUERYPARAM if the underlying DBMS supports bind variables and the query statement is executed multiple times. For example, if you use bind variables in Oracle, it will check to see if there is an existing execution plan for the SQL statement, whereas if you don't use them, it will parse the statement and come up with a new execution plan for it. I believe this is the case even when static variables are used.
@Dcs,
Thanks for the insight.
I'm having an issue where CFQUERY PARAM is slowing down the query to the point where using them the query times out, without them, it runs in 10-15 seconds. I've consistently seen this. Anyone hear of this?
This is using ORACLE as the database
@Alan,
Funky! I've never heard of anything like that.
I've had something like that happen when I've had code inside of <CFLOCK> statements that was bad, and caused everything to timeout (I can't remember what I had miscoded). It didn't throw a helpful error, and I only figured it out when I started commenting out portions of code.
Thanks for your quick responses. CFLOCK is not used.
I'm not a ColdFusion developer, I'm the Oracle DBA. I work with 3 Cold Fusion Developers. I can extract the query out of ColdFusion, run it in pl/sql developer and it returns in about 15 seconds.
I had suspected that it may be a JDBC issue. We are running Oracle RAC and use the Oracle JDBC drivers so Oracle TAF is supported. After just finding this article maybe it's not a JDBC issue?
http://www.coldfusionmuse.com/index.cfm/2008/11/18/performance.and.cfqueryparam
Anybody have any idea what is the best proven method to connect CF7 MX to an Oracle RAC Cluster?
Have you tried running the query in ColdFusion without CFQUERYPARAM? That would give you more information as to whether CFQUERYPARAM is the actual cause.
Yes I have, and without the CFQUERYPARAM, performance is consistent as seen when run directly in PLSQL/Developer.
I ran it at the same time of two different web servers, with and without cfqueryparam, without was 15 seconds, with timed out.
Hmmm, well, as a practical matter I would just avoid using CFQUERYPARAM for that query - just make sure you verify your parameters to avoid SQL injection (which isn't as much of a problem with Oracle as it is with SQL Server anyway).
It sounds to me like an issue with converting from coldfusion data types to oracle data types (or something similar). How many different data types are you using in your queryparams? Can you try putting the queryparams in one at a time and see if it is a specific one?
I've recently run into a similar issue with CFQUERYPRAM as well. I have the simplest query running at 750ms to an SQL server. Within SQL Studio it returns in under 1 ms.
If I isolate the same query in a separate CFM template, it returns just as fast. But if the query is embeded in a function within a component, it executes in 750ms. I took out the CFQUERYPARAM and it results in 4ms.
This never used to be an issue, until CFMX8. Hadn't touched ColdFusion in about a year prior to that which was then CFMX7.
DonV69,
Thanks for your response. While I did not update, eXcalibur.lk hit the nail on the head. It was a parameter conversion problem.
Thanks for all.
Alan
I know this is an old thread, but I found something today that answers a question I put here a while back.
If you ever need to clear the query cache, and don't want to (or can't) restart CF, the following works like a champ:
1. Login to your CF Admin
2. Go to the Data Source in question.
3. Change "Max Pooled Statements" to 0.
http://www.mail-archive.com/cf-
talk@houseoffusion.com/msg214427.html
You can put it back to whatever value you want after that.
@Paul,
Ah, cool tip.
I'm not a ColdFusion developer, I'm the Oracle DBA. I work with 3 Cold Fusion Developers. I can extract the query out of ColdFusion, run it in pl/sql developer and it returns in about 15 seconds.
We recently had a similar issue on one of our ORACLE servers and it turned out to be one of the initialization parameters - optimizer_index_cost_adj. I don't have a clue why this parameter would affect the performance of queries using bind variables though - the lower it is set, the less expensive index scans appear to the optimizer relative to table scans.
@Dcs,
That kind of performance difference is odd and goes beyond my understanding of SQL optimization mechanisms.
That kind of performance difference is odd and goes beyond my understanding of SQL optimization mechanisms.
Mine too - thankfully we have a DBA who can look at the access paths and make adjustments accordingly.
Revisiting this older thread, I have some additional suggestions on the subject of clearing CF's query cache (which is enabled via the CFQUERY attributes CACHEDWITHIN or CACHEDAFTER). While one approached offered for clearing it will work, it requires access to the CF Admin. There is in fact a little-known CF tag that can do it, too. Since Ben's blog is so widely read (and for good reason!) I'll address that and a couple related sources of frequent confusion on this topic.
First, as for setting the admin query cache max to 0, that will work. But really, it's simply any change in the value. That will flush the query cache. It's not documented, but one can see it happens by viewing the query cache status page in the CF8/9 Server Monitor. (As for the suggestion of And the max pooled statements has nothing at all to do with CF's caching of query results.)
But what if you don't have access to the CF Admin? Well, some people (though none here, yet, fortunately) have proposed changing the cachedwithin timeout, and while that can work in some instances, it cannot be relied upon. It only works if you re-execute each cached query (all cached variants, which can be nearly impossible) while the value is set to 0. It doesn't magically change the timeout for already cached results that are not re-executed.
There is, however, a built-in, documented way to clear the query cache. And (for better or worse) it doesn't even require CF Admin access. It does happen to be the worst-named of all CF tags: CFOBJECTCACHE. It has nothing to do with objects. It's got just one attribute/value pair: action="clear".
It will wipe out all cached query results, for all queries. Sadly, there is no way (yet) to wipe out only the cached results for a given query or datasource.
Hope that's helpful to some.
@Charlie,
Excellent tip. Leveraging the built-in caching features in ColdFusion is something that I have never really been good at. With CF9 and the new Cache methods, hopefully I'll get better; but, pre-CF9, this is really good to know (not that this is any different really in CF9 - was just going off on a tangent).
Here is my understanding of bindings for those who may have not put it all together. Some of this may be old info to you.
When you run a query the sql server must first put together an execution plan. That is what sql decides is the best way to approach the query you handed it.
If your query is this
(select firstName from tbNames where id = 3)
sql will decide how to find your results. If you run that EXACT same query multiple times, it will use the cached version of the execution plan saving some work and time on the sql server.
If you then run this query
(select firstName from tbNames where id = 4)
sql server has to create a new execution plan for that query and caches it separately.
On the other hand, if you do this
(select firstName from tbNames where id = <cfqueryparam value="3" cfsqltype="CF_SQL_INTEGER">)
and then follow it by
(select firstName from tbNames where id = <cfqueryparam value="4" cfsqltype="CF_SQL_INTEGER">)
sql will create the execution plan on the first call and use the cached execution plan on the second. That is great for performance. Some large queries can take longer to build the execution plan than to actually execute it, but can cause the issues that brought us all to this post.
I *think* ColdFusion query caching is quite different. If you cache a query and using ColdFusion, you are asking ColdFusion to hold a copy of the results of the query in it's own cache. If you run the EXACT same query again, ColdFusion will grab the results from it's local cache and never even hit the sql server at all.
This elimitates:
Transmitting the query to the sql server.
SQL creation of the execution plan.
Execution of the execution plan.
Transmitting the recordset over the network.
This can save huge processing but be a nightmare if your data changes.
@Keith,
Yeah, I think the ColdFusion query caching is different. To be honest, I never learned too much about query caching because originally, you couldn't cache AND use CFQueryParam. That has since been updated in newer releases of ColdFusion. But, I had already skipped over it.
I should really take some time to dig deeper into cachedWithin and cachedUntil. I am sure there is some pure gold there that I am not taking advantage of.
@Ben et al.,
Cached queries are absolutely pure gold. They're what you want to use when (a) you have a lot of memory (they take up space in memory, after all) and (b) you have a set of data that doesn't change frequently or that doesn't need to be real time. For example, suppose you're writing a front-end for a retailer who has a bunch of product categories, and these categories don't change often. You could cache the query say once per day. (And if you absolutely need to do an update, you can clear the query cache.) Each time the query is called after it's been cached, the cached results will be returned rather than CF hitting the database again. Very fast.
Cached queries are identified by both name and SQL. So if you have two queries named "get_categories" but with slightly different SQL, they would be considered two queries for the sake of caching. Similarly if their names are different but the SQL is the same. Unfortunately, the maxrows attribute is not taken into account in these cases, so if you have a cached query that returned 100 rows initially, and you later want to grab just 10 rows, the query will return 100 rows because that was what was cached! The way to get around this is to limit the # of rows returned in the SQL itself (using TOP in T-SQL and ROWNUM in Oracle ... I'm sure there are ways to do it in MySQL and PostgreSQL as well).
@DCS,
I think one of the biggest gripes that people have about cached queries is that it's not (or was not) possible to clear the object cache in a granular way - it was all, or nothing at all. As such, you couldn't invalidate a specific cached query; you'd have to flush all cached queries to update a single one.
I think maybe this is now different in ColdFusion 9? I can't remember. I haven't moved to CF9 in production yet (very soon for this blog!!); at that time, I am sure I will start drilling more CF9-specific features into my head :D
@Ben,
It may be possible to clear a single cached query now - I'm not sure. (There is a quick-and-dirty way of doing it -- making a small change to the SQL -- but I don't think that was what you had in mind. :)) However, even if you have to clear all your cached queries, this is no worse than having no cached queries at all, so you don't actually lose anything (except memory) by caching queries (unless you write crappy SQL code and are using query caching as a crutch!).
@DCS,
Yeah, good point re: using cached queries as a crutch. The only place where I have found it to be really important (I think) is when my site gets indexed and many blog posts get requested at one time. This can actually lock up my database (I think). Of course, like you are saying - that might be a factor of poorly written queries. I'll definitely admit that I am not an indexing master :)
try this:
<cfqueryparam cfsqltype="cf_sql_bigint"
Thanks!
Just came up with this same problem myself, yet again.
I tried removing the cfqueryparam, changing the name of the variable, testing the variable with math operators, and finally using int(val()) worked.
Wouldn't it make sense for the cached query to be refreshed on an error? (I'm not even convinced it was a cached query that caused my specific problem but I did try renaming.
I feel this is a bug, anyone agree?
@dcs, @Ben,
I realize I'm joining this discussion late but FWIW, here's how I clear a ColdFusion query cache on a granular level.
<cfset variables.cachedWithin = CreateTimeSpan(1,0,0,0) />
<cfif IsDefined("url.clearquerycache") and url.clearquerycache>
<cfset variables.cachedWithin = CreateTimeSpan(0,0,0,0) />
<cfquery name="variables.myQuery" cachedWithin="#variables.cachedWithin#">
[sql goes here]
</cfquery>
When I want to clear the query cache on that page, I pass clearquerycache=true in the url and the query that normally (in this case) would be cached for 1 day will instead get cleared immediately due to setting the cachedWithin to CreateTimeSpan(0,0,0,0). I can also pass different url variable names to even more granularly clear specific queries on the same page; also, if I'm concerned about security, I can have a separate page/form/web service/etc. to set persistent variables (e.g., session) that my query can look for when determining whether to clear the query cache.
HTH,
Lisa
(Sorry, I missed an ending </cfif> in there).
Hey Lisa, thanks for sharing. But I would warn people not to get too excited about using it. The chances that it would work are somewhat slim, I'd argue.
The caching mechanism keys on the EXACT sql statement that was generated when it was cached (in whatever CFML page was running when the query was first cached). You would have to replicate it EXACTLY here to clear it the way you are proposing, down to every space and new line that may be embedded.
While it may well be possible in your case that you've found it to work as expected, it could be that your SQL is created in such a way that this isn't an issue. But most CFML I see generates SQL using embedded CFML (like CFIF and other tags) which are removed during the processing of the page, so that the generated SQL has all kind of spaces.
I'm sure some have noticed that in the debug output (if enabled) when they see a query showing there. And certainly if you could somehow obtain that exact SQL and use that in a tool like yours, that should work (though as I note in my comment above, it's got to be exact down to even the values used in a WHERE clause, so you may need to clear every variant.)
One last tip: if one uses the CF Enterprise Server Monitor, and has enabled "start profiling", then it has a "cached queries" page which will show the exact SQL for any queries that have been cached (as long as they were cached before "start profiling" was enabled). That could allow you to get the values to clear.
And someday someone could/should certainly create a tool to call the Admin API to get those values, and pass them to some mechanism to clear them (or re-execute them with a 0 cachedwithin as Lisa shows).
Until then, though, it may just be that clearing the cache entirely will be easier for many (using cfobjectcache), though granted it's totally a case of "throwing the baby out with the bathwater" int that it clears ALL cached queries for all apps on the CF instance.
We can also hope that perhaps later versions of CF will improve all this, adding perhaps a means to label a set of cached queries, so as to be able to remove all under that label at once. (There needs to be a new method in the AdminAPI to clear the query cache, also.)
Hope that's helpful.