SELECT * Schema Caching using JDBC

Posted: January 13, 2011 in ColdFusion

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s