?
Solved

Interrupting the Query?

Posted on 2000-04-26
14
Medium Priority
?
361 Views
Last Modified: 2010-04-04
Is it possible to interrupt the Query?
[while the query is running, I have to stop it.sample code desirable]Experts please...
0
Comment
Question by:veeramani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +3
14 Comments
 
LVL 3

Expert Comment

by:Stefaan
ID: 2754299
Hi,

Could you provide us with some more information as to what DB you are working on ?

Best regards,

Stefaan

0
 
LVL 3

Expert Comment

by:shenqw
ID: 2754340
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.
0
 
LVL 3

Expert Comment

by:shenqw
ID: 2754367
in v$session,the fields:USERNAME,SCHEMANAME,OSUSER,MACHINE,TERMINAL,PROGRAM can help you decide which session you want you kill.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Lischke
ID: 2754583
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
0
 
LVL 2

Expert Comment

by:florisb
ID: 2754696
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.
0
 
LVL 1

Author Comment

by:veeramani
ID: 2754758
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.
0
 
LVL 10

Expert Comment

by:Lischke
ID: 2754784
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
0
 
LVL 3

Expert Comment

by:shenqw
ID: 2754785
I think ,If you kill the session ,all resource of the session also should be released,include the running query.
0
 

Expert Comment

by:Volk
ID: 2754817
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.
0
 
LVL 10

Expert Comment

by:Lischke
ID: 2754837
This is on the server side, how do you want to check variables within a query?

Ciao, Mike
0
 
LVL 1

Author Comment

by:veeramani
ID: 2754874

How to create a thread and how to assign the particular query to that thread and how to terminate the particular thread?
0
 
LVL 10

Accepted Solution

by:
Lischke earned 800 total points
ID: 2754926
That's quite easy. Say you have two buttons. One creates the query and the other one kills it:

type
  TQueryThread = class(TThread)
  private
    FQueryString: String;
  public
    procedure Execute;
  end;

  TMainForm = class(TForm)
    procedure OnQueryTermination(Sender: TObject);
  private
    :
    :
 
  end;

var
  QueryThread: TQueryThread;

procedure TMainForm.Button1Click(Sender: TObject);

begin
  QueryThread := TQueryThread.Create(True);
  with QueryThread do
  begin
    FreeOnTerminate := True;
    OnTerminate := OnQueryTermination;
    FQueryString := MyQueryString;
    Resume;
  end;
end;

procedure TMainForm.Button1Click(Sender: TObject);

var
  ExitCode: Cardinal;

begin
  if Assigned(QueryThread) then TerminateThread(QueryThread.Handle, ExitCode);
end;

procedure OnQueryTermination(Sender: TObject);

begin
  QueryThread := nil; // thread memory will be freed automatically (FreeOnTerminat = True)
end;

procedure TQueryThread.Execute;

begin
  // pseudo code
  CreateQueryFromString;
  Query.ExecSQL; or Query.Open;
  // nothing more to consider here
end;

Ciao, Mike
0
 
LVL 10

Expert Comment

by:Lischke
ID: 2754951
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(Sender: 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(QueryThread.Handle, 500) = WAIT_TIMEOUT then
      TerminateThread(QueryThread.Handle, ExitCode);
  end;
end;

Ciao, Mike
0
 
LVL 1

Author Comment

by:veeramani
ID: 2754997

Very good, thank you very much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question