Help with TIBConnection, TSQLTransaction, and TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
ramutau · 2017-01-29 09:02

Hi there,

Newbie here, using Lazarus 1.6.2 r53354 FPC 3.0.0
i386-win32-win32/win64 and Firebird 2.5

I appreciate all the previous help, and my project is coming along
nicely. Thank you all.

One thing that I can't seem to grasp, and is causing me issues with
transactions/queries either NOT being active or ALREADY being active:

I have various Databases that I connect to, as well as various SQL
queries to each database, spread over about 15 forms.

At the moment I connect to all the databases at startup, and then to
the SQLTransactions and SQLQueries on the forms where they are needed.
The problem is I don't explicitly lose them on the form where I open
them.

My question is, what is the 'protocol' I need to follow?

Do I start a new transaction, and open the query/s related to a form
when I create that specific form, and then stop/close these when I
close each form?

Appreciate the help, as always

----------------------------------------------------------------------
mangakissa · 2017-01-29 16:13

The best way is to create a datamodule or more to separate your data
and GUI.
The best is creating a dataodule for each form. Not load them with
startup, but when the form creates
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses
DModule1; type TForm1 = class(TForm)  private     FDMModule :
TDMForm1;end; //event of formprocedure TForm1.formcreate(sender :
TObject)begin   FDMModule := TDMForm1.create;end;//event of
formprocedure TForm1.formdestroy(sender : TObject) begin 
 FDMModule.free;end Give each SQLQuery his own Transaction.
Do a Transaction.commitretaining when running subform. If action is
caReadCommit, it's not necessary.
QuoteI have various Databases that I connect to, as well as various
SQL queries to each database, spread over about 15 forms.
You have one database and 15 tables. Each table can have his own
SQLQuery or multiple ones when using join on several tables.
QuoteAt the moment I connect to all the databases at startup, and then
to the SQLTransactions and SQLQueries on the forms where they are
needed. The problem is I don't explicitly lose them on the form where
I open them.
Only opens the required SQLQueries of your form. Open all SQLQueries
gives you a lot of load-balance and  headache on using transactions.
This can be solved by creating a datamodule for each form or declare
the datamodule private in the form. By declaring private a new pointer
of the datamodule is created for all data. It's seperated from the
other datamodule.

----------------------------------------------------------------------
ramutau · 2017-01-29 17:17

Thank you SOOO much. That is exactly the advice I was looking for.
Does this close the queries and end the transactions as well?


procedure TForm1.formdestroy(sender : TObject)
15.begin
16.   FDMModule.free;
17.end

----------------------------------------------------------------------
mangakissa · 2017-01-30 08:11

When a datamodule is being freed, all related SQLQueries will be freed
too. When SQLquery gets the signal the dataset will be closed and
removed from the memory. But i'm not sure about the transactions. What
I do to prevent lost transactions is closing the opened SQLQueries
manually.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TDMDatamodule1.DataModuleDestroy(Sender: TObject);var teller  :
integer;    MyQuery : TSQLQuery;begin  for teller := 0 to
componentcount - 1 do  begin    if components[teller] is TSQLQuery
then    begin      MyQuery := TSQLQuery(components[teller]);      if
MyQuery.Active then      begin        if MyQuery.State in [dsInsert,
dsEdit] then          MyQuery.Cancel;        if (aDataset is
TSQLQuery) and (TSQLQuery(aDataset).Transaction is TSQLTransaction)
then          
TSQLtransaction(TsqlQuery(aDataset).Transaction).Commit; //also close
the dataset      end;    end;  end;end;

----------------------------------------------------------------------
ramutau · 2017-01-30 08:42

Thank you very much mangakissa. Your help is really appreciated.