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]
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/
> To unsubscribe: [email protected]! 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]
