coldfusionJedi

This entry was posted on Wednesday, March 24, 2004 at 5:23 AM. It was filed in the following categories: ColdFusion. It has been viewed 4,770 times and has 27 comments.

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

• Comment 1 written by Bryan F. Hogan on 24 March 2004, at 7:43 AM

  

  Don't know if this is related or not. When using cfqueryparam on a query within a CFC that is cached in the application scope and you make a change to a column in the SQL table, even after you refresh the CFC cache an error will be thrown that the column you just added does not exist.
  
  Only time it comes back to life is after a restart of the CF service.
  
  Like yours, it seems to be random and I haven't been able to reproduce it at will.

• Comment 2 written by dave ross on 24 March 2004, at 7:52 AM

  

  RAY!
  
  I have been dealing with these issues for some time, and for while I thought no one believed me. Yes, CF does cache the schema of the db when using cfqueryparam... and if you change the table definition (even if you add fields that aren't used by the query), cfqueryparam will choke.
  
  I can almost always reproduce it if the query is inside a CFC that's in a shared scope. I think the reason it doesn't seem reproducible is that CF caches a certain number of queries, and as ones that don't use cfqueryparam come in, yours get pushed out out the cache (and are reloaded, and don't error).
  
  I made a few posts about it on the MM forums... Ike Dealy also mentioned it on his blog.
  
  

• Comment 3 written by Eric Jones on 24 March 2004, at 8:13 AM

  

  Hey ray, your link to mindseye isn't working is that site down?

• Comment 4 written by Raymond Camden on 24 March 2004, at 8:21 AM

  

  Yep, power outage at the office. Luckily I'm remote.

• Comment 5 written by Doug Keen on 24 March 2004, at 9:34 AM

  

  My guess is that queries with CFQUERYPARAM are treated like prepared statements, and a compiled version of that specific query is cached on the DB or in CF (rather than CF caching the DB schema). When you change one of the tables involved in that prepared statement, the query version compiled against the old schema may still exist, but is now invalid and needs to be recompiled. I assume that when the query is called again, either CF or the DB isn't smart enough to know the statement needs to be recompiled, and the cached version is used anyway, which may or may not work, and could cause some weird, unpredicatable effects. When you added the semicolon, Ray, you in effect created a brand new query which would trigger a new compilation (fixing the problem). Similarly, a CF service refresh would clear the cache.
  
  Not sure how shared scopes would affect this. Maybe the prepared statement is cached right in the persisted CFC instance, rather than the normal repository (where it can be knocked out of the queue like Dave said)?

• Comment 6 written by Daniel D on 24 March 2004, at 6:35 PM

  

  If all the where statement items are build with queryparam then CF runs the query as a prepared statement. Which for most DB server is a mini stored procedure. Which means that the execution plan for the query is cached so that when you run it again with different paramaters it can reuse that execution plan. Great for getting better performance but looks like it can be a pain with development. I have not run into this myself but I suspect if you kill your DB connections that the cache will expire. Not sure if there is a easy way to close all connections for a given datasource though one of the CF factories or not. Might be worth looking into if you don't/can't restart CF to resolve the issue.
  
  

• Comment 7 written by Doug on 29 March 2004, at 6:32 AM

  

  We have had the same experiences. I have found that if you use the CF Admin tool to disable a DSN, then re-enable it, things clear out. Better then a service restart.
  
  

• Comment 8 written by Peter Windemuller on 9 April 2004, at 7:53 AM

  

  Had the same experience today, because I changed a database type from tinyint to varchar;
  
  Thanks for the entry!
  

• Comment 9 written by Chris Tsongas on 12 April 2004, at 10:08 AM

  

  This has nothing to do with CFCs because the problem persisted for sevaral days on a site of mine that doesn't use them. Disabling and re-enabling the data source fixed it as described above. My query also used
  
  select *
  
  and I'm curious if anyone has had this problem with queries that don't use that? Has anyone submitted a bug report?
  
  Helpful thread....

• Comment 10 written by Dave Cordes on 31 May 2005, at 5:16 PM

  

  Same thing here. It's a real pain in the butt because there is an extra step involved every time you make a database change. It shouldn't be this way!
  
  Has anyone submitted a bug request? I think this should qualify as one.
  
  Thanks.

• Comment 11 written by Rob on 4 October 2005, at 1:53 PM

  

  same over here. It's getting really annoying.

• Comment 12 written by Damen on 6 October 2005, at 5:48 PM

  

  Thanks for this,
  reinforces my general rule with coldfusion caching craziness "When in doubt kick it in the guts (restart JRUN)"

• Comment 13 written by Daryl Banttari on 7 December 2005, at 10:34 AM

  

  Ray,
  
  I'll bet just adding (or removing) a space in the query would have the same effect as adding a semicolon: it becomes a "new" CallableStatement, and will therefore appropriately map to the updated schema.

• Comment 14 written by Haris Kusumo on 16 September 2006, at 12:31 PM

  

  Hi,
  
  I got similar problem with this queries
  
        <CFQUERY NAME="getMemberInfo" DATASOURCE="#Request.dsn#" USERNAME="#Request.uid#" PASSWORD="#Request.pwd#">
           SELECT    a.mem_id, a.firstname, a.lastname, b.status, c.role
           FROM   t_mems a, t_usrs b, t_roles c
           WHERE   a.mem_id = <CFQUERYPARAM CFSQLTYPE="cf_sql_integer" VALUE="10">
           AND      a.mem_id = b.usr_id
           AND      a.mem_id = c.mem_id
        </CFQUERY>
  
  It seems that when I put value 10 on cfqueryparam I got the error message but any other number except 10 is working fine.
  
  Is number 10 some kind of magic number or I am missing something.
  
  Regards
  - Haris -

• Comment 15 written by Snack on 27 September 2006, at 10:15 PM

  

  I love you Ray!
  
  Just spent 45 minutes trying to figure out why after updating a table in my DB with two new rows any data I updated would succeed but then error upon request of the new data. Any record I had not updated would not error at all but all the data looked the same. Gack! Requesting updated data would generate the "[Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type" error from this line of code, which worked perfectly for all data that hadn't been updated (and all data looked the same... sigh)
  "WHERE game_id = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#url.id#">"
  
  At a loss I googled the error, this page was the number one hit and one cold restart later I'm back to work! Speaking of cold, here's a cold one tipped in your general direction. Cheers mate!

• Comment 16 written by Raymond Camden on 28 September 2006, at 8:51 AM

  

  No worries! Glad this post helped.

• Comment 17 written by Jason Weible on 14 December 2006, at 3:30 PM

  

  Is this going to be fixed or anything? It seems a bit ridiculous.
  
  I'm glad I found this page though, I was going crazy trying to figure out what the deal was.

• Comment 18 written by Michelle Kinsey Bruns on 10 January 2007, at 12:26 AM

  

  Okay, this is the most ridiculous bug EVER. Thanks for calling it out.
  
  You know, I'd wondered if caching was somehow involved and I thought I *had* modified the query in such a way that any cache should have been flushed. I had commented out everything between CFQUERY and /CFQUERY, and typed in its place a shorter, hardcoded version of the query to bypass CFQUERYPARAM. Of course the query worked so I undid my changes. By "recycling" the original CFQUERY tag, with an unchanged name attrib, I expected I'd eliminated the notion of caching as a culprit, per docs w/r/t CACHEDWITHIN and CACHEDAFTER. I'd be looking for the answer into my old age if I hadn't found your blog...
  
  I guess my assumption having been wrong makes sense (for lack of a better term) in light of Daniel D's post, if the deal is indeed that the bug is related to having all where items built w/ CFQUERYPARAM. But talk about a needle in a haystack.

• Comment 19 written by Kamil Saiyed on 18 September 2007, at 7:08 AM

  

  I was completely stumped by this same exact error. It had it working perfectly on my development server, but the moment I ported the files on my production server, it began throwing this error: "[Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type." My search led me to this page. I restared CF and it worked fine thereafter.

• Comment 20 written by Mike Haggerty on 18 December 2007, at 10:09 AM

  

  I had the same issue today when changing a dbtype from int to varchar. Thanks for the entry!

• Comment 21 written by Eric on 13 March 2008, at 7:07 AM

  

  The solution: due to caching of the odbc driver, you do NOT have to restart CF.
  
  1. Go in the settings of the current datasource an UNCHECK 'Maintain Connections' --> submit the change
  
  2. Rerun the page with the changed view/table/sp and make sure it's running fine.
  
  3. Go back into the odbc settings an re-enable the 'Maintain Connections' --> submit the change
  
  4. done!
  
  Now everything should be working fine without restarting CF where a lot more i cached, like templates etc.

• Comment 22 written by Raymond Camden on 13 March 2008, at 8:12 AM

  

  Nice Eric!

• Comment 23 written by Alexander Sante on 15 January 2009, at 8:24 AM

  

  Thanks guys, this solved my problem as well. Great Post!

• Comment 24 written by Lee on 7 May 2009, at 7:57 AM

  

  Eric,
  
  I have beat my head against a wall with this issue several times. Finding your post was like finding a pot of gold. Prefect fix - Thanks big time!

• Comment 25 written by Aaron on 8 October 2009, at 4:11 PM

  

  This just got me today! Grrr! Was restarting the app and everything trying to get this to clear. What's even more frustrating is that if you change the SQL, it now sees the new fields, but if you put the QUERYPARAM back in there, it goes back to NOT seeing the new fields and using the old cached query! Even using cfobjectcache failed here. Very very annoying, especially since the DAO component is instantiated into the Application scope.

• Comment 26 written by Brandon on 11 January 2010, at 11:44 PM

  

  CFQUERYPARAMS use prepared statements from java http://java.sun.com/docs/books/tutorial/jdbc/basic... so the db compiles the statement like a stored procedure then the query executes by just passing parameters to it. If the schema of the db changes the prepared statement does not know it since nothing signals the change. This is not really a bug but a FOL if it was fixed then you wouldn't get the performance improvements of using prepared statements. Clearing the connection pool cleans up the the prepared statements so that they are not reused thats why unchecking that option in the CF admin makes it work again.
  
  All other queries in CF without CFQUERYPARAMS are dynamically executed.

• Comment 27 written by Michael Peters on 12 August 2010, at 6:42 AM

  

  thanks for the workaround !

[Add Comment] [Subscribe to Comments]