ColdFusion CFQuery Zero Records and the “Element x is undefined in x” Error

Posted: October 23, 2012 in ColdFusion
Tags: , , , , ,

When you run a CFQuery tag in ColdFusion you “will always” get a query object returned whether or not any results where found. But recently, after running a complicated query in ColdFusion 9.0.1, I found this to not be true. When there where results, everything worked as expected. However, when there where no results then I received a “Element x is undefined in x.” error when calling the CFQuery’s name variable. Here’s a simplified example of my code:

<cfquery name="local.products">
SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( PC.price ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

After banging my head and doing some research I ran through this thread that helped me resolve the issue: http://forums.adobe.com/message/2595679 . They mention a couple of bug ID’s, but Adobe updated their bug-base and I can’t find them. So at some point I’ll create a new ticket for this issue.

The problem lies in the fact that my SQL was generating a warning message inside the joined sub-query and CF wasn’t handling that very well. One fix is to fix why this message is being generated in the first place by adding a ISNULL() or COALESCE() around the column the MIN() method is reading. For example:

<cfquery name="local.products">
SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( COALESCE( PC.price, 0 ) ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

The second method would be to add “SET ANSI_WARNINGS OFF;” to the beginning of the SQL statement. For example:

<cfquery name="local.products">
SET ANSI_WARNINGS OFF;

SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( PC.price, 0 ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

The first fix is probably the best method to choose, however both of these resolved my issue.

There are other workarounds such as using the JDBC driver, but this approach seems to be the most practical.

Advertisements
Comments
  1. Gary F says:

    I find a similar problem when using Stored Procedures. My workaround is to ensure the query is always followed with to ensure that CF has actually created a query object. It’s annoying especially since isdefined() is frowned upon as being inefficient.

  2. Phil says:

    Thanks for this – very useful. Had this same problem a couple of times this week (strangely – as never had it before) and didn’t really get much joy from livedocs. Previously, I fiddled with the query until it wasnt a problem, but this helps shed light on why its happening in the first place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s