Pb with transaction still active

[calou]

Hello,

Here is my code to save data in FB2.1

Code: Select all

if not IBCTrnsctnDataStatus.Active then
  begin
    IBCQryDataStatus.SQL.Text:='INSERT INTO AFFECT_DATA_STATUS (ID,DATE_HEURE, NOM_PROJET, TURBINE, VIT_VENT, PUISS_ACTIVE,'
                                +' TEMP_EXTERIEURE, CODE_STATUS, NOM_STATUS, FAMILLE_CONSTRUCTEUR, FAMILLE_VALOREM, STATUS_CORRIGE)'
                                +' VALUES( :ID,:DATE_HEURE, :NOM_PROJET, :TURBINE, :VIT_VENT, :PUISS_ACTIVE, :TEMP_EXTERIEURE, :CODE_STATUS,'
                                +' :NOM_STATUS, :FAMILLE_CONSTRUCTEUR, :FAMILLE_VALOREM, :STATUS_CORRIGE)';

      IBCTrnsctnDataStatus.StartTransaction;
      IBCQryDataStatus.Prepare;
      for r := 1 to frmMain.AdvStrgGrdAffectDataStatus.RowCount - 1 do
      begin
        try
          IBCQryDataStatus.ParamByName('ID').AsInteger:=r;
          IBCQryDataStatus.ParamByName('DATE_HEURE').AsDateTime:=STrToDateTime(frmMain.AdvStrgGrdAffectDataStatus.Cells[1,r]);
                    IBCQryDataStatus.Execute;
        except
          on E:EIBCError  do
          begin
           IBCTrnsctnDataStatus.Rollback;
           error:=TRUE;
           MessageDlg('Erreur lors de l''insertion des données dans la table temporaire',mtError,[mbOK],0);
           exit;
          end;
        end;//fin du try except
      end;//fin du for
      IBCTrnsctnDataStatus.Commit;
  end;//fin du if
  IBCQryDataStatus.UnPrepare;

This work good

After i do that

Code: Select all

          frmAffectDataStatus.IBCQryDataStatus.SQL.Text:='select count(*) as TOTAL from AFFECT_DATA_STATUS where NOM_PROJET='+AnsiQuotedStr(lst_prjt.Strings[p],'''')
                                     +' and TURBINE='+AnsiQuotedStr(LstNomTrbn.Strings[t],'''');
          frmAffectDataStatus.IBCQryDataStatus.Execute;
          total:=frmAffectDataStatus.IBCQryDataStatus.FieldByName('TOTAL').AsInteger;

if i recall the function to save data in the database the transaction is active.

I don't understand why

Thank you for help

Regards

[Plash]

InterBase/Firebird server does not allow to select data without a transaction. So IBDAC starts the transaction automatically when you execute SELECT statement.

[calou]

Hello,

In my post http://www.devart.com/forums/viewtopic.php?t=13145 you have written that i don't need to use transaction??

Could you write a very simple code for how to use select with ibcquery?

Thank you for help

Regards

[calou]

Sorry my question is that Could you write a very simple code for how to use select with ibcquery and ibctransaction?

Regards

[Plash]

You don't need to start a transaction manually when you open a query. But the transaction is started automaticaly. You can close the transaction after you have read all data. For example:

Code: Select all

IBCQryDataStatus.SQL.Text := 'select count(*) as TOTAL from AFFECT_DATA_STATUS where NOM_PROJET=1'; 
IBCQryDataStatus.Open; // this starts the transaction
total := IBCQryDataStatus.FieldByName('TOTAL').AsInteger;
IBCQryDataStatus.Close;
if IBCTrnsctnDataStatus.Active then
 IBCTrnsctnDataStatus.Rollback;

You can also use two different transactions for reading and writing data. Place on the form two TIBCTransaction components and two TIBCQuery components. Set the IsolationLevel property of the first TIBCTransaction component to iblReadOnlyReadCommited. This transaction will be used to execute SELECT statements. Assign SELECT statement to the SQL property of the first TIBCQuery component, and set its Transaction property to the read transaction component.
Assign INSERT statement to the SQL property of the second TIBCQuery component, and set its Transaction property to the write transaction component.

[calou]

Thank you Plash

Regards