Transaction handling

[GVCL]

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]

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]

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]

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]

Hi Victor,

Thanx a lot!

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

[ViktorV]

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.