hans wrote:
well, i could instantiate a bunch of zsql methods, each with its own query and then call them from my products methods. but how could i obviate the need to have these zsql methods and query directly? Or is it just not worth it?
This is what I've done. However, it's a hack in the sense I'm relying on the current SQL() class implementation and am using Zope-2.3.3. Still, I've been quite happy with the results and does what you've been looking for. This may have to be cleaned up a little for later versions and would be curious of any updates. Hopefully one of the Zope developers will make something like this part of the Zope API. Enjoy, Albert ---------------------------------------------------------------------- # -*- mode: python; -*- import Globals import OFS import StringIO from Products.ZSQLMethods.SQL import SQL # given a connection_id string, the ZSQL template, variable list, and # definitions, execute the ZSQL statement. Return a ZSQL Result object. # Example: # # expresion = '''SELECT FirstName,LastName FROM Users # WHERE Users.Email=<dtml-sqlvar email type="string">''' # result = query(self,expression,"email",email="foo@bar.com") # # This does not store a ZSQL object in the ZODB, so that ZODB remains # unchange. IMHO, this provides the most flexibility, allowing us to # create an arbitrary number of ZSQL statements from python code, # without being tied down to the traditional point&click editing. def query(self,connection_id,template,vars="",**keywords): id = "__custom_query" sql = SQL(id, id, connection_id, vars, template) # normally if we create a ZSQL method in zope (or add sql instance # to self object), the sql instance would hold the ZSQL DA object. # So we need to manually set it. # # note that we don't store the sql object into the zope object, as # the transaction gets stored in the Data.fs. Even deleting later # makes it worse, as the "undo" records gets updated setattr(sql,sql.connection_id, getattr(self,sql.connection_id)) if keywords.has_key(MAX_ROWS): # DA parses max_rows and saves it in max_rows_ sql.max_rows=sql.max_rows_=keywords[MAX_ROWS] else: sql.max_rows=sql.max_rows_=1000 results = sql(keywords) return results