[Zope-DB] Transactional problem with eGenix mxODBC Zope DA 1.0.6 and SQL Server 2000

[Nicolas Lehuen]

Hi,

We're running Zope 2.6.2 (with the embedded Python 2.1.3) on Windows 2000 and connect to our SQL Server 2000 server through ODBC. All server and driver versions are the latest available. We've bought the Zope DA version 1.0.6 after a 3 weeks evaluation.

The trouble is that the Zope process hangs when we try to call to an INSERT, UPDATE or DELETE ZSQL Method from a Zope Python Script and then call, say, a template page. This trouble disappears if we set autocommit on the Zope DA object.

In fact we ran into this problem while replacing the old Zope ODBC DA by the mxODBC Zope DA. All was working perfectly before (maybe because the old DA was automatically using a DA), but we decided to replace it for three reasons :

- consistency : we need true transactions ; we sometimes have to call two ZSQL Method and make sure both are executed in the same transaction, or none in case of failure. With autocommit, this would not be possible.
- performance : mxODBC Zope DA provide connection pooling and true multi-threading.
- capabilities : the 'fetch last available resultset option' allows us to insert rows and fetch the last autogenerated key in one single request, i.e. by 'INSERT INTO ... (...) VALUES (...) SELECT @@IDENTITY'.

Let me give an example : let's say we have those objects in a directory :

- db (a mxODBC Zope DA connection object with a max count of physical connections of 10 and 'fetch last available resultset' on)
- zsql1 (an insert ZSQL method)
- script1 (a python script)
- index_html (a TAL template)

script1 typically contains, amongst other things :

context.zsql1(param1='value1',param2='value2')
return context.index_html()

1) If we set autocommit to off (the default setting), and run the script, the Zope process hangs while holding physical connections to the database. We have to restart the Zope process.
2) If we set autocommit to on, all is running perfectly, except that we don't want to set autocommit to on, because of consistencies issues.
3) If we replace the last line by :
	return 'foobar'
   the two requests are processed and 'foobar' is displayed, i.e. all is running perfectly, except that the script doesn't do what we want it to do.

Does anybody see where the problem could be ?

Best regards,

Nicolas Lehuen
CTO
The CRM Company
http://www.thecrmcompany.com/
mailto:nicolas.lehuen at thecrmcompany.com
Tél. +33 (0)1 55 77 28 16
Fax. +33 (0)1 55 77 28 30