[Zope-DB] Wierd rollback problem in mxODBC DA

[Philip Kilner]

Hi Matthew,

Matthew T. Kromer wrote:
> if I had to guess at it, I'd think you're seeing some kind of 
> glitch in your actual database, most likely having to do with how 
> independent transactions view each other when run concurrently.
> 

Hmmm...but the identical code to that produced by the ZSQL query 
template, when run cut and pasted into a stored procedure, executed as 
intended.

> You didn't list your target database type, nor your code, so I cant 
> guess any further.
> 

I did, in fact - it's MS SQL Server.

The ZSQL Method reads: -

INSERT INTO dbo.T_CANDIDATE_REGISTRATION(
           CreateGUID,
           Forenames,
           Surname,
           DateOfBirth,
           Gender,
           EthnicGroup,
           OrgID,
           AwardID,
           CentreCandidateRef,
           SpecialAssessment
           )
SELECT    TransactionRef,
           Forenames,
           Surname,
           IsoDoB,
           Gender,
           CorrectedEthnicGroup,
           AsetCentreRef,
           AwardID,
           CentreCandidateRef,
           SpecialAssessBit
FROM      dbo.V_QUAL_EREG_REG_VLD_DRIVER
WHERE     (RegBatchID = <dtml-sqlvar RegBatchID type="int">) AND
           (BatchResultSource = 'Accepted')

As you can see it's very simple, the selection is based on "RegBatchID" 
- only this batch has been an issue as a ZSQL method, but it ran fine as 
an SP.

> I would be surprised if mxODBC were the culprit; the symptoms you 
> describe usually have to do with understanding the nuances of your 
> target database and how Zope actually drives the adapter.
> 
> For example, are you sure the SQL method which queries the table isn't 
> caching the results?   There's a cache tab which controls this; the 
> result cache is unaware of any updates you may do which would invalidate 
> the cache.  Not caching is the default behavior, but if you turned 
> caching on you could be bitten by this.
> 

Well, I'm, not sure what it's /doing/, but in terms of settings - it's 
turned off.

-- 

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