[Zope-DB] Basic tsql example using MxODBC Zope DA adaptor?

Charlie Clark charlie at egenix.com
Sun Jan 7 11:45:02 EST 2007

Am 04.01.2007, 18:47 Uhr, schrieb gilcneth at earthlink.net  
<gilcneth at earthlink.net>:

> Hello Everyone,
> I am relatively new to Zope/Plone, as well as the MxODBC Zope DA  
> adaptor.  I am wondering if someone could point me to a basic example  
> that would illustrate how to use the MxODBC Zope DA adaptor outside of a  
> ZSQL method, using the Python API.  I am using a script to build  
> transact-sql statements, and it doesn't appear to be possible to pass  
> t-sql into a ZSQL method.

You can use the mxODBC ZopeDA to pass in all kinds of SQL, ie. if you wish  
to generate your SQL dynamically or to make use of bound parameters. This  
is done using an ExternalMethod as detailed below. However, this will  
still be within Zope's transacational management and is, thus, not  
suitable if you wish to manage transactions yourself. If you wish to do  
this then you should first of all read up on Zope's transactional  
management to see how to use this. If you wish to use this for something  
like a batch function then you are probably better of without using Zope  
at all.

ZSQL and ZopeDAs are severely limited and contain antiquated code.
This makes it particularly difficult to generate dynamic SQL as often
required. It also means that parameters get quoted by the DA which can
be messy. An alternative is to use ExternalMethods to call the
.execute() method of an mxODBCZopeDA and pass it statements
and parameters.

Create a file in ~/Extensions, say ODBC_SQL.py

This is essentially needs only one function:

 from Shared.DC.ZRDB import Results

def callSQL(connObj, SQL="SELECT value FROM content"):
     results = connObj.execute(SQL)
     return Results.Results(results)

Create a Zope External Method say SQL which points to callSQL in

You can then call this method from any PythonScript and simply pass it
the connection and statement you want to execute.

In your case the PythonScript needs only to be modified slightly,
depending on what you want to.

statement = "select [Building ID] from [Buildings] "\
             "where [Building Name] = ?"
results = context.SQL(mymxODBCZopeDA(), statement)

return results

The results returned are the same as would be returned by a ZSQL-method."""


More information about the Zope-DB mailing list