How can I force closing a connection which is taking to long to response

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

How can I force closing a connection which is taking to long to response

Post by radub » Thu 19 Apr 2018 05:51

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.

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: How can I force closing a connection which is taking to long to response

Post by Stellar » Thu 19 Apr 2018 13:40

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

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: How can I force closing a connection which is taking to long to response

Post by radub » Thu 19 Apr 2018 13:54

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.

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: How can I force closing a connection which is taking to long to response

Post by Stellar » Mon 23 Apr 2018 13:19

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.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: How can I force closing a connection which is taking to long to response

Post by radub » Wed 25 Apr 2018 08:17

Hello,

It works!
Thank you.

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: How can I force closing a connection which is taking to long to response

Post by Stellar » Wed 25 Apr 2018 11:14

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: How can I force closing a connection which is taking to long to response

Post by radub » Sat 05 May 2018 14:26

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

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: How can I force closing a connection which is taking to long to response

Post by Stellar » Mon 07 May 2018 12:33

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

wilton_radinfo
Posts: 11
Joined: Mon 29 Apr 2013 18:59

Re: How can I force closing a connection which is taking to long to response

Post by wilton_radinfo » Fri 01 Jun 2018 12:57

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.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: How can I force closing a connection which is taking to long to response

Post by radub » Sat 02 Jun 2018 08:05

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.

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: How can I force closing a connection which is taking to long to response

Post by Stellar » Thu 07 Jun 2018 08:50

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.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: How can I force closing a connection which is taking to long to response

Post by radub » Sat 16 Jun 2018 12:55

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.

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: How can I force closing a connection which is taking to long to response

Post by Stellar » Wed 11 Jul 2018 12:31

Unfortunately, we cannot influence the support of Embarcadero should help you with your question.

Post Reply