[Zope-DB] Wierd rollback problem in mxODBC DA

Charlie Clark charlie at egenix.com
Fri Oct 22 05:17:52 EDT 2004

On 2004-10-22 at 10:57:08 [+0200], Philip Kilner <phil at xfr.co.uk> wrote:
> Hi Charlie,
> Blimey - that was quick!

Yes, well still recovering from cycling accident and a heavy cold this week 

> Charlie Clark wrote:
> > this should have nothing to do with Zope as it is a single, valid SQL
> > statement (SELECT INTO), at least in theory. But what when parts of the
> > SELECT query don't match with the INSERT definition? The database should
> > give a nice integrity error at least.
> > 
> Exactly - and in fact the view which is the source
> (V_QUAL_EREG_REG_VLD_DRIVER) is *very* heavily validated - anything
> which violates the requirements of an insert into
> T_CANDIDATE_REGISTRATION should never make it into that view in the
> first place.
> > From your original mail:
> > "On testing the ZSQL method in isolation, the results set returned the
> > appropriate number of records, complete with identity field data. On
> > reviewing the table, the records were not there but the counter for the
> > identity field indicated that the numbers in question had been allocated."
> > 
> That's right: -
> - calling the ZSQL from a script failed (but incremented the identity
> "counter" by the number of records involved, indicating a roll-back (?).

Probably. The counter might be incremented automatically by the size of the 
SELECT result set but this is implementation...
> - running the single ZSQL method on it's own exhibited the same
> behaviour - with the added feature of the entered records being returned
> by the select.

So, success as expected.

> - running the code from the ZSQL method as a stored procedure worked.

So, success as expected.
> I think it's reasonable to conclude that the issue is "downstream" of
> SQL Server, but I don't pretend to know where.
> > So testing in isolation does not do the INSERT either? This is definitely
> > on the DB side of things. What you need to do is as Marc-André has
> > suggested and run mxODBC in debugging mode via an ExternalMethod.
> > 
> Agreed - problem is, I no longer have a failing batch to play with!

oh, no development system? Slapped wrists for you then!
Duplicate the database, remove all entries and start from scratch.

Charlie Clark

Professional Python Services directly from the Source
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::

More information about the Zope-DB mailing list