Odd Issue w/ CFQueryParam

03-24-2004 8,012 views ColdFusion 30 Comments

So, I ran into an interesting bug yesterday. Consider the following query:

<cfquery name="qObjectGet"
datasource="#attributes.datasource#">
   SELECT    *
   FROM    #attributes.type#
   WHERE   id = <cfqueryparam
cfsqltype="CF_SQL_INTEGER" value="#attributes.id#" list="No" null="No">
</cfquery>

This was running fine, but all of a sudden began throwing the following error:

[Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.

I did a quick google search, but couldn't find anything clearly related - except for one post that suggested simply modifying the query by adding a semicolon. I did and the error went away.

It turns out that this error would return everytime I modified the table. I either had to modify the query or restart CF.

Apparently there is some caching involved of the table schema when using queryparam, and the change to the table schema confused CF.

However, it is not reproduceable. I tried to reproduce it again this morning and was not able to. My coworkers at Mindseye have also seen this and have not been able to reproduce it at will.

So - something to watch out for!

30 Comments

Bryan F. Hogan #

Commented on 03-24-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.

dave ross #

Commented on 03-24-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.

Eric Jones #

Commented on 03-24-2004 at 8:13 AM
Hey ray, your link to mindseye isn't working is that site down?

Raymond Camden #

Commented on 03-24-2004 at 8:21 AM
Yep, power outage at the office. Luckily I'm remote.

Doug Keen #

Commented on 03-24-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)?

Daniel D #

Commented on 03-24-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.

Doug #

Commented on 03-29-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.

Peter Windemuller #

Commented on 04-09-2004 at 7:53 AM
Had the same experience today, because I changed a database type from tinyint to varchar;

Thanks for the entry!

Chris Tsongas #

Commented on 04-12-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....

Dave Cordes #

Commented on 05-31-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.

Rob #

Commented on 10-04-2005 at 1:53 PM
same over here. It's getting really annoying.

Damen #

Commented on 10-06-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)"

Daryl Banttari #

Commented on 12-07-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.

Haris Kusumo #

Commented on 09-16-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 -

Snack #

Commented on 09-27-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!

Raymond Camden #

Commented on 09-28-2006 at 8:51 AM
No worries! Glad this post helped.

Jason Weible #

Commented on 12-14-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.

Michelle Kinsey Bruns #

Commented on 01-10-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.

Kamil Saiyed #

Commented on 09-18-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.

Mike Haggerty #

Commented on 12-18-2007 at 10:09 AM
I had the same issue today when changing a dbtype from int to varchar. Thanks for the entry!

Eric #

Commented on 03-13-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.

Raymond Camden #

Commented on 03-13-2008 at 8:12 AM
Nice Eric!

Alexander Sante #

Commented on 01-15-2009 at 8:24 AM
Thanks guys, this solved my problem as well. Great Post!

Lee #

Commented on 05-07-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!

Aaron #

Commented on 10-08-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.

Brandon #

Commented on 01-11-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.

Michael Peters #

Commented on 08-12-2010 at 6:42 AM
thanks for the workaround !

Tony Weeg #

Commented on 12-02-2010 at 1:58 PM
ray :) again you help... i kinda knew this, but i wanted to make sure... instead of hitting you up on gtalk, i tried here first!

thanks bro