Referencing field in a query struct

[WebPexDev]

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

[Newsgroup_User]

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/

[WebPexDev]

All my CFCs which access the database extend a Database.cfc which has an SqlQuery() method:

[-__cfSearching__-]

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>

[WebPexDev]

-==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!

[-__cfSearching__- · Accepted solution]

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>
....

[-__cfSearching__-]

-==cfSearching==- wrote:
> I would do something like this ..

.. If I used dynamic queries like that. I do not ;-)

[WebPexDev]

Okay I'll try that. I see your point about using IsDefined() with out being scoped though. That never occurred to me.
I'm sure there are a few places where its being used like that I'm going to go fix. I'll put this into production and see if it helps and post back.

Thanks!

[WebPexDev]

Var scoping that main SqlQuery() function seems to have solved the problem. Haven't seen an error in more that 36 hours, whereas before it was about 1-2 hour. Thanks for all the help!

[-__cfSearching__-]

WebPexDev,

You are welcome, and thanks for posting the outcome :-)