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.

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.

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

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

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)?

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.

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.

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

Thanks for the entry!

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....

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.

same over here. It's getting really annoying.

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

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.

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 -

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!

No worries! Glad this post helped.

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.

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.

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.

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

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.

Nice Eric!

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

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!

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.

CFQUERYPARAMS use prepared statements from java http://java.sun.com/docs/bo... 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.

thanks for the workaround !

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

Wow, March 2004. Was there an internet back then?

Don't you remember all those tubes?