Missing column in query results CF9

[vectorpj]

I've got a very simple table in mssql 2005 consisting of these columns:

id (pk int),

app_id (int),

name (varchar50),

dir_key (varchar50),

path (varchar100),

size_limit (smallint),

ext_limit (varchar50),

virtual_dir (varchar50)

My query, run via cfquery or as a query object in script:

SELECT * FROM dbo.tblAppUploadSettings WHERE app_id = 1

Now, when that query runs, column virtual_dir is missing in the result set. It is present when running the same query via sql management studio.

If I explicitly add virtual_dir to the select list ( *,virtual_dir ) then I get the column returned twice.

I've made sure to clear query cache, etc. but to no avail.

Any thoughts?

[cfjedimaster]

I seem to remember seeing this before where it was a cache on the DB side, not CF. Try restarting MSSQL real quick. Shoot - CF even. Can't hurt.

[ilssac · Accepted solution]

vectorpj wrote:

I've made sure to clear query cache, etc. but to no avail.

Which 'query cache' did you clear?

vectorpj wrote:

Any thoughts?

DO NOT USE

SELECT *

When you do, the database driver will cache the table structure the first time it is executed, and from then on it will not see any changes made to the table in the database, until such a time as this 'cache' is cleared.  And the only way, AFAIK, to do this is to restart services and|or servers.  This can lead to a whole host of hard to debug issues, such as the one you are experiencing.

[vectorpj]

I think you're on to something with the db side cache. I just ran the query with all columns explicitly named. Now running it with SELECT * it shows the correct structure. So apparently that reset the cache. Thanks for the asist.

[ilssac]

Ah, yes, changing the structure of the SQL statement is also a way to get a fresh version of the database schema.

vectorpj wrote:

I just ran the query with all columns explicitly named.

That is considered a best practice for several reasons, including this table structure caching issue.