ColdFusion Query Error: Value Can Not Be Converted To Requested Type
Posted August 4, 2006 at 2:40 PM by Ben Nadel
My friend Si was getting this error on one of our sites:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested type.
This error goes hand in hand with the error I have blogged about before "Error Occurred While Processing Request N >= N" and is, in fact, caused by the same thing. The problem is inevitably SQL SELECT statements that use (*) such as:
- SELECT
- *
- FROM
- [table]
I am not 100% sure why this takes place, but here's my version: ColdFusion caches the SQL statement (or SQL does the caching). Then you go and you change the database structure such as adding a column. Then you run the query again. Since the actually SQL string didn't change, SQL thinks nothing has changed and tries to run the same query. However, since a column was added to the database, the cached structure not longer maps properly to the database structure, which is why it thinks it has to convert data types.
THIS IS WHY SELECT * IS NOOOO GOOD.
I can't remember off hand, but I think this is made even worse if you use CFQueryParam and SELECT *. If you spell out your query column instead of using (*), you are alllll good.
Reader Comments
I get this error occasionally and I use * in my select statements because the SQL server selects fields and does all the joins for me. I also use cfqueryparam and it seems to have a problem with it. if I remove the cfqueryparam and just put #Arguments.value# it works, but of course that's no good. your post helped me figure out what was going wrong. It seems like if I restart the coldfusion application service, the problem goes away and in a development environment, that's ok.
I know this is an old post, but I just wanted to say that this post has saved my life. i was tearing my hair out with this error
Always glad to help save some time (and hair) :)
To circumvent that CF caches select * queries our DBmanager came up with a clever trick.
Instead of writing "select * from tblcustomer", then type "select c.* from tblcustomer as c"
CF is stupid enough to not understand c.* so it dosen't cache it.
However SQL server is smart enough to understand that * and c.* is identical statements and therefore SQL server will cache it (caching it the way it should be cached i might add)
That way you dont mess up with SQL servers caching (which is a good thing)
@Johansen,
Cool tip. When I see people get this error, I try to advocate them writing out the explicit column requests, but this might prove to be the quicker fix.
Fantastic Tip - popped up in Google as link number 2 when searching for "Value Can Not Be Converted To Requested Type" and was exactly what what was wrong... I can't even begin to think how long it would have taken me to figure this out on my own. I really hate going into the lowdown nitty gritty of how CF, JDBC and MSSQL interact and as a fellow lazy programmer this tip solved my problem in 5min!
Respect!
Glad to have helped :)
Wow. Thank you very much. This post probably saved me 3 hours worth of debugging
I was getting this error when using cfqueryparam to pass a value to a stored proc; restarting the coldfusion application service solved my problem.
@Carole,
That will work since it will flush the cached database structure. Are you using SELECT * in your queries?
Thanks for blogging about this error Ben. It seems like there are all sorts of database caching issues caused by cfqueryparam.
I was only able to resolve this issue by a restart of the CF service after changing the db structure. It's interesting to note that I'm not using * in my SELECT query where CF throws an error, yet it still seems to cache the structure.
Great answer! I have one client using Fusebox so my error messages were obscure at best. I would have AN error after a db change, and figured it was some kind of caching. The only fix I could come up with was to restart ColdFusion service (not great, I know). So I got the same error on another app (no Fusebox) and could see the actual error message. I started to replace the * based on your recommendation, then had an idea. I'm still using ColdFusion studio, so I refreshed the database in the db tab and it fixed the error. Still, I agree that the * is not great.
Thanks!
The error just came back. I guess I was wrong about the CF Studio fix.
@Cheryl,
You're saying you're still getting the error on non-select-* queries?
No. I'm saying that I could swear the error went away when I refreshed the datasource through CFStudio. Then I posted my comment, then I went back and refreshed the page and the error returned. So I went in and corrected the query and the error went away for good. I really appreciate the insight on this.
@Cheryl,
Ah, ok. As long as it's gone now :)
Yup. Your post saved me time!
Everything was working fine then WHAT Happened??? Lucky for me I didn't change too much (except for that deleted column).
Narrowed down my problem to the CFQUERYPARAM usage and then googled the error.
Thank you!
@Stephanie,
Glad to help :)
Replacing the "select *" with the column names worked like a charm. Thanks!
@Sharon,
Awesome!
Three questions about this issue:
1. Does this error occur when you query a CF query object or only when you query a DB table?
2. I assume this error occurs when you query views as well as tables.
3. Is this problem solely the byproduct of a "Select *" (i.e., if you explicitly reference columns in the select statement, no problem).
The reason I ask about item 3 is that Craig McDonald said in his post above that he wasn't using "Select *" and was still experiencing this problem.
BTW, this is my first posting here but I want to join others in thanking Ben for hosting this great discussion, as well as others on his site.
@Mike,
The problem comes from the query caching that ColdFusion is performing. I assume that this happens if you hit a database table OR a view.
As far as I have personally experienced, as long as you are not using "select *", then you should not experience this error as ColdFusion will not try to execute a cached structure that does NOT match your given query.
Doing a select c.* totally fixed this issue for me! Thanks
The aliasing (e.g., "select c.* from tblcustomer as c") has also worked in my case.
While I don't like to do a "select *", I have one table with 150 fields (a related view of that table has even more) and I use all of them in one of my apps, so listing all the individual fields in the query would be a pain and really futz up the code.
Thanks again everyone.
@Roman, @Mike,
Interesting; I wonder why aliasing the table makes a difference.
@Mike,
150 columns! Bananas :)
@Mike,
I resisted the long select statements too, but finally I started using CFCs so it only has to be done once (if queries are written flexibly). <cfinvoke>s are nice and clean in your code. I'm glad I made the switch.
Using ColdFusion Builder, double clicking on the field name places it at the cursor. That combined with a ", c." on the clip board and you've got yourself a proper query in no-time. : )
An old post which won't die! :-) Obviously still an irritation to many of us.
Here are my observations:
1) This effects all (inc. non-cached) cfquery's.
2) Replacing the * with column names works.
3) Aliasing the * (e.g. a.*) only works if it was not previously aliased (for me). So if you already had a.* in the query, then deleted a column in table a, the aliasing doesn't protect you from seeing this error.
4) Late breaking news - rebooting the ColdFusion server seems to clear the problem!
My thinking is that table structures used by cfquery are cached somehow. Make a change on the SQL side, the query may fail. Make a change in the cfquery, it is then re-cached, so the query works again. Rebooting of course also clears the cache. Just a hypothesis!
Hope this helps - keep up the good work, Ben!
You can also clear the cache by ticking "Disable Connections" under the data source's Advance Settings in CF admin. Save. Then untick. Then save again.
@Tri,
I've definitely used that a few times, especially before I realized that "select *" was the culprit.
Does anybody know why this error is appearing for us now when it never did in the past? Are we caching queries when we shouldn't be? Is this something new to CF9?
Commenting on Oxide said...
I had the following select statement that was still throwing this error. So, I don't think that is a definite fix.
SELECT UI.salutation, UI.firstname, UI.lastname, UI.email, UI.nickname, UI.photo, UA.*, S.state_abbr,
@Josh,
When in doubt, remove the ".*". Pretty much the only place I would ever use * in a SELECT statement is inside a query-of-queries.
As far as why this is happening now and not before, there might be a change in the underlying caching algorithms. Not sure.
Thanks for this post, very helpful.
I was looking for a way to flush this DB/query cache. It looks like the way to do it is 'suspend all client connections' on the datasource.
Then re-run your query, then re-enable connections. Maybe.
Just ran into this issue with some legacy code. Thanks for getting this out there for us, Ben.
Thanks for the post, it was helpful.
I would like to point the fact that a * in the query as nothing to do with the problem itself. I had a query with no * in it and had this problem. Cf version was 9,0,0,251028. Changed an int field to a varchar(255).
I hit this on CF9, but I am explicitly naming all the columns instead of doing a select *.
We are using SQL Server and have a lot of our columns as bit flags. I changed one of the views to do a case statement to translate these to Yes/No on the request of a client who didn't like seeing the 1/0.
Since the datatype is now a string and not a bit, it must not have matched the structure and would throw an error.
Thanks, saved me yet again.
Just received this same error but with named columns. The problem was an empty space ' ' after the last column (ex: desc as description' '). Took a few minutes to spot that. Might save somebody some troubles.
If you get this error and add a space to the query that does the (select *) the error will go away. If you undo you space there error will come back. Until you restart the server. Something figured out the hard way. I found this post after alot of debugging. Ugh....
I've had some issues using select * ... and no longer do it ...
select t1.* seems to work for me though ...
