If you use JDBC drivers for your ODBC source in ColdFusion, you may notice that you don’t return a new column entered into your database table using SELECT *. You may also get an error such as “Syntax error converting the varchar value ‘billboard’ to a column of data type int”.
I’ve known about this for awhile from Mark Kruger, but other people have asked me about this nuance.
Basically when you use SELECT * in your query, it caches the database schema.
<cfquery name="q1" datasource="#application.DSN#"> SELECT * FROM TABLE1 WHERE COLA = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="A"> </cfquery>
Three things can fix this:
- Update your code to include the specific column names, such as “SELECT COLA, COLB”. This will also increase performance since SQL doesn’t have to lookup the table columns.
- Change your query somehow, even adding a space
- Restart the ColdFusion Service. This will flush out the schema cache.