Hi Mauro, Based on what you have said, would you be able to to run your queries in separate transactions, testing for valid entry before moving onto the next transactions.
Or you could also run the more than one transaction within a main transaction if that would help? BeginTrans ++BeginTrans ++Insert Into Table1 ++CommitTrans ++BeginTrans ++Insert Into Table2 ++CommitTrans CommitTrans "First the end: about "if MyAdoConnection. InTransaction = True then" => of course I do these checks. I did not report it because it was not related to" Sorry about that, I've send quite a lot of code in the past where that is missed out. mauro russo wrote: > > First the end: about "if MyAdoConnection.InTransaction = True then" > => of course I do these checks. I did not report it because it was not > related to > the problem, but I thank you to try to help me in any detail. > > For the main question, I start from the Ariel comment: > > <So basically your transaction goes like this: you insert R1 into > MyTable1, then for each record in MyTable2 you execute a query that > inserts a record R2 into MyTable3 which has a reference to R1.> > > The point is that I need to transfer a block of data from MyTable2 to > MyTable3, > translating each row according to a fixed rule. The rows transferred > into MyTable3 > refer a 'head'-row in MyTable1 [the row R1]. > At the same way, the scanned rows of MyTable2 refer to a 'head'-row in > a MyTable4. > In practice each row in MyTable4 represents a "block" of data which > need to be > transferred and translated from MyTable2 to MyTable3 [the rows of new > block in MyTable3 will > refer the 'head'-row R1 in MyTable1]. > > Because of that, my atomic data is a block of rows and so I use an > atomic transaction > to trasfer the full block. > > However, also if you have useful comments about a different way to > reach my goal, > I still would like to understand why an exception on MyADOQuery.ExecSQL(); > generates the transaction closure. > About that, I did not understand your sentence "So if the second fails > then so does the first". > Do you mean : " if the nested 'except' fails to catch the exception, > so does the non-nested 'except' "? > If you mean that, I believe it is not related to my question, because > in my case the nested 'except' > is able to catch the exception. > > Thanks for help. I will wait your useful comments. > Mauro. > > ----- Original Message ----- > From: Mike Kershaw > To: [email protected] <mailto:delphi-en%40yahoogroups.com> > Sent: Monday, February 09, 2009 10:47 PM > Subject: Re: [delphi-en] TADOQuery / TADOConnection > > Hi Mauro, > > Based on the way you have set up this transaction you will always roll > back the entire transaction when you hit an exception as your second > try, except block is within the first. So if the second fails then so > does the first, you may want to set each set of inserts in their own > transactions and test each one. > Can you have records in table 1 if table 2 or 3 fail? Can you give me > an idea of what sort of rules you are trying capture within the > transaction. > > Also when Committing your transaction you may wish to test for it first, > i.e.: > if MyAdoConnection.InTransaction = True then > MyAdoConnection.CommitTrans; > > Kind regards, > Mike. > > mauro russo wrote: > > Hi everyone, > > > > I am using Delphi 7. > > > > I am using a TADOConnection object and some design-time TADOQuery > objects. > > > > A my procedure works starting by > > > > MyAdoConnection.BeginTrans(); > > > > and ending by > > > > MyAdoConnection.CommitTrans(); > > > > > > In the block there is a MyADOQuery object for which I use (becaus of > sometimes it can generate DB exceptions) > > > > try > > MyADOQuery.ExecSQL(); > > except > > end; > > > > > > My problem is that when MyADOQuery.ExecSQL() generates an exception, > the transaction is automatically closed. > > > > I say that because my pascal-like code is > > > > MyAdoConnection.BeginTrans(); > > try > > Insert in a MyTable1 > > > > for each row on a MyTable2 > > do Insert in a MyTable3 through > > a) preparing MyADOQuery > > b) executing MyADOQuery by > > try > > MyADOQuery.ExecSQL(); > > except > > end; > > > > MyAdoConnection.CommitTrans(); > > except > > MyAdoConnection.RollbackTrans(); > > end; > > > > > > and, when MyADOQuery.ExecSQL(); generates an exception for some row, > the same happens for all following rows, > > but for the following rows it is because MyTable3 has a reference to > the row initially inserted in MyTable1, which is > > like disappeared (my explanation is simply that the transaction is > automatically closed with a Rollback before > > continuing the for cycle). > > > > Do someone knows if there is some property which I should set to > avoid the automatic rollback on the transaction? > > > > Do you need some other detail? > > > > > > Best Regards, > > Mauro Russo. > > > > > > > > ---------- > > > > Questa email è stata verificata dal sistema centralizzato antivirus > della UniPlan Software > > > > > > [Non-text portions of this message have been removed] > > > > > > > > ------------------------------------ > > > > ----------------------------------------------------- > > Home page: http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/> > > To unsubscribe: [email protected] > <mailto:delphi-en-unsubscribe%40yahoogroups.comYahoo>! Groups Links > > > > > > > > ---------------------------------------------------------- > > > > > > No virus found in this incoming message. > > Checked by AVG - www.avg.com > > Version: 8.0.233 / Virus Database: 270.10.19/1941 - Release Date: > 02/07/09 13:39:00 > > > > > > ---------- > > Questa email è stata verificata dal sistema centralizzato antivirus > della UniPlan Software > > [Non-text portions of this message have been removed] > > > ------------------------------------------------------------------------ > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.0.233 / Virus Database: 270.10.19/1942 - Release Date: 02/07/09 > 13:39:00 > >
