veeramani
asked on
Interrupting the Query?
Is it possible to interrupt the Query?
[while the query is running, I have to stop it.sample code desirable]Experts please...
[while the query is running, I have to stop it.sample code desirable]Experts please...
If you use Oralce:
select sid,serial#,status from v$session;
alter system kill session 'sid,serial';
you use first sql,you can get the session's sid ,serial,status. The second sql,you can kill the session .release all resources that the session uses.the 'sid,serial' are replaced by you get from the first sql result.
Good Luck. shenqw.
select sid,serial#,status from v$session;
alter system kill session 'sid,serial';
you use first sql,you can get the session's sid ,serial,status. The second sql,you can kill the session .release all resources that the session uses.the 'sid,serial' are replaced by you get from the first sql result.
Good Luck. shenqw.
in v$session,the fields:USERNAME,SCHEMANAME ,OSUSER,MA CHINE,TERM INAL,PROGR AM can help you decide which session you want you kill.
If I'm not totally wrong then veeramani wants to stop an already (and long) running query using code not to kill sessions and such...
If I'm right then I have bad news. No, it is usually not possible a running query to stop. On some RDBMS like Interbase you can set a query timeout value to cause the server to abort the query if it runs longer than the timeout value but this is not supported on all DBMS. You can kill the thread which started the query but this is probably only to be used as last ressort...
Ciao, Mike
If I'm right then I have bad news. No, it is usually not possible a running query to stop. On some RDBMS like Interbase you can set a query timeout value to cause the server to abort the query if it runs longer than the timeout value but this is not supported on all DBMS. You can kill the thread which started the query but this is probably only to be used as last ressort...
Ciao, Mike
Isn't possible to give the database some kind of interface to call a function that updates some kind of process-indicator (0..100%)?
F.
F.
ASKER
I appreciate Lischke comment and that's what i am expecting for ,but how to kill the thread which started the query? If i kill the thread, does it disconnect the database?. I am using AS/400 as my backend, IDCO400 as my driver.
I said killing the thread is only as last ressort because it will only allow your application to continue. The query (which runs on the server) is still executed until the server realizes that it lost the conncetion to the client.
The perfect solution would be to notify the server to abort the query but to my knowledge this is not supported (although it wouldn't be that hard because all DBMSs are multi threaded).
On the client side you can use TerminateThread to kill a blocking thread regardless of its state. This requires of course that you have executed your query in a separate thread (see TThread, use TThread.Handle as handle for TerminateThread).
Ciao, Mike
The perfect solution would be to notify the server to abort the query but to my knowledge this is not supported (although it wouldn't be that hard because all DBMSs are multi threaded).
On the client side you can use TerminateThread to kill a blocking thread regardless of its state. This requires of course that you have executed your query in a separate thread (see TThread, use TThread.Handle as handle for TerminateThread).
Ciao, Mike
I think ,If you kill the session ,all resource of the session also should be released,include the running query.
Isn't it possible to communicate with a running query (depending on the DBMS) through a kind of global variable? Just let the query/procedure check for some kind of stop condition at regular intervals.
This is on the server side, how do you want to check variables within a query?
Ciao, Mike
Ciao, Mike
ASKER
How to create a thread and how to assign the particular query to that thread and how to terminate the particular thread?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are some typos in the text, sorry, but I think you will easily correct them :-)=
For better management you can do a limited WaitForSingleObject before killing the thread:
procedure TMainForm.Button2Click(Sen der: TObject);
var
ExitCode: Cardinal;
begin
if Assigned(QueryThread) then
begin
QueryThread.Terminate;
// don't call WaitFor here as this waits unlimited time
// wait 500ms...
if WaitForSingleObject(QueryT hread.Hand le, 500) = WAIT_TIMEOUT then
TerminateThread(QueryThrea d.Handle, ExitCode);
end;
end;
Ciao, Mike
For better management you can do a limited WaitForSingleObject before killing the thread:
procedure TMainForm.Button2Click(Sen
var
ExitCode: Cardinal;
begin
if Assigned(QueryThread) then
begin
QueryThread.Terminate;
// don't call WaitFor here as this waits unlimited time
// wait 500ms...
if WaitForSingleObject(QueryT
TerminateThread(QueryThrea
end;
end;
Ciao, Mike
ASKER
Very good, thank you very much
Could you provide us with some more information as to what DB you are working on ?
Best regards,
Stefaan