[Zope-DB] Wierd rollback problem in mxODBC DA

[Charlie Clark]

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
-- 
Charlie Clark
eGenix.com

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 ! ::::