"UniQuery1.UpdateTransaction" How do I use?

[gn7soft]

Hello,

I'm using UniDac6.0.2(Professional) for Firebird2.1

To commit my operations manually, I set the AutoCommit property of TUniQuery to False.
Also, to avoid the dataset is closed when commit or rollback, I used different transactions for connection and dataset.
But, eventhough I don't use different transactions for reading and modifying data, below code works good.

When do I use Uniquery1.UpdateTransaction?
Also, How do I use Uniquery1.UpdateTransaction?

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin

  DM.UniConnection1.AutoCommit:=true;     //DM: DataModule

  DM.UniConnection1.DefaultTransaction := UniTransaction1;
  UniQuery1.Transaction := UniTransaction2; 

  UniQuery1.SpecificOptions.Values['AutoCommit'] := 'False';  

  //Reading Data
  With UniQuery1 Do  
   Begin
    Close;
    Sql.Clear;
    SQL.Text:='Select * From Car';
    Open;
   end;
 

  //Modifying data
  If DM.UniConnection1.InTransaction=False Then DM.UniConnection1.StartTransaction;

  Try
   With UniQuery1 Do
    Begin
     Append;
     FieldByName('CARNAME').AsString:=Trim(EdCarName.Text); 
     FieldByName('ADDR').AsString:=Trim(EdAddr.Text);   
     FieldByName('TEL').AsString:=Trim(EdTel.Text);   
     Post;
     DM.UniConnection1.Commit;
    End;
  Except
    Dm.UniConnection1.Rollback;
    UniQuery1.CanCel;
  End;
end;

Regards,
Peace

[ViktorV]

The similar question has already been discussed on our forum. Follow the link http://forums.devart.com/viewtopic.php? ... ion#p83624 for details.
If any questions remain after reading the blog, please contact us again.

[gn7soft]

Thanks for your reply. I question once more.
I know that UniQuery1.UpdateTransaction is used for modifying data.

But, eventhough I don't use UniQuery1.UpdateTransaction for modifying data as below code, the below code works good. (that is, UniQuery1.UpdataTransaction property is empty but below code works good.)

I don't know when to use the UniQuery1.UpdateTransaction.
If possible, for my understand, please insert UniQuery1.UpdateTransaction in below sample code.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin

  DM.UniConnection1.AutoCommit:=true;     //DM: DataModule

  DM.UniConnection1.DefaultTransaction := UniTransaction1;
  UniQuery1.Transaction := UniTransaction2; 

  UniQuery1.SpecificOptions.Values['AutoCommit'] := 'False';  

  //Reading Data
  With UniQuery1 Do  
   Begin
    Close;
    Sql.Clear;
    SQL.Text:='Select * From Car';
    Open;
   end;


  //Modifying data
  If DM.UniConnection1.InTransaction=False Then DM.UniConnection1.StartTransaction;
  Try
   With UniQuery1 Do
    Begin
     Append;
     FieldByName('CARNAME').AsString:=Trim(EdCarName.Text); 
     FieldByName('ADDR').AsString:=Trim(EdAddr.Text);   
     FieldByName('TEL').AsString:=Trim(EdTel.Text);   
     Post;
     DM.UniConnection1.Commit;
    End;
  Except
    Dm.UniConnection1.Rollback;
    UniQuery1.CanCel;
  End;
end;

Regards,
Peace

[ViktorV]

Below is your modified sample using UpdateTransaction:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin

  DM.UniConnection1.AutoCommit:=True;     //DM: DataModule

  DM.UniConnection1.DefaultTransaction := UniTransaction1;
  UniQuery1.UpdateTransaction := UniTransaction2; 

  UniQuery1.SpecificOptions.Values['AutoCommit'] := 'False';  

  //Reading Data
  With UniQuery1 do begin
    Close;
    Sql.Clear;
    SQL.Text:='Select * From Car';
    Open;
  end;


  //Modifying data
  if UniQuery1.UpdateTransaction.Active = False then 
    UniQuery1.UpdateTransaction.StartTransaction;
  Try
    With UniQuery1 do Begin
      Append;
      FieldByName('CARNAME').AsString:=Trim(EdCarName.Text); 
      FieldByName('ADDR').AsString:=Trim(EdAddr.Text);   
      FieldByName('TEL').AsString:=Trim(EdTel.Text);   
      Post;
      UniQuery1.UpdateTransaction.Commit;
    end;
  except
    UniQuery1.UpdateTransaction.Rollback;
    UniQuery1.Cancel;
  end;
end;

[gn7soft]

Thanks. I understood that I should be using different transactions for connections and datasets.
(also, for reading and modifying data)

I have two questions to ask you.

1) I would like to insert property of UniQuery1.Transaction in upper code.
Please see below. Is this code right?

DM.UniConnection1.DefaultTransaction := UniTransaction1;
UniQuery1.UpdateTransaction := UniTransaction2;
UniQuery1.Transaction:=UniTransaction3;


2) Eventhough UniQuery1.UpdateTransaction property is empty, below code for reading and modifying data works good. Also, a dataset(UniQuery1) is not closed. Could you tell me the reason?

Code: Select all

  DM.UniConnection1.AutoCommit:=true;     //DM: DataModule

  DM.UniConnection1.DefaultTransaction := UniTransaction1;
  UniQuery1.Transaction := UniTransaction2; 

  UniQuery1.SpecificOptions.Values['AutoCommit'] := 'False';  

  //Reading Data
  With UniQuery1 Do  
   Begin
    Close;
    Sql.Clear;
    SQL.Text:='Select * From Car';
    Open;
   end;


  //Modifying data
  If DM.UniConnection1.InTransaction=False Then DM.UniConnection1.StartTransaction;

  Try
   With UniQuery1 Do
    Begin
     Append;
     FieldByName('CARNAME').AsString:=Trim(EdCarName.Text); 
     FieldByName('ADDR').AsString:=Trim(EdAddr.Text);   
     FieldByName('TEL').AsString:=Trim(EdTel.Text);   
     Post;
     DM.UniConnection1.Commit;
    End;
  Except
    Dm.UniConnection1.Rollback;
    UniQuery1.CanCel;
  End;

Regards,
Peace

[ViktorV]

1. The code you have provided with using UniQuery1.Transaction is correct.
2. If the TUniQuery.UpdateTransaction property is set to an empty string, then the default connection transaction is used for the data modification. For data reading, the transaction that is defined in the TUniQuery.Transaction property is used.

[gn7soft]

Thanks a lot for your kind help.

Regards,
Peace

[ViktorV]

Thank you for your interest to our products.
Feel free to contact us if you have any further questions about UniDAC.

[gn7soft]

Hello,

I have a problem during transaction.
On the below code, ①scope is not executed.
The error message is not appeared, but UPDATE statement is not executed.
But, it execute well on ZeosDbo.
I'm changing from ZeosDbo to UniDac6.0.2.
I have several code like ①scope for transaction in my project.
I would like to execute various querys in a transaction, under dataset(TUniquery) AutoCommit is False.
Please help me for successful execution of ①scope code.

Code: Select all

   DM.UniConn.DefaultTransaction := DM.UniTransaction1; // DM:DataModule
   UniQuery1.Transaction := DM.UniTransaction2;         // a transaction for reading data
   UniQuery1.UpdateTransaction := DM.UniTransaction3;   // a transaction for modifying data

   UniQuery1.SpecificOptions.Values['AutoCommit'] := 'False';
   UniQuery3.SpecificOptions.Values['AutoCommit'] := 'False';

   With UniQuery1 Do
   Begin
     Close;
     Sql.Clear;
     SQL.Text:='Select * From Car';    
     Open;
   end;

   if UniQuery1.UpdateTransaction.Active = False then UniQuery1.UpdateTransaction.StartTransaction;
   Try
      With UniQuery1 do begin
        edit;
        FieldByName('CarNo').AsString:=trim(edCarNo.Text);
        FieldByName('CustCode').AsString:=trim(edCustCode.Text);
           :
           :
        Post;
      end;
   
      with UniQuery3 Do begin  //①~Start
        Close;
        Sql.Clear;
        Sql.Text:='update CarCode Set CarNo='''+EdCarNo.Text+''''+','+
                  'AGuide='''+EdGuide1.Text+''''+','+
                  'StartDay='''+DeStartDay1.Text+''''+','+
                    :
                    :
        execsql;
      end;                     //①~End

      UniQuery1.UpdateTransaction.Commit;

   Except
      UniQuery1.UpdateTransaction.Rollback;
   End;

Regards,
Peace

[ViktorV]

Upon execution of the TIBCQuery.ExecSQL method, a transaction defined in the TIBCQuery.Transaction property, and not in the TIBCQuery.UpdateTransaction property, is used. To succfully execute the UPDATE query in your example, you can change it as follows:

Code: Select all

   DM.UniConn.DefaultTransaction := DM.UniTransaction1; // DM:DataModule
   UniQuery1.Transaction := DM.UniTransaction2;         // a transaction for reading data
   UniQuery1.UpdateTransaction := DM.UniTransaction3;   // a transaction for modifying data

   UniQuery1.SpecificOptions.Values['AutoCommit'] := 'False';
   UniQuery3.SpecificOptions.Values['AutoCommit'] := 'False';

   With UniQuery1 Do
   Begin
     Close;
     Sql.Clear;
     SQL.Text:='Select * From Car';    
     Open;
   end;

   if UniQuery1.UpdateTransaction.Active = False then UniQuery1.UpdateTransaction.StartTransaction;
   Try
      With UniQuery1 do begin
        edit;
        FieldByName('CarNo').AsString:=trim(edCarNo.Text);
        FieldByName('CustCode').AsString:=trim(edCustCode.Text);
           :
           :
        Post;
      end;
   
     UniQuery3.Transaction := DM.UniTransaction3;
      with UniQuery3 Do begin  //①~Start
        Close;
        Sql.Clear;
        Sql.Text:='update CarCode Set CarNo='''+EdCarNo.Text+''''+','+
                  'AGuide='''+EdGuide1.Text+''''+','+
                  'StartDay='''+DeStartDay1.Text+''''+','+
                    :
                    :
        execsql;
      end;                     //①~End

      UniQuery1.UpdateTransaction.Commit;

   Except
      UniQuery1.UpdateTransaction.Rollback;
   End;

[gn7soft]

Thank you very much for your help.

Regards,
Peace

[ViktorV]

If you have any questions during using our products, please don't hesitate to contact us.