[Zope-DB] Wierd rollback problem in mxODBC DA

[Philip Kilner]

Hi Charlie,

Blimey - that was quick!

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 (?).

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

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

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!

-- 

Regards,

PhilK

Email: phil at xfr.co.uk / Voicemail & Facsimile: 07092 070518

"Work as if you lived in the early days of a better nation." - Alasdair Gray