[Zope-DB] Using dynamic ZSQL objects

[TOM HAVILAND]

I'm having a problem getting results from a dynamically created SQL object.  I'd appreciate it if someone out there can tell me what I'm doing wrong.

What I'm trying to do is to run the same query on several databases that use the same schema (actually they're Microsoft Project files).  I create (by hand) a ZODBCA connector object for each database file.  I can then create a ZSQL query (by hand) and use it without any problem.

However I'm having a problem taking it to the next level.  I've written an external method, as follows:

from Products.ZSQLMethods import SQL

def TestQuery(dbconn):
   
  query = SQL.SQL("id","title", dbconn, "", "select * from msp_projects")
  return query

The idea is that I'll select a database connection and then call this function passing in the one that was selected.

So I try it out with an ZPT like:

<span tal:repeat="name python:container.exttest(container.IR_DB)()">
<h1><span tal:replace="name/proj_name"></span>:</h1>
Engineer: <span tal:replace="name/proj_prop_manager"/><br/> 
Start Date: <span tal:replace="name/proj_info_start_date"/><br/>
End Date: <span tal:replace="name/proj_info_finish_date"/><br/>
</span>

Note that IR_DB is the id of a ZODBCA connection in the same folder as the ZPT (and the external method).  This connection and page template work fine if I subsitute the python expression in the tal:repeat line with a manually created ZSQL object.

In any case when I try to render the ZPT, I get an error: the database connection cannot be found

I've hunted around the usual places, but can't seem to find anything on this.

Any thoughts?

Thanks

Tom Haviland