Transaction handling

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
GVCL
Posts: 16
Joined: Tue 09 Dec 2008 16:08

Transaction handling

Post by GVCL » Tue 30 Jan 2018 14:38

Hi,
I still have some problems using transactions properly.

The idea is: A form is shown, the user enters data, saves it, prints it and closes the form. After saving the data he might change data and save them again. Only when the data is saved and printed everything is ok and when closing the form the data is committed.

Let's say I have 3 datamodules: two contain 2 TIBCQuery (QuOne1a, QuTwo1a, QuOne1b, QuTwo1b) components, one contains TIBCConnection (DBCon) and 2 TIBCTransaction (DBTr, DBTrUpd) components.

The TIBCConnection component has AutoCommit=false, DefaultTransaction=DBTr.

The TIBCQuery components have AutoCommit=false, Transaction=DBTr, UpdateTransaction=DBTrUpd.
The QuOne1a and QuTwo1a are used to insert data into tables TBLONE and TBLTWO. QuOne1b and QuTwo1b are used to provided the data from TBLONE and TBLTWO for printing reports.

QuOne1a and QuTwo1a are only opened and used to insert data when the user entered data into edit fields and clicked 'save'. So 'open', 'insert', QuOne1a.FieldByName('XYZ').AsString:='abcde'..., QuTwo1a.FieldByName('XXYY').AsInteger:=1234..., 'post' and 'close' are called.

Kind of (abstract):

Code: Select all


procedure FormShow;
begin
 DBTrUpd.StartTransaction;
 ...
end;

procedure OnSaveDataClick;
begin
 With QuOne1a do
  begin
   Open;
   Insert;
   FieldByName('XXX').Value:=Edit1.Text;
   FieldByName('XXY').Value:=Edit2.Text;
   FieldByName('XXZ').Value:=StrToInt(Edit3.Text);
   Post;
   Close;
  end;
 With QuTwo1a do
  begin
   Open;
   Insert;
   FieldByName('YYX').Value:=StrToInt(Edit4.Text);
   FieldByName('YYY').Value:=StrToFloat(Edit5.Text);
   FieldByName('YYZ').Value:=Edit6.Text;
   Post;
   Close;
  end;
end;

procedure OnPrintClick;
begin
 QuOne1b.Open;
 QuTwo1b.Open;
 PrintReport;
 QuOne1b.Close;
 QuTwo1b.Close;
end;

procedure FormClose;
begin
 if AllOk then
  DBTrUpd.Commit
 else
  DBTrUpd.Rollback;
end;

But QuOne1b and QuTwo1b do not see the data which is inserted by QuOne1a and QuTwo1b.

Where is my misunderstanding of how transactions work in this case?

ViktorV
Devart Team
Posts: 2780
Joined: Wed 30 Jul 2014 07:16

Re: Transaction handling

Post by ViktorV » Wed 31 Jan 2018 08:14

When TIBCQuery.AutoCommit is set to False, the transaction is only committed when the Commit (CommitRetaining) method of the UpdateTransaction transaction is explicitly called. Therefore, to solve your task, you should call the Commit (CommitRetaining) method of the UpdateTransaction transaction after calling Post.

GVCL
Posts: 16
Joined: Tue 09 Dec 2008 16:08

Re: Transaction handling

Post by GVCL » Wed 31 Jan 2018 09:40

Hi Victor,

But that is not, what I want to achieve. The customer has to be able to change/edit data and 'save' and 'print' it as often as he wants, but he might as well do a 'quit' after all changes/edits and prints which then should have no effect on the database. So the idea is to start a transaction when the form is shown and to commit or rollback when it's closed. But all actions ('save', 'print') between 'show' and 'close' must be possible.

For different reasons I cannot use the same TQuery components for editing and printing - as shown they are in different datamodules for different purpose.

Any hint?

ViktorV
Devart Team
Posts: 2780
Joined: Wed 30 Jul 2014 07:16

Re: Transaction handling

Post by ViktorV » Wed 31 Jan 2018 10:53

In this case, you should perform all operations ('save', 'print') within one transaction. You should use one transaction for all the datasets and do not use the UpdateTransaction property.
For this, set the Transaction property to DBTr for all your data sets (QuOne1a, QuTwo1a, QuOne1b, QuTwo1b), and set the UpdateTransaction property to an empty string. When opening an application, you should execute the DBTr.StartTrasnsaction method, and when closing, execute DBTr.Commit or DBTr.Rollback.

GVCL
Posts: 16
Joined: Tue 09 Dec 2008 16:08

Re: Transaction handling

Post by GVCL » Wed 31 Jan 2018 11:19

Hi Victor,

Thanx a lot!

My misunderstanding was that using the UpdateTransaction is mandantory...

ViktorV
Devart Team
Posts: 2780
Joined: Wed 30 Jul 2014 07:16

Re: Transaction handling

Post by ViktorV » Wed 31 Jan 2018 11:22

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about our products.

Post Reply