This content has been marked as final. Show 6 replies
This is a known issue when using SELECT *. Somewhere along the way the CF
or the JDBC driver is caching the column list it expects to get back from
the DB, and it gets confused when the DB returns a different column list,
despite the fact the SQL statement is the same.
The solution is to not use SELECT *, which is generally considered poor
Do you actually mean to get ALL the columns back for that table, or are you
using SELECT * as a short cut to not have to think about which columns you
might want? It's usually the latter.
You should also put the two dates into <cfqueryparam> tags too. And
possibly use the BETWEEN operator, rather than the >= & <= ones.
Try using quotes
AND calID = '<cfqueryparam value="#calID#" cfsqltype="cf_sql_integer">'
and/or also check the condition wether #calID# is blank or not.
Which line is Number 8
04:45:06.006 - Database Exception - in /data/............/qry_detail.cfm : line 8
Error Executing Database Query.
What is the datatype of the calID field in the database? Are you sure that it is type int? Does it work any differently if you use CF_SQL_NUMERIC?
Thanks Adam. In this case, I needed 23 of the 24 fields so I was using select *. But when I went back and listed each individually that solved the problem. I'm going to fix the date variables as well. Thanks again
I agree that using SELECT * is usually a poor practice, often the result of being lazy. But not always. Sometimes, I use SELECT * to get the ColumnList and work with the fields, and there are other scenarios.
Nonetheless, saying "Don't use SELECT *" is a bit like the "Doc it hurts when I do this" "Don't do that". WHat do I do if it hurts when I breathe?
What can one do to resolve or avoid the problem? I manage a number of external applications, and some have a lot of inherited code with SELECT *. I can't just go fix all of the instances of SELECT *. So what can I do when I have to upload table design changes? Stop one or more of the Cold Fusion services? Stop the database (SQL Server, in my case)?
I've learned that you can usually "fix" the problem by simply changing the query. Add a space, add a "AND 1 = 1", hit ENTER to move a cfqueryaram to a new line, stuff like that. But that is not a fix.
I would like to know if the problem is with the JDBC driver, or Cold Fusion.
Nothing to do with the actual question, but the query may have a logic problem with the dates. Or it may not.