Devart Forums

Posted: **Mon 26 Sep 2011 14:58**

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.

Posted: **Tue 27 Sep 2011 09:39**

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.

Posted: **Tue 27 Sep 2011 11:43**

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);

Posted: **Tue 27 Sep 2011 15:12**

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.

Posted: **Tue 27 Sep 2011 17:27**

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.

Posted: **Tue 27 Sep 2011 20:41**

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.

Posted: **Wed 28 Sep 2011 12:04**

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.

Posted: **Wed 28 Sep 2011 13:27**

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.

Posted: **Thu 29 Sep 2011 08:06**

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.

Posted: **Thu 29 Sep 2011 12:55**

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.

Posted: **Thu 29 Sep 2011 13:37**

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

Devart Forums

Select statement in Firebird 2.5 retaining transaction

Select statement in Firebird 2.5 retaining transaction