Hi Mauro, Yes I believe that the transaction would be closed as you are on running one transaction, however the loop would still try and continue as it has not been told otherwise. Have you tried testing this without the inserts into table 1 not being in the transaction to see what happens if table 3 fails on one of it's rows to see which records get inserted? - I don't if this will help.
mauro russo wrote: > > > > You are the calling the roll back transaction in your code at the moment > in final except block, so if any part of that block fails then the > entire transaction will roll back. > > > > No, Mike, this is false. > The internal-nested-except is able to catch the exception and so the > for cycle goes on, > but all next inserts in MyTable3 fail because the row R1 in MyTable1 > does not exists anymore > [in fact the message of these further catched exceptions read like > "...foreign key unsitisfied..."] > My explanation is that the transaction is automatically closed on the > exception. > > Mauro. > > ----- Original Message ----- > From: Mike Kershaw > To: [email protected] <mailto:delphi-en%40yahoogroups.com> > Sent: Tuesday, February 10, 2009 12:59 PM > Subject: Re: [delphi-en] TADOQuery / TADOConnection > > Hi Mauro, > > http://www.codeproject.com/KB/database/sqlservertransactions.aspx > <http://www.codeproject.com/KB/database/sqlservertransactions.aspx> has > some good information on transactions > > "The DB is "SQL Server Express 2005"" With this in mind, why don't you > let SQL Server run the transaction in a stored procedure and then it can > control any fails > > " About the "behavior switching" I would like just that > the rollback happens only if I explicitly call RollbackTrans. > In practice [and, in fact, this was in my mind when I wrote the code] > if the nested except statement catches the exception raised on > the statement "MyADOQuery. > ExecSQL()", I would like to continue in > the same previously open transaction, beacuse I still did not explictly > call RollbackTrans. Is it possible?" > You are the calling the roll back transaction in your code at the moment > in final except block, so if any part of that block fails then the > entire transaction will roll back. > > " Again, how to realize "++BeginTrans ? Is it just enough to call > BeginTrans twice?" > It would read a bit like this: > > Begin Tran > Select * from Table > Insert ... > Begin tran nested > Select * from another table > Insert ... > Commit tran nested > etc > You need also to set up counts for the transactions; the web address > above gives some good examples, but as Theo mentioned these things > needed to be planned very carefully otherwise you may end up with some > very undesirable results. > > Mike. > > mauro russo wrote: > > > > The DB is "SQL Server Express 2005" > > > > A) > > Can you give me references about save-points and rollback-points? > > > > B) > > The behavior can change if I use a different server, but still > > TADOConnection and TADOQuery. > > > > C) > > About the "behavior switching" I would like just that > > the rollback happens only if I explicitly call RollbackTrans. > > In practice [and, in fact, this was in my mind when I wrote the code] > > if the nested except statement catches the exception raised on > > the statement "MyADOQuery.ExecSQL()", I would like to continue in > > the same previously open transaction, beacuse I still did not explictly > > call RollbackTrans. Is it possible? > > > > D) > > Again, how to realize "++BeginTrans"? Is it just enough to call > > BeginTrans twice? > > > > Very thansk for help. > > Mauro. > > > > ----- Original Message ----- > > From: Mike Kershaw > > To: [email protected] <mailto:delphi-en%40yahoogroups.com> > <mailto:delphi-en%40yahoogroups.com> > > Sent: Tuesday, February 10, 2009 12:01 PM > > Subject: Re: [delphi-en] TADOQuery / TADOConnection > > > > Hi Mauro, > > > > Yes you can run nested transactions, it is possible under SQL Server, I > > should have asked actually which database you were using for the > back end? > > > > You can also set save points and rollback points within your > > transactions so if part of it fails then you can roll back to different > > parts of the transaction depending on what you need. > > > > With the exception I believe your issue is arising because the for loop > > is within the BeginTrans call, a single rollback will always roll back > > entire transaction. > > > > "But, still a question: is really not possible to switch the behavior > > "exception => transaction closure" to the behavior "exception !=> > > transaction closure"?" > > Do you mean by this you only want the Transaction to roll back under > > certain exceptions? Sorry did quite get that bit. > > > > Mike. > > > > mauro russo wrote: > > > > > > Wow! > > > > > > I have never heard about nested transactions. > > > Is it possible? [logically it seems to be not so wonderfull, but in > > > practice I did not believe it is possible]. > > > > > > If yes, now I could understand why it can be normal the transaction > > > closure if there is an exception. > > > But, still a question: is really not possible to switch the behavior > > > "exception => transaction closure" to the behavior "exception !=> > > > transaction closure"? > > > > > > Finally, how to realize "++BeginTrans"? > > > Is it just enough to call BeginTrans twice? > > > > > > Regards, > > > Mauro. > > > > > > ----- Original Message ----- > > > From: Mike Kershaw > > > To: [email protected] <mailto:delphi-en%40yahoogroups.com> > <mailto:delphi-en%40yahoogroups.com> > > <mailto:delphi-en%40yahoogroups.com> > > > Sent: Tuesday, January 20, 2009 11:29 AM > > > Subject: Re: [delphi-en] TADOQuery / TADOConnection > > > > > > 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> <mailto:delphi-en%40yahoogroups.com> > > <mailto:delphi-en%40yahoogroups.com> > > > <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/> > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/>> > > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/> > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/>>> > > > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/> > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/>> > > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/> > > <http://groups.yahoo.com/group/delphi-en/ > <http://groups.yahoo.com/group/delphi-en/>>>> > > > > > To unsubscribe: [email protected] > <mailto:delphi-en-unsubscribe%40yahoogroups.comYahoo> > > <mailto:delphi-en-unsubscribe%40yahoogroups.comYahoo> > > > <mailto:delphi-en-unsubscribe%40yahoogroups.comYahoo> > > > > <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 > > > > > > > > > > > > > > ---------- > > > > > > 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 > > > > > > > > > > ---------- > > > > 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 > > > > > > ---------- > > 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 > >
