Canceling a query

[JensFudge]

Hi

After setting a SQL in a TUniQuery and calling open, the user might decide that this is taking too long.
Is it possible to cancel the execution of a TUniQuery, and how?

Best regards
Jens Fudge

[azyk]

Please specify the DBMS version you are using.

[JensFudge]

Currently MS SQlServer, but same question would be valid for all databases that UniQuery supports.

Basically I want:

UniQuery1.open; //The query sends the SQL to the database and starts opening.

The user presses a cancel button, because he decided to not get data after all.
So something like:
UniQuery.CancelOpening;

Thanks

Kind regards
Jens Fudge

[JensFudge]

Anything on this subject?

[azyk]

To stop executing SQL query during its execution, you need to call the TUniQuery.BreakExec method in a separate thread.

[JensFudge]

Thanks.

I have the receiving of data in a separate thread already. Now, I am definitley not a threading expert but would it be a good idea to access the same TUniQuery instance from two seperate threads?
I'll try it out and see how that works...

Thanks for these really great components

Cheers
Jens Fudge

[azyk]

If the TUniQuery.Open method call in a separate thread, TUniQuery.BreakExec call can be made from the main application thread. A sample of such a console application can look like this:

Code: Select all

program UniQuery_CancelOpening;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  ActiveX, Classes, SysUtils, Windows, SQLServerUniProvider, Uni;

type
  TCoolUniQueryThread = class(TThread)
  private
    FConnection: TUniConnection;
    FQuery: TUniQuery;
  public
    procedure Execute;override;
    function CancelOpening:boolean;
    constructor Create;
    destructor Destroy;override;
  end;

function TCoolUniQueryThread.CancelOpening:boolean;
begin
  Result := not FQuery.Fetched;
  if Result then FQuery.BreakExec;
end;

constructor TCoolUniQueryThread.Create;
begin
  inherited Create(True);

  FConnection := TUniConnection.Create(nil);
  FConnection.ProviderName := 'SQL Server';
  FConnection.Server   := '********';
  FConnection.Database := '********';
  FConnection.Username := '********';
  FConnection.Password := '********';

  FQuery := TUniQuery.Create(nil);
  FQuery.Connection := FConnection;
  FQuery.SQL.Text := 'select * from big_table';
end;

destructor TCoolUniQueryThread.Destroy;
begin
  FQuery.Free;
  FConnection.Free;
  inherited;
end;

procedure TCoolUniQueryThread.Execute;
begin
  inherited;
  CoInitialize(nil);
  FQuery.Open;
  CoUninitialize;
end;

var
  QueryThread: TCoolUniQueryThread;
begin
  CoInitialize(nil);
  QueryThread := TCoolUniQueryThread.Create;

  Writeln('Start query');
  QueryThread.Resume;

  Writeln('Press Enter to cancel query');
  Readln;

  if QueryThread.CancelOpening then
    Writeln('Canceling query...')
  else
    Writeln('Can''t cancel query, allready finished');

  WaitForSingleObject(QueryThread.Handle, INFINITE);
  QueryThread.Free;
  Writeln('Finish query');
  Readln;
end.

[JensFudge]

Now thats what I call a perfect answer. Thanks a lot!!

The answer you provided is for Windows only, as it uses ActiveX (CoInitialize), I assume it's also possible in the FireMoney framework?

Kind regards
Jens

[azyk]

COM library initialization in a separate thread is required only for the providers using OLE DB interface - standard OLEDB provider or SQL Native Client provider (the 'prSQL' or 'prNativeClient' values of the TUniConnection.SpecificOptions.Values ['SQL Server.Provider'] property).

If you are using Direct mode( 'prDirect' ), then in the above sample you can delete the ActiveX unit from uses and all the CoInitialize and CoUninitialize calls.