[Zope-DB] Wierd rollback problem in mxODBC DA

[Charlie Clark]

Hm, Hi Phil,

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.

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

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.

Charlie

On 2004-10-22 at 10:23:14 [+0200], Philip Kilner <phil at xfr.co.uk> wrote:
> 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.
-- 
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 ! ::::