[Zope-DB] Atomic ZSQL in Zope 2.7.5

[Maslak, Michael]

For some reason I thought ZSQL method calls were atomic, but they appear
otherwise.

 

Using an eGenix mxODBC Database Connection at
/Database/PoPy_database_connection to a SQL Server 2000 back end, I have
created a Python script to write SQL commands and feed them in one large
string:

 

UPDATE equip SET workstation=1 WHERE eq_id=39315; 

 

UPDATE node SET node='M28057' WHERE node_id=13451; 

 

UPDATE materiel SET mat_nm='mm-M28057-H-01', owner='mmaslak',
datetime='2006/04/24 15:09:07.611 GMT-5' WHERE mat_id=39315;

 

This all gets sent as one string to a ZSQL method where the first two
commands execute, but the third fails because it doesn't like the
datetime string, which indeed fails when I try it alone in the PoPy
connection.  Why, then doesn't the whole thing fail like I might expect?

 

When I wrap the call in a 'begin transaction ... commit', it becomes
atomic. Nothing changes in the database if part of it fails. Great.  

 

But failure is not detectable by the Python script. I have used
try/except and if/else blocks to ascertain failure. It thinks a
non-commit is AOK, not an exception.  Is this function of the eGenix
ODBC? Of MS SQL Server 2K? Zope/ZSQL/Python?

 

How can I detect failure or success in the Python script and still
maintain atomicity? Do I need to program by contract?

 

 

Michael Maslak, Jr.

Associate Software Engineer

Anteon Corporation

294 Thames Ave

Bay St. Louis, MS  39520

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/zope-db/attachments/20060425/fcf9d3ed/attachment.htm