[Zope-DB] Basic tsql example using MxODBC Zope DA adaptor?
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
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
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)
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)
The results returned are the same as would be returned by a ZSQL-method."""
More information about the Zope-DB