Select statement in Firebird 2.5 retaining transaction

[JavaPauloMG]

Good morning friends,

I convert all my delphi 7 projects, to UniDac Suite, but i got a strange behavior in my applications, in a simple select statement, like this sample:

Code: Select all

qry.SQL.Clear();
qry.SQL.Add('select * from RDB$TRIGGERS');
qry.Open;

But i solve my problemn when i start and rollback a transaction, like this sample:

Code: Select all

con.StartTransaction;

qry.SQL.Clear();
qry.SQL.Add('select * from RDB$TRIGGERS');
qry.Open;

con.Rollback;

It is a normal behavior of TUniQuery component? or i doing something wrong? my connection code is below:

Code: Select all

con.Username     := 'SYSDBA';
con.Password     := 'masterkey';
con.ProviderName := 'Interbase';
con.Server       := '127.0.0.1';
con.Database     := 'c:\sample.fdb';
con.SpecificOptions.Values['ClientLibrary'] := 'fbclient.dll';
con.SpecificOptions.Values['Charset']       := 'ISO8859_1';
con.Open;

Thanks to all, and sorry for my poor english.

P.S: I use http://www.sinatica.com/ to follow database behavior.

[AndreyZ]

Hello,

InterBase and Firebird require an active transaction for any operation under data. When you open a dataset without transaction, UniDAC starts transaction internally, and it allows you to read and write data. When you explicitly start transaction and rollback or commit it, dataset will be closed (because there will be no active transaction). You can choose which one of these two ways is more appropriate for you. You can control transactions by explicit calls, or you can let UniDAC do it automatically.

[JavaPauloMG]

Thanks for the aswer,

But when we destroy the TUniQuery object the transaction still alive, and keep some allocate resources in server side, my applications have two thousand users under this server, and the transaction objects are destroyed only when the TUniConnection object are destroy.

Exist some simple way, or connection param to free this transaction when i close, ou destroy TUniQuery object? without a direct transaction controller?

Code: Select all

var
  qryTmp : TUniQuery;
begin

  qryTmp := TUniQuery.Create(nil);

  qryTmp.Connection := con;

  qryTmp.SQL.Clear();
  qryTmp.SQL.Add('select * from RDB$TRIGGERS');
  qryTmp.Open;

  FreeAndNil(qryTmp);

[AndreyZ]

It's a designed behaviour of UniDAC when InterBase provider is used. When a dataset is closed, UniDAC leaves its transaction active. To make UniDAC close connections and transactions, you can set the TUniConnection.Options.DisconnectedMode option to True. The DisconnectedMode option is used to open a connection for performing a server call only when it's needed, and close a connection after performing the operation. For more information, please read the UniDAC documentation.

[JavaPauloMG]

Thanks again for de aswer,

We have buy the UniDac suite for a development team, but where i found this documentarion?

Thanks again for the attention.

[JavaPauloMG]

One more question please,

When i execute "FreeAndNil" statement the transaction are deallocate, i can do this same behavior when i execute "Close()" statemente in TUniQuery?

This question is because my legacy applicatons, in many places the form still open for all the day, and this forms have TUniQuery objects, but this querys are open and close, but not destroyed, and transaction stay allocate.

Thanks again.

[AndreyZ]

You can access UniDAC documentation through Delphi Main menu->UniDAC->UniDAC help.
If the DisconnectedMode property is set to True, UniDAC rolls back (default behaviour) dataset transactions if dataset was closed or freed.

[JavaPauloMG]

Thanks for the aswer and for the help,

I make some tests, using TUniQuery and "Close()" statement, but the transaction object still alive in database, and his is deallocate only when i destroyed the TUniQuery object, using "FreeAndNil()".

Connection configuration:


http://www.flickr.com/photos/53155197@N06/6191550735/

This code fragment not use "FreeAndNil()":

Code: Select all

var
  qryTmp : TUniQuery;
begin

  qryTmp := TUniQuery.Create(nil);

  qryTmp.Connection := con;
  qryTmp.SQL.Clear();
  qryTmp.SQL.Add('select * from RDB$TRIGGERS');
  qryTmp.Open;

  qry.Close();

After execution the transaction still alive:


http://www.flickr.com/photos/53155197@N06/6192068806/

This code fragment use "FreeAndNil()":

Code: Select all

var
  qryTmp : TUniQuery;
begin

  qryTmp := TUniQuery.Create(nil);

  qryTmp.Connection := con;
  qryTmp.SQL.Clear();
  qryTmp.SQL.Add('select * from RDB$TRIGGERS');
  qryTmp.Open;

  qry.Close();

  FreeAndNil(qryTmp);

After execution the transaction ate deallocate:


http://www.flickr.com/photos/53155197@N06/6191550965/

This is somekind of error? When i do "Close()" and transaction still alive?

Thanks again.

[AndreyZ]

I cannot reproduce this problem. If the DisconnectedMode option is set to True and dataset was closed, there are no active transactions in Synatica Monitor. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com.

[JavaPauloMG]

Thanks for attention,

I make some new tests, and i see the erro is in my application, the param works fine:

Code: Select all

var 
  qryTmp : TUniQuery; 
begin 

  qryTmp := TUniQuery.Create(nil); 

  qryTmp.Connection := con; 
  qryTmp.SQL.Clear(); 
  qryTmp.SQL.Add('select * from RDB$TRIGGERS'); 
  qryTmp.Open; 

  qry.Close(); //Close in incorrect object.

  FreeAndNil(qryTmp); 

The correct code is:

Code: Select all

var 
  qryTmp : TUniQuery; 
begin 

  qryTmp := TUniQuery.Create(nil); 

  qryTmp.Connection := con; 
  qryTmp.SQL.Clear(); 
  qryTmp.SQL.Add('select * from RDB$TRIGGERS'); 
  qryTmp.Open; 

  qryTmp.Close(); //Close in correct object.

  FreeAndNil(qryTmp); 

Thanks again for AndreyZ by your attention and pacient.

[AndreyZ]

It's good to see that you've found a solution. If any other questions come up, please contact us.