Page 1 of 1
Canceling a query
Posted: Fri 02 Dec 2016 12:44
by 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
Re: Canceling a query
Posted: Mon 05 Dec 2016 13:43
by azyk
Please specify the DBMS version you are using.
Re: Canceling a query
Posted: Thu 08 Dec 2016 11:53
by 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
Re: Canceling a query
Posted: Tue 17 Jan 2017 13:16
by JensFudge
Anything on this subject?
Re: Canceling a query
Posted: Fri 20 Jan 2017 14:32
by azyk
To stop executing SQL query during its execution, you need to call the TUniQuery.BreakExec method in a separate thread.
Re: Canceling a query
Posted: Mon 23 Jan 2017 10:21
by 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
Re: Canceling a query
Posted: Thu 26 Jan 2017 11:19
by 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.
Re: Canceling a query
Posted: Thu 26 Jan 2017 15:57
by 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
Re: Canceling a query
Posted: Fri 27 Jan 2017 09:11
by 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.