Check query results if no data is returned

4
votes

I have a query that I am calling to update an email service. Most times it will have data in it, but in testing I came across the situation of it not returning any data because there was no data to return. In the case of no data it returns the error "Variable EDITEDACCTS is undefined".

I have tried wrapping the query in a <cftry> but it doesn't "fail" per se so it does not trip the <cfcatch>. I have also tried defining the variable

var EditedAccts = QueryNew("")

as well as simply trying

<cfif NOT isDefined(#EditedAccts#)>

and it always returns "Variable EDITEDACCTS is undefined".
I need a production ready solution to this and I'm hoping somewhere here on SO can help me out.

Thanks in advance for your help.

2
coldfusioncoldfusion-9
Do not use # signs with IsDefined(). It causes evaluation of EditedAccts which triggers the very error you are trying to prevent. Is this a basic select query? Because the query object itself should still exist even if it does not contain any records. – Leigh
.. forgot to include the correct syntax for isDefined: <cfif NOT isDefined("EditedAccts")> – Leigh
Thanks @Leigh. Removing the # signs worked. I figured it was something simple but I couldn't see the forest for the trees. – JimP
@JimP - Good. So what kind of sql statement are you running? Like I mentioned a SELECT query should always exist. Just wondering if you ran into a possible bug or are misunderstanding cfquery. – Leigh
@Leigh - It's a SELECT statement with a couple of nested queries but it was simply a misunderstanding on my part of isDefined() as a function. A perfect example of how awesome SO can be when you just aren't catching the obvious error in front of you. Thanks again for the help. – JimP

2 Answers

4
votes

I have just found the answer. You set the "result" parameter in the query call and then you can check the recordcount field returned.

<cfquery name="EditedAccts" datasource="mydatasource" result="queryResult">
    ...query goes here...
</cfquery>

When using the "result" parameter you get a struct returned with the sql used, the cached setting, the execution time and the record count. Now I can check the record count and proceed from there.

Hopefully this will help someone in the future.

0
votes

I tried using result="queryResult" but when I tried to reference the query name I got something like this error - "The value of the attribute query, which is currently EditedAccts, is invalid". Instead, I used something like IsDefined("#EditedAccts#") - including the value in quotes did the trick for me. I am only new to ColdFusion but I am learning quickly that values in quotes are entirely different to values not in quotes, in terms of how a function will interpret a parameter.