BorlandTalk.com

Borland · Guest

I am using Delphi 6 with SQL 2000 (and MSDE) through TADOConnection and a
TADOCommand.

I have two tables

InvoiceHeader
InvoiceDetail

For the purposes of this test I am always inserting a unique PK into the
Header table and duplicate PKs into the detail table

Transaction 1
INSERT INTO InvoiceHeader (HPK, Total) VALUES (1, 100)
INSERT INTO InvoiceDetail(DPK, Item) VALUES (1, 'ABC')
INSERT INTO InvoiceDetail(DPK, Item) VALUES (2, 'DEF')

Transaction 2
INSERT INTO InvoiceHeader (HPK, Total) VALUES (2, 200)
INSERT INTO InvoiceDetail(DPK, Item) VALUES (1, 'ABC')
INSERT INTO InvoiceDetail(DPK, Item) VALUES (2, 'DEF')

My code goes something like this

con.BeginTrans
com.Execute
try
con.CommitTrans
except
ShowMessage('Error')
end

Assuming empty tables
- The first transaction commits just fine and I see 1 row in the header
table and 2 rows in the detail table.
- The second transaction does NOT fire an exception and worse I see 2 rows
in the header table and 2 rows in the detail table.

I did some playing around and found that if I check con.Errors.Count after
the Execute I can see that an error (or more) has occured and then decide to
not commit the transaction

con.BeginTrans
com.Execute
if con.Errors.Count <> 0 then begin
con.RollbackTrans;
end else begin
try
con.CommitTrans
except
ShowMessage('Error')
end;
end;

But this is not how I expected it to work at all. When I look around for
examples on how to do transactions with Delphi & ADO I always see a
variation of the first piece of code.

I did some more experimentation and found that if I execute the three
inserts as three separate Execute commands before doing a commit I got the
same results as the first case (the first statement gets committed and the
last two do not).

From what I can gather, ADO treats each statement within the command text as
a separate transaction regardless of my stating con.BeginTrans. As well, ADO
doesn't seem to consider a PK Violation as an exception which I find odd.

Does this correspond to what anybody else has found?
Have I missed a setting that I should be aware of?

Any enlightenment would be much appreciated

Richard Speiss

Rolf Hesterberg · Guest

Hi

You are from Borland and you don't know the answer?

Viatcheslav V. Vassiliev · Guest

Richard Speiss · Guest

Oops, I had my newsgroup reader configuration wrong.

The point I was making is that an exception is not fired in the case of a pk
violation. Your code doesn't doesn't work either. But you are right about
having the begin and end in the try block. My real code actual does that
but I didn't type it in correctly when I was making my example.

My example was a simplified case to illustrate what is happening. I
actually do store the pk of the header in the detail but I needed something
that would case the error I was talking about

Thanks for thinking about it though

Richard

"Rolf Hesterberg" <r.hesterberg (AT) heimsoft (DOT) ch> wrote

Rolf Hesterberg · Guest

Hi Viatcheslav

Rolf Hesterberg · Guest

Hi Richard

Richard Speiss · Guest

DPK is the detail primary key.

The INSERT commands are designed to generate the error to show that ADO is
not generating an exception when a key violation occurs

I am working around the problem by testing con.Errors.Count after the
Execute to see if there are problems.

I am merely trying to point out that you can't depend on an exception being
raised

Richard

"Rolf Hesterberg" <r.hesterberg (AT) heimsoft (DOT) ch> wrote