Skip to main content
W
WebPexDev
Inspiring
March 24, 2008
Answered

Referencing field in a query struct

  • March 24, 2008
  • 1 reply
  • 1094 views
About 5% of the time I get an error 'Element COMPANYSTATUS is undefined in Q.' from the code in SNIPPET 1. Can anyone tell me why I would ever get this error? If the query returns 0 results (which it may if the user is not logged in and SESSION.User.GetCompanyID() return 0) it should return false since q.RecordCount is 0. The problem is I know users in at least some cases are logged in and the query is returning at least one row. The IF statement should short circuit on the record count if its 0. If it doesn't then the comparison for q.CompanyStatus should always be valid. Does any know why CF flakes out occasionally when referencing q.CompanyStatus? I see this in other areas as well such as in SNIPPET 2. The method is called from another CFC where I can guarantee the passed argument '_company_id' is valid integer and the SQL should always return 1 result. Why then do I get the error 'Element STATE is undefined in Q.' Is it bad practice referencing query fields this way?

Thanks In Advance

    This topic has been closed for replies.
    Correct answer -__cfSearching__-
    -==cfSearching==,
    Yes these particular CFCs are stored in the application scope as part of a code library. I actually noticed it wasn't var scoped when I was making the post. I've modified it accordingly. I found you can't just return _SqlQuery though because if the sql is not a SELECT, no return variable is defined. _SqlQuery is undefined after the query even if it is declared as a var which is kind of weird. I added a check to see it is defined and its a query and if so return it, otherwise return false. I try to stay away from using try blocks to test for variables. I'm going to put this into production and see if it solves the problem. I'll post what I find. Thanks!
    No. The cftry/cfcatch was for catching database errors. However, you may wish to let those bubble up.

    Since the component exists in a shared scope, using IsDefined() is potentially dangerous IMO. If you do not include a scope, CF automatically searches multiple scopes for the given variable name. So I would use structKeyExists instead. (Coincidentally a similar topic came up on HOF today)

    I would do something like this

    ...
    <cfset var Local = structNew()>
    <cfquery name="Local._SqlQuery" datasource="#APPLICATION.config.DSN#">
    #preserveSingleQuotes(ARGUMENTS._sql)#
    </cfquery>
    <cfif structKeyExists(Local, "_SqlQuery")>
    <cfreturn Local._SqlQuery>
    <cfelse>
    <cfreturn false>
    </cfif>
    ....

    1 reply

    N
    Newsgroup_User
    Inspiring
    March 25, 2008
    it looks to me like you are not actually running the query?
    i see no <cfquery> tag anywhere, only the sql statement as text string...

    in cf you run sql statements inside a <cfquery></cfquery> tag block (or
    initiate a stored procedure with <cfstoredproc> tag...

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    W
    WebPexDevAuthor
    Inspiring
    March 27, 2008
    All my CFCs which access the database extend a Database.cfc which has an SqlQuery() method:

    -
    -__cfSearching__-
    Inspiring
    March 27, 2008
    WebPexDev,

    Are you storing your cfc's in the application scope by any chance? Because your query variable is not var scoped, your problem may be the result of race conditions.

    <cffunction name="SqlQuery" access="public" returntype="any" output="false">
    <cfargument name="_sql" required="true" type="string">
    <cfset var _SqlQuery = "" />
    <cftry>
    <cfquery name="_SqlQuery" datasource="#APPLICATION.config.DSN#" .... >
    #preserveSingleQuotes(ARGUMENTS._sql)#
    </cfquery>
    <cfreturn _SqlQuery >

    <cfcatch type="any">
    <cfreturn false>
    </cfcatch>
    </cftry>
    </cffunction>
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices