How can I force closing a connection which is taking to long to response
How can I force closing a connection which is taking to long to response
Hello,
I have some particular situations when a connection is waiting to long to get the sql response (a bad design query for example).
I want to know if there is some way to force closing it and return the control to the main application.
Thank you.
I have some particular situations when a connection is waiting to long to get the sql response (a bad design query for example).
I want to know if there is some way to force closing it and return the control to the main application.
Thank you.
Re: How can I force closing a connection which is taking to long to response
You can try setting the maximum timeout for the command execution. For TMSQuery, TMSTable, and TMSStoredProc, you can set the timeout in seconds by specifying a value in the CommandTimeout property.
More details about the CommandTimeout property:
https://www.devart.com/sdac/docs/devart ... imeout.htm
More details about the CommandTimeout property:
https://www.devart.com/sdac/docs/devart ... imeout.htm
Re: How can I force closing a connection which is taking to long to response
Thank you for your quick response.
Unfortunately, this solution doesn't address all situations.
Let me explain:
I have a query that takes ~5' to execute and is fine, but I can put some filters on that query which reduce the time to less than a minute. It happens very often that an user forget to put the necessary filters and realize that just after pressing the button and he has to wait the 5' (or more) to finish the query, so the Timeout is not an option here.
And there are similar situations like this.
Therefor I need a "panic button" to stop the execution, closing the connection.
Unfortunately, this solution doesn't address all situations.
Let me explain:
I have a query that takes ~5' to execute and is fine, but I can put some filters on that query which reduce the time to less than a minute. It happens very often that an user forget to put the necessary filters and realize that just after pressing the button and he has to wait the 5' (or more) to finish the query, so the Timeout is not an option here.
And there are similar situations like this.
Therefor I need a "panic button" to stop the execution, closing the connection.
Re: How can I force closing a connection which is taking to long to response
You can try executing the query in a separate thread, and when the thread is terminated, stop the query execution by calling the BreakExec method of TMSQuery . For example, the code for creating a thread:
Code: Select all
type
TRunTask = class(TThread)
...
protected
procedure Execute; override;
procedure TerminatedSet; override;
end;
implementation
procedure TRunTask.Execute;
begin
inherited;
MSQuery1.Open;
end;
procedure TRunTask.TerminatedSet;
begin
inherited;
MSQuery1.BreakExec;
end;
Last edited by Stellar on Wed 25 Apr 2018 11:15, edited 1 time in total.
Re: How can I force closing a connection which is taking to long to response
Hello,
It works!
Thank you.
It works!
Thank you.
Re: How can I force closing a connection which is taking to long to response
Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.
Feel free to contact us if you have any further questions about our products.
Re: How can I force closing a connection which is taking to long to response
Hi again,
As the solution works as described, I couldn't find a way to implement it in an n-tier application.
I've tried to call BreakExec through a method from the application server in 2 ways:
1. directly through a procedure from the same thread, but raise "Interface not supported".
2. marshaling the application server interface pointer to the main thread (by creating a global interface pointer ), but it seems COM serialize the calls, meaning it waits until the query finishes and after that execute BreakExec method.
Any idea helps me a lot.
Thank you
As the solution works as described, I couldn't find a way to implement it in an n-tier application.
I've tried to call BreakExec through a method from the application server in 2 ways:
1. directly through a procedure from the same thread, but raise "Interface not supported".
2. marshaling the application server interface pointer to the main thread (by creating a global interface pointer ), but it seems COM serialize the calls, meaning it waits until the query finishes and after that execute BreakExec method.
Any idea helps me a lot.
Thank you
Re: How can I force closing a connection which is taking to long to response
We provided you with a sample of how to stop the query execution. Please modify this sample, in order for it to reproduce the issue.
Then please send this project to us, so that we can analyze it and give our recommendations.
You can send the sample using the contact form at our site: devart.com/company/contactform.html
Then please send this project to us, so that we can analyze it and give our recommendations.
You can send the sample using the contact form at our site: devart.com/company/contactform.html
-
- Posts: 11
- Joined: Mon 29 Apr 2013 18:59
Re: How can I force closing a connection which is taking to long to response
use option nonblocking
Set the NonBlocking option to True to fetch rows in a separate thread. The BeforeFetch event is called in the additional thread context that performs data fetching. This event is called every time on the Fetch method call. The AfterFetch event is called in the main thread context only once after fetching is completely finished.
In the NonBlocking mode as well as in the FetchAll=False mode an extra connection is created. When setting TCustomMSDataSet.Options.NonBlocking to True, you should keep in mind that execution of such queries blocks the current session. In order to avoid blocking, OLE DB creates an additional session as in the TCustomMSDataSet.FetchAll = False mode. It causes the same problems as in the TCustomMSDataSet.FetchAll = False mode. This problem can be solved by using MARS (TMSConnectionOptions.MultipleActiveResultSets = True). The current session is not blocked and OLE DB is not required to create addition session to run a query. MARS is supported since SQL Server 2005 if SQL Native Client is used as OLE DB provider.
Set the NonBlocking option to True to fetch rows in a separate thread. The BeforeFetch event is called in the additional thread context that performs data fetching. This event is called every time on the Fetch method call. The AfterFetch event is called in the main thread context only once after fetching is completely finished.
In the NonBlocking mode as well as in the FetchAll=False mode an extra connection is created. When setting TCustomMSDataSet.Options.NonBlocking to True, you should keep in mind that execution of such queries blocks the current session. In order to avoid blocking, OLE DB creates an additional session as in the TCustomMSDataSet.FetchAll = False mode. It causes the same problems as in the TCustomMSDataSet.FetchAll = False mode. This problem can be solved by using MARS (TMSConnectionOptions.MultipleActiveResultSets = True). The current session is not blocked and OLE DB is not required to create addition session to run a query. MARS is supported since SQL Server 2005 if SQL Native Client is used as OLE DB provider.
Re: How can I force closing a connection which is taking to long to response
Thank you for your info.
I've tried setting the options in both manners, but is the same result, ie not working; either raise exception "Interface not supported", either is waiting for the first query to finished.
I can send you the sample project to see where I am faulting something.
I've tried setting the options in both manners, but is the same result, ie not working; either raise exception "Interface not supported", either is waiting for the first query to finished.
I can send you the sample project to see where I am faulting something.
Re: How can I force closing a connection which is taking to long to response
The issue is that the DataSnap server does not process the next query from the DataSnap client until it completes the previous operation. This behavior is a DataSnap specificity and does not depend on using SDAC.
You can address the following question to Embarcadero: how to process the next request from the DataSnap client before completing the previous operation on the DataSnap server.
You can address the following question to Embarcadero: how to process the next request from the DataSnap client before completing the previous operation on the DataSnap server.
Re: How can I force closing a connection which is taking to long to response
Unfortunately, nobody answered the issue posted in Embarcadero forums... or I didn't encounter the place know where to raise it.
Therefore, if you have a closer connection with Embarcadero's guys, please raise for me.
Thank you.
Therefore, if you have a closer connection with Embarcadero's guys, please raise for me.
Thank you.
Re: How can I force closing a connection which is taking to long to response
Unfortunately, we cannot influence the support of Embarcadero should help you with your question.