CODEWITHSUNDEEP
sqlsql-server-2008coldfusion
KeithComito
KeithComito

Reputation: 1387

Select * not returning all columns - Coldfusion / SQL Server 2008

I am getting some strange behavior involving database queries that I have never seen before and I'm hoping you can illuminate the issue.

I have a table of data called myTable with some columns; thus far everything involving it has been fine. Now, I've just added a column called subTitle; and II notice that the SELECT * Query that pulls in the data for a given record is not aware of that column (it says the returned query does not have a subTitle column), but if I explicitly name the column (select subTitle) it is. I thought perhaps that the Coldfusion server might be caching the query so I tried to work around with cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#" but no dice.

Consider the below code:

<cfquery name="getSub" datasource="#Application.datasourceName#">
     SELECT   subTitle 
     FROM     myTable
     WHERE    RecordID = '674'
 </cfquery> 

 <cfoutput>#getSub.subTitle#</cfoutput>

 <cfquery name="getInfo" datasource="#Application.datasourceName#">
     SELECT   * 
     FROM     myTable
     WHERE    RecordID = '674'
 </cfquery>  

 <cfoutput>#getInfo.subTitle#</cfoutput>

Keeping in mind that record 674 has the string "test" in it's subTitle column the about of the above is

test

[[CRASH WITH ERROR]]

This doesn't make sense to me unless SQL Server 2008 has somehow cached the SELECT * query with the previous incarnation of the table, but the strange thing is if I run the query right from within SQL Management Studio there are no problems and it shows all columns with the select *

Frankly this one has me baffled; I know I can get around this by explicitly naming all the desired columns in the select query instead of using * (which is best practice anyway), but I want to understand why this is occurring.

I've worked with SQL Server 2005 for many years and never had something like this happen, which leads me to believe it might involve something new in SQL Server 2008; but then again the fact that the query works fine inside of the management studio doesn't jive with that either.

===UPDATE===

Clearing the Template Cache in the CF admin will solve the issue

Upvotes: 3

Views: 2110

Answers (2)

Tomalak
Tomalak

Reputation: 338228

Yes, ColdFusion caches the <cfquery> SQL string. If the underlying table structure changes, the result might be an exception like you see it.

Work-arounds:

  • Recommended solutiuons:
    • If you have the development or enterprise version you can view your query cache in the server moniter and clear only the queries there. (comment from @Dpolehonski, thanks)
    • Otherwise, click Clear Template Cache Now in the ColdFusion Administrator (under Server Settings/Caching).
      This will invalidate all cached CFML-Templates on the server and CF will re-compile them when necessary.
  • Quick and dirty:
    Subtly change the query SQL, for example add a space somewhere. When you are on a development machine it's the quickest way to fix the issue.
    This will invalidate the compiled version of this query only and force a re-compile.
    (Note that removing the subtle change will trigger the error again since the old query text will remain cached.)
  • Brute-force:
    Re-start the ColdFusion server. Brutal, but effective.

Upvotes: 8

John
John

Reputation: 223

Or the quick and super dirty method:

<cfquery name="getInfo" datasource="#Application.datasourceName#">
   SELECT 
     *, #createUUID()# as starQueryCacheFix
   FROM 
     myTable
   WHERE 
   RecordID = '674'
 </cfquery>

Don't leave in production code though... it'll obsolete all of the query caching ColdFusion does. I did say it was super dirty ;)

Upvotes: 2

Related Questions