mysql55conn from Thread slows closing applications
lazarus / 16-Databases

----------------------------------------------------------------------
salvarica · 2015-11-11 23:56

Thread is executed properly, in the course of work, the only problem
arises after closing the programs, namely there is no fault, only the
program closes 4-5 seconds longer, freeze it, and the program closes
neatly as it is not used thread.
The problem arose when I made the connection and transaction component
of the thread, previously I have inherited from the main form and tied
Query to them and used it, the program is regularly closed.

Quote
unit ThreadComboItemAdd;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqldb,mysql55conn,Forms,StdCtrls,Controls;

  type
    {TGetComboItems}
    TGetComboItems = class(TThread)
        private
          S: String;
          tCombo: TComboBox;
          StrList: TStringList;
          tField: String;
          tFieldID: String;
          tSQL: String;
          tIndex: Integer;
          tQ : TSQLQuery;
          tConn : TMySQL55Connection;
          tTrans : TSQLTransaction;
          procedure SetEnable;
          procedure SetDisable;
          procedure PropertyQuery;
          procedure AddToCombo;
          procedure ShowStatus;
          procedure SetCursorWait;
          procedure SetCursorDefault;
        public
          IsFinish : Boolean;
          constructor Create(sSQL,sField,sFieldID: String;
                             ComboBox: TComboBox; sIndex: Integer);
          destructor Destroy; override;
        protected
          procedure Execute; override;
    end;

implementation

uses glava1s;

{ TGetComboItem }

procedure TGetComboItems.SetEnable;
begin
    Form1.ComboEnable:=true;
    Form1.ComboObj:= tCombo;
    Synchronize(@Form1.SetComboEnable);
end;

procedure TGetComboItems.SetDisable;
begin
    Form1.ComboEnable:=false;
    Form1.ComboObj := tCombo;
    Synchronize(@Form1.SetComboEnable);
end;

procedure TGetComboItems.PropertyQuery;
begin
  // kreiranje konekcije
     tConn := TMySQL55Connection.Create(nil);
     tConn.Hostname := 'localhost';
     tConn.DatabaseName := 'baza';
     tConn.UserName := 'root';
     tConn.Password := 'root';
     tConn.CharSet:='latin1';
     tConn.Port:=3306;
  // kreiranje transakcije
     tTrans := TSQLTransaction.Create(nil);
     tTrans.Database := tConn;
  // kreiranje Query-a
     tQ := TSQLQuery.Create(nil);
     tQ.DataBase    := tConn;
     tQ.Transaction := tTrans;

     tConn.Transaction := tTrans;
  // Otvaranje konekcije i tabele
     tConn.Connected:=true;
     tQ.SQL.Add(tSQL);
end;


constructor TGetComboItems.Create(sSQL, sField, sFieldID: String;
                                  ComboBox: TComboBox; sIndex:
Integer);
begin
  // kreiranje thread-a
     FreeOnTerminate:=true;
     StrList := TStringList.Create;
     tField  := sField;
     tFieldID:= sFieldID;
     tIndex  := sIndex;
     tCombo  := ComboBox;
     tSQL    := sSQL;
     IsFinish:= false;
     inherited Create(true);
end;

destructor TGetComboItems.Destroy;
begin
  // Oslobađanje komponenti
     FreeAndNil(StrList);
     FreeAndNil(tConn);
     FreeAndNil(tQ);
     FreeAndNil(tTrans);
     inherited Destroy;
end;

procedure TGetComboItems.Execute;
begin
 // izvršavanje thread-a
  //if not Terminated then
    begin
     Synchronize(@SetDisable);
     Synchronize(@SetCursorWait);
     s:='Učitavanje podataka je u toku...';
     Synchronize(@ShowStatus);
     PropertyQuery;
     with tQ do
      Begin
        Open;
        while not eof do
          begin
            if (not IsFinish) then
              begin
                StrList.AddObject(FieldByName(tField).AsString
                                   
 ,TObject(FieldByName(tFieldID).AsInteger));
                Synchronize(@ShowStatus);
                Next;
              end
            else
               Last;
          end;
         close;
         tConn.Transaction.Commit;
         tConn.Connected:=false;
      end;
     IsFinish:=true;
     s:='Učitavanje podataka je izvršeno.';
     Synchronize(@ShowStatus);
     Synchronize(@AddToCombo);
     Synchronize(@SetCursorDefault);
     Synchronize(@SetEnable);
    end;
end;

procedure TGetComboItems.AddToCombo;
begin
  // Punjenje ComboBox-a na traženoj formi
     Form1.ComboObj:=tCombo;
     Form1.StrList:=StrList;
     Form1.ComboIndex:=tIndex;
     Synchronize(@Form1.SetComboItems);
end;

procedure TGetComboItems.ShowStatus;
begin
   // Ispis statusa thread-a
     Form1.StatusText:=s;
     Synchronize(@Form1.SetStatusText);
end;

procedure TGetComboItems.SetCursorWait;
begin
     Screen.Cursor:=crSQLWait;
end;

procedure TGetComboItems.SetCursorDefault;
begin
     Screen.Cursor:=crDefault;
end;

end.

What is the problem?

----------------------------------------------------------------------
rvk · 2015-11-12 09:15

First... It's best to place code between the [ code ] tags. You can
select your code and use the # above the editor.

Second... I see a lot, and I mean a lot, of Synchronize in your code.
I'm not sure if that really helps the smoothness of your program. But
I also see some problems in your use of Synchronize. For example in
AddToCombo you call Synchronize(@Form1.SetComboItems) but AddToCombo
itself is already called with Synchronize so you don't need to use
Synchronize again in that routine. The same goes for ShowStatus and
SetEnable.

And you have this line: Form1.StrList:=StrList; in AddToCombo.
This will not put all the strings in Form1.StrList. It will make
Form1.StrList point to your StrList in the thread. But that thread is
about to get destroyed so that StrList will cease to exist. So doing
it like this is very dangerous. You should use Assign to transfer all
the strings to Form1.StrList (which should have been created in your
Form1).

And finally... maybe I'm seeing this wrong... but you assign
s:='Učitavanje podataka je u toku...'; and you call
Synchronize(@ShowStatus) which uses s for every iteration of tQ. But I
don't see s changing. So why call Synchronize(@ShowStatus) so much.
Only once should be enough.

----------------------------------------------------------------------
JD · 2015-11-12 11:03

My own two cents (off the top of my head)

a) Remove the GUI elements (combobox etc) from your thread (because it
is not threadsafe and you may later want to use the results in another
control like a listview, stringgrid etc)
b) Set FreeOnTerminate to True for the thread
b) Fill the stringlist with the results of the query (in the thread)
c) Synchronize the thread with the main GUI by passing the stringlist
items i.e Synchronize(StrList). You only need one synchronize
statement
d) Read the stringlist you have passed (in the main form)
e) Copy the contents of the stringlist passed by the thread into your
combobox (in the main form)

JD

----------------------------------------------------------------------
salvarica · 2015-11-12 11:17

Problem solved, thanks to their help.

http://forum.lazarus.freepascal.org/index.php?topic=27379.0

Adding components solved the problem, the rest is functioning
properly.