SELECT * Schema Caching using JDBC

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.

Example Code:

<cfquery name="q1" datasource="#application.DSN#">
SELECT * FROM TABLE1
WHERE COLA = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="A">
</cfquery>

Three things can fix this:

  1. 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.
  2. Change your query somehow, even adding a space
  3. Restart the ColdFusion Service. This will flush out the schema cache.
Advertisements