Mailing List Archive

> 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.
By default in Zope, when you call some SQL methods
during request, there is automatical commit or rollback
on your connection object at the end of request.
If there is any exception during request
rollback is called at DatabaseAdapter otherwise commit.

If it is not enough for you then you may try someting as
below, although I've not tried this with mxODBC, but this
may work. Get connection object like:

conn = context.mxODBCDatabaseConnection()

where 'mxODBCDatabaseConnection' is id of
your connection object in Zope.

execute query with:
conn.query('insert into table.... ')

finish transaction:
conn.commit()
or
conn.rollback()

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Please, stay on zope-db list :)

> Thank you for your assistance. I plugged in the new code, using the
> example you forwarded, and everything seems to work fine, without
> errors,and navigates to my results-page. The problem, however, is
> that nothing changes in the DB.
It is not possible (at last in easy way) to look into mxODBC code so I'm
not sure how it's API is... Instead of commit and rollback try to call:

_finish() - to commit
abort() or _abort() - to rollback.

It may be necessary to call these functions from external method (not
from Script (Python)) because you'll get unauthorized exception.

These are functions of Zope transaction managers
(lib/python/Shared/ZRDB/DC/TM.py). Typically (so I suppose mxODBC does
this too), call to Database Adapter object like:
mxODBCDatabaseConnection()
returns transation manager. Transaction manager holds real database
connection object and is able to call commit or rollback on it.

Have you read pdf files that are
available with mxOdbc? Maybe you'll find some clues there.

> Should "mxODBCDatabaseConnection()" be my actual DB adaptor or should
it be
> a method used to call it?
DB adapter

In fact you may also use direct python mxODBC API without any
mxODBCZopeDA features (there are examples in mxODBC docs).
Just write a python script (without zope) that does what you need and
then plug it into Zope as external method (or product).

--
Maciej Wisniowski

> Maciej,
>
> Thank you for your assistance. I plugged in the new code, using the
> example you forwarded, and everything seems to work fine, without errors,
> and navigates to my results-page. The problem, however, is that nothing
> changes in the DB.
>
> Should "mxODBCDatabaseConnection()" be my actual DB adaptor or should it be
> a method used to call it?
>
> Also, if it should be a separate method, would I want to use a Controller
> Python Script if I am trying to call it from a form?
>
>
> Thank you and Best Regards,
>
> Chris Nethery

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Am 04.01.2007, 18:47 Uhr, schrieb gilcneth@earthlink.net
<gilcneth@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
ODBC_SQL.

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."""

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db