[Zope] Using recursion with a SQL method

[Jim Cain]

All,

I have a MySQL table "category" with the following schema:

id         number
name       char
parent_id  number

The table is self-referencing in that the parent_id points back to an
ID. This structure renders a hierarchical listing of categories, with the
root categories having a parent_id of -1.

I have a SQL method to select category names and IDs by their parent ID:

ID:        Query_category_by_parid
Arguments: parent_id:required:int
Query:     select id, name from category
           where <dtml-sqltest parent_id type=int>
           order by name

I have a DTML method which calls itself recursively to find a list of
categories starting with the specified parent:

ID: list_categories

<dtml-in "Query_category_by_parid(parent_id=parent_id)">
  <p><dtml-var name> (<dtml-var id>)</p>
  <dtml-var "list_categories(parent_id=id)">
</dtml-in>

This however, doesn't work. It appears that the second call to
Query_category_by_parid, within the second call to list_categories, fails.

Here is the URL: /list_categories?parent_id=-1

This is the error I receive:

  Error Type: NameError
  Error Value: Query_category_by_parid 

And here is the traceback:

Traceback (innermost last):
  File /usr/local/src/Zope-2.0.1/lib/python/ZPublisher/Publish.py, line
214, in publish_module
  File /usr/local/src/Zope-2.0.1/lib/python/ZPublisher/Publish.py, line
179, in publish
  File /usr/local/src/Zope-2.0.1/lib/python/Zope/__init__.py, line 201, in
zpublisher_exception_hook
    (Object: ElementWithAttributes)
  File /usr/local/src/Zope-2.0.1/lib/python/ZPublisher/Publish.py, line
165, in publish
  File /usr/local/src/Zope-2.0.1/lib/python/ZPublisher/mapply.py, line
160, in mapply
    (Object: list_categories)
  File /usr/local/src/Zope-2.0.1/lib/python/ZPublisher/Publish.py, line
102, in call_object
    (Object: list_categories)
  File /usr/local/src/Zope-2.0.1/lib/python/OFS/DTMLMethod.py, line 145,
in __call__
    (Object: list_categories)
  File /usr/local/src/Zope-2.0.1/lib/python/DocumentTemplate/DT_String.py,
line 502, in __call__
    (Object: list_categories)
  File /usr/local/src/Zope-2.0.1/lib/python/DocumentTemplate/DT_In.py,
line 691, in renderwob
    (Object: Query_category_by_coid_parid(parent_id=parent_id))
  File /usr/local/src/Zope-2.0.1/lib/python/DocumentTemplate/DT_Util.py,
line 321, in eval
    (Object: list_categories(parent_id=id))
  File &lt;string&gt;, line 0, in ?
  File /usr/local/src/Zope-2.0.1/lib/python/OFS/DTMLMethod.py, line 141,
in __call__
    (Object: list_categories)
  File /usr/local/src/Zope-2.0.1/lib/python/DocumentTemplate/DT_String.py,
line 502, in __call__
    (Object: list_categories)
  File /usr/local/src/Zope-2.0.1/lib/python/DocumentTemplate/DT_In.py,
line 633, in renderwob
    (Object: Query_category_by_coid_parid(parent_id=parent_id))
  File /usr/local/src/Zope-2.0.1/lib/python/DocumentTemplate/DT_Util.py,
line 321, in eval
    (Object: Query_category_by_coid_parid(parent_id=parent_id))
  File &lt;string&gt;, line 0, in ?
NameError: (see above)


I have a feeling I'm missing something obvious, but I can't find it. Any
help would be greatly appreciated.

Cheers,
Jim