[SOLVED] Thread - program exits after the sql exception

[tudi_x]

Hi All,
Please advise what I am missing.
Basically what I want to achieve is to pass to the main form the error encountered during the run of a sql query against a database.
Unfortunately I do not reach even Execute as I get access violation in main form immediately after the thread create.

Main form:

Code: [Select]

unit main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, DBGrids, query, db;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure Button1Click(Sender: TObject);
    procedure HandleError(Thread: TThread; E: Exception);
    procedure DisplayResults(ADataset : TDataSet);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);

var
  q : tQuery;

begin
  Button1.Caption:= 'Busy';

  q.Create(true, 'select sysdate from dual');
  if Assigned(q.FatalException) then raise q.FatalException;

  q.OnThreadError := @HandleError;
  q.OnResultsReady:= @DisplayResults;
  q.Start;
end;

procedure TForm1.HandleError(Thread: TThread; E: Exception);

begin
  ShowMessage(E.Message);
end;

procedure TForm1.DisplayResults(ADataset : TDataSet);

begin
  DBGrid1.DataSource.DataSet := ADataset;
  DBGrid1.DataSource.DataSet.First;
  DBGrid1.Show;
  Button1.Caption:= 'Run';
end;

end.

Thread:

Code: [Select]

unit query;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, zConnection, zDataset, db;

type
TExceptionGenerated = procedure(Thread: TThread; E: Exception) of Object; //event for exception and passing the exception message
TResultsReadyEvent  = procedure(AResult: TDataSet) of Object;   //event for results ready

tQuery = class(TThread)

    private
      fQuery   : string;
      fResults : TDataSet;
      EvThreadError  : TExceptionGenerated; //event for internal exception handling
      EvResultsReady : TResultsReadyEvent;  //event for passing results
      procedure GetResults();
      procedure ExcpHandleAtThreadLvl(Thread: TThread; E: Exception);

    protected
      procedure Execute(); override;

    public
      constructor Create(CreateSuspended: boolean; AQuery : string);
      property OnThreadError : TExceptionGenerated read EvThreadError write EvThreadError;  //Outside referral for errors
      property OnResultsReady: TResultsReadyEvent  read EvResultsReady write EvResultsReady; //Outside passing of results
end;

implementation

constructor tQuery.Create(CreateSuspended: boolean; AQuery : string);

begin
  inherited Create(true);

  fQuery := AQuery;

  FreeOnTerminate := True;
  if not CreateSuspended then Start;
end;

procedure tQuery.Execute();

var
  c : TZConnection;
  q : TZQuery;

begin
  c := TZConnection.Create(nil);
  c.Protocol:= 'oracle';
  c.Database:= 'precision';
  c.User    := 'xxx';
  c.Password:= 'xxx';

  q := TZQuery.Create(nil);
  q.Connection := c;
  q.SQL.Text:= fQuery;

  try
  c.Connect;
  q.Open;
  fResults := q;
  Synchronize(@GetResults);
  except on E : Exception do ExcpHandleAtThreadLvl(Self, E);
  end;

end;

procedure tQuery.ExcpHandleAtThreadLvl(Thread: TThread; E: Exception);

begin
  if Assigned(EvThreadError) then EvThreadError(Thread, E);
end;

procedure tQuery.GetResults();

begin
  if Assigned(EvResultsReady) then EvResultsReady(fResults);
end;

end.


Thank you

[Cyrax]

...

Code: [Select]

unit main;
...
   q := tQuery.Create(true, 'select sysdate from dual');
...

You need to use form <ClassType>.Create to create and get instance of the class.

[tudi_x]

Thank you Cyrax.

I have modified the code as below.
q := tQuery.Create(true, 'select sysdate from ') pertains to an incorrect sql statement.
What I want is to get the error back from the database - example 'invalid table name' and pass it as message in procedure TForm1.HandleError(Thread: TThread; E: Exception).

What happens now is that program exits after the sql exception is thrown in except on E : Exception do ExcpHandleAtThreadLvl(Self, E);  .
Please advise.

Code: [Select]

unit main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, DBGrids, query, db;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure Button1Click(Sender: TObject);
    procedure HandleError(Thread: TThread; E: Exception);
    procedure DisplayResults(ADataset : TDataSet);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);

var
  q : tQuery;

begin
  Button1.Caption:= 'Busy';

  q := tQuery.Create(true, 'select sysdate from ');
  if Assigned(q.FatalException) then raise q.FatalException;

  q.OnThreadError := @HandleError;
  q.OnResultsReady:= @DisplayResults;
  q.Start;
end;

procedure TForm1.HandleError(Thread: TThread; E: Exception);

begin
  ShowMessage(E.Message);
end;

procedure TForm1.DisplayResults(ADataset : TDataSet);

begin
  DBGrid1.DataSource.DataSet := ADataset;
  DBGrid1.DataSource.DataSet.First;
  DBGrid1.Show;
  Button1.Caption:= 'Run';
end;

end.


[engkin]

1-The EvThreadError procedure is not called through Synchronize and it is doing GUI work.
2-You do not need to pass the exception variable. Pass (a copy of) the message.
3-Exit the exception block before handling the error code.

[tudi_x]

Thank you engkin.
The below works now - I tried to meet your suggestions.

Code: [Select]

unit query;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, zConnection, zDataset, db;

type
TExceptionGenerated = procedure(AMessage: string) of Object;
TResultsReadyEvent  = procedure(AResult: TDataSet) of Object;   //event for results ready

tQuery = class(TThread)

    private
      fQuery   : string;
      fErrMsg  : string;
      fResults : TDataSet;
      EvThreadError  : TExceptionGenerated; //event for internal exception handling
      EvResultsReady : TResultsReadyEvent;  //event for passing results
      procedure GetResults();
      procedure ExcpHandleAtThreadLvl(Thread: TThread; E: Exception);
      procedure handle_exc();
    protected
      procedure Execute(); override;

    public
      constructor Create(CreateSuspended: boolean; AQuery : string);
      property OnThreadError : TExceptionGenerated read EvThreadError write EvThreadError;  //Outside referral for errors
      property OnResultsReady: TResultsReadyEvent  read EvResultsReady write EvResultsReady; //Outside passing of results
end;

implementation

constructor tQuery.Create(CreateSuspended: boolean; AQuery : string);

begin
  inherited Create(true);

  fQuery := AQuery;

  FreeOnTerminate := True;
  if not CreateSuspended then Start;
end;

procedure tQuery.Execute();

var
  c : TZConnection;
  q : TZQuery;

begin
  c := TZConnection.Create(nil);
  c.Protocol:= 'oracle';
  c.Database:= 'precision';
  c.User    := 'xxx';
  c.Password:= 'xxx';

  q := TZQuery.Create(nil);
  q.Connection := c;
  q.SQL.Text:= fQuery;

  try
  c.Connect;
  q.Open;
  fResults := q;
  Synchronize(@GetResults);
  except on E : Exception do ExcpHandleAtThreadLvl(Self, E);
  end;

end;

procedure tQuery.handle_exc();

begin
  if Assigned(EvThreadError) then EvThreadError(fErrMsg);
end;

procedure tQuery.ExcpHandleAtThreadLvl(Thread: TThread; E: Exception);

begin
  fErrMsg := E.Message;
  Synchronize(@handle_exc);
end;

procedure tQuery.GetResults();

begin
  if Assigned(EvResultsReady) then EvResultsReady(fResults);
end;

end.


[cdbc]

Hi
Please "Do not touch the GUI in secondary threads", just like engkin noted!!!! I noted it too in a former thread of yours.... At least not without Synchronize...
Save the exception somewhere, and retrieve it when the exception block has finnished. Alternatively, set FreeOnTerminate:= false; and investigate the exception before YOU free the thread...
Maybe setup a communication queue between threads and main thread AND protect it with a Critical Section... There are a lot of litterature on the thread AND synchronization subject out there on the internet.
Ex.: http://www.midnightbeach.com/jon/pubs/MsgWaits/MsgWaits.html

edit: I forgot, it might not be a good idea to name your thread "tQuery" when you reference "Db" in your uses clause -> possible name-clash....
Regards Benny