null500 on nested custom tag

This is driving me nuts. Pretty simple, I have a little custom tag that runs through a pivot table trying to reach the bottom (categoryID=0) of a category tree by way of active (bActive=1) categories. The logic is: It starts by assuming that the tree is bad (goodcat=0). If it finds a level that is disabled (bActive bit=0), it cfexits, leaving goodcat=0. If it makes it to category 0, it flags the caller's goodcat variable as 1 and cfexits. (Each nest passing its current results to its .caller along the way) It's throwing a null 500 error and I cannot figure out why; I've commented out just about every line I can without actually preventing it from nesting and still throws the error. The pivot table is very simple with only two columns, here's the data I'm testing with: CatPivot table, present data: nCategoryID nParentCatID 289 2337 2337 0 Category table, data: (simplified) nCategoryID bActive 289 1 2337 1 Code: <cfparam name="attributes.nCategoryID" default=0> <cfparam name="nCategoryID" default="#attributes.nCategoryID#"> <cfset caller.goodcat=0> <cfquery username="#application.dblogin#" password="#application.dbpw#" name="q1" datasource="#application.datasource#">   SELECT cp.nParentCatID   FROM CatPivot cp   INNER JOIN Category c ON cp.nCategoryID = c.nCategoryID   WHERE cp.nCategoryID = #nCategoryID# AND c.bActive=1 </cfquery> <cfset goodcat=0> <cfloop query="q1">   <cfif nParentCatID LT 1>     <cfset caller.goodcat=1>     <cfexit>   </cfif>   <cf_checkpartcat nCategoryID="#nParentCatID#">   <cfset Caller.goodcat=goodcat>   <cfif goodcat GT 0>     <cfexit>   </cfif> </cfloop> ..And if this can all be done in a single SQL query, by all means please enlighten me :)

You should try a variable called lolcat.  :) Seriously though, it sounds like an endless loop to me.  If you check your server's "coldfusion-out" log you will probably see an out of memory exception. Put in some external logging to a text file or something to follow what is happening to your variables.  Or use a counter in session to keep yourself from recursing more than 10 times so you can output debugging info in your page. Without really digging in to your code I can't really say where the problem is.  It sounds like your issue is pretty straight forward-- you have a series of categories which are arranged in a hierarchy via your pivot table. Given a leaf node of your tree, you wish to climb until you reach the root node.  I guess you're trying to make sure that none of your categories are orphaned with no active parents? Honestly, it might be a little simpler to write this using iteration, but don't let that keep you from recursive tags if that's what you like. As to whether or not you can do it in a single SQL statement-- maybe.  If you are on SQL Server 2005 or later CTE's (common table expressions) allow recursion but I don't know that it would be any simpler.  If your hierarchy of categories always has a known depth, you can just keep joining to your pivot table that many times.  Other than that, you are out of luck using an adjacency list model.  Nested sets are much better for calculating ancestors and descendants. One question: if your query returns no records, won't the line "<cfif nParentCatID LT 1>" error trying to convert "" to a number? ~Brad > This is driving me nuts. Pretty simple, I have a little custom tag that > runs through a pivot table trying to reach the bottom (categoryID=0) of a > category tree by way of active (bActive=1) categories. The logic is:

Lolcat :P Good thought about logging to an external file. On SQL 2k. The depth is unknown, it is usually 2-6 levels but theoretically could go way down. A fixed DB structure would make this a million times easier but also be virtually worthless for the application. And no, the "<cfif nParentCatID LT 1>" shouldn't error out because it's with a loop over the query its referencing, so if it returns no records it's never run and just returns but up to the last nesting. You should try a variable called lolcat.  :) Seriously though, it sounds like an endless loop to me.  If you check your server's "coldfusion-out" log you will probably see an out of memory exception. Put in some external logging to a text file or something to follow what is happening to your variables.  Or use a counter in session to keep yourself from recursing more than 10 times so you can output debugging info in your page. Without really digging in to your code I can't really say where the problem is.  It sounds like your issue is pretty straight forward-- you have a series of categories which are arranged in a hierarchy via your pivot table. Given a leaf node of your tree, you wish to climb until you reach the root node.  I guess you're trying to make sure that none of your categories are orphaned with no active parents? Honestly, it might be a little simpler to write this using iteration, but don't let that keep you from recursive tags if that's what you like. As to whether or not you can do it in a single SQL statement-- maybe.  If you are on SQL Server 2005 or later CTE's (common table expressions) allow recursion but I don't know that it would be any simpler.  If your hierarchy of categories always has a known depth, you can just keep joining to your pivot table that many times.  Other than that, you are out of luck using an adjacency list model.  Nested sets are much better for calculating ancestors and descendants. One question: if your query returns no records, won't the line "<cfif nParentCatID LT 1>" error trying to convert "" to a number? ~Brad > This is driving me nuts. Pretty simple, I have a little custom tag that > runs through a pivot table trying to reach the bottom (categoryID=0) of a > category tree by way of active (bActive=1) categories. The logic is: