Skip to main content
V
vectorpj
Participant
May 10, 2011
Answered

Missing column in query results CF9

  • May 10, 2011
  • 2 replies
  • 654 views

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?

    This topic has been closed for replies.
    Correct answer ilssac

    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.

    2 replies

    ilssac
    ilssacCorrect answer
    Inspiring
    May 10, 2011

    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.

    V
    vectorpjAuthor
    Participant
    May 10, 2011

    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
    ilssac
    Inspiring
    May 10, 2011

    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.

    cfjedimaster
    cfjedimaster
    Inspiring
    May 10, 2011

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices