Cancelling a connection or a query while it is being executed

[euthymos]

Dear developers,

I've download MyDAC trial version because I was searching for a MySQL VCL Components suite that allowed me to cancel a connection or a query while it is being executed.

Will you please suggest me how can I allow the user to click a "cancel" button on a form so that the connection attempt is simply cancelled without falling into timeout?


Thank you

[Antaeus]

Meanwhile MyDAC does not support such feature. It will be added to the next (fifth) version of MyDAC. The beta version of MyDAC 5 will be released soon.
Meanwhile you can try to implement such behaviour by executing KILL command for the hanging session from another session.

[euthymos]

Please, explain me how.

[Zagawa]

Also you can try to store in a variable the current connectionid of Mysql with this query .

'select CONNECTION_ID();'

After this, send your query in a thread.

When click on "cancel" button.
Make a new connection on Mysql and send the query :

'kill '+Variable

cya

[Antaeus]

You can use MyConnection.ThreadId instead of execution of 'select CONNECTION_ID();'.

[euthymos]

Also you can try to store in a variable the current connectionid of Mysql with this query .

'select CONNECTION_ID();'

After this, send your query in a thread.

When click on "cancel" button.
Make a new connection on Mysql and send the query :

'kill '+Variable

cya

Please, can you be more precise?

Make a new connection... to which server? Not the same server as the primary connnection, I suppose...
Can you make me a practical example? I really would like to buy your libraries, but my precise objective is to develop this cancel procedure, it's a critical fact.

Thank you.

[Antaeus]

The code may look like the following:

- the code in the addition thread you are reqired to create:

Code: Select all

  MyConnection1.Connect;
  id := MyConnection1.ThreadId;
  MyQuery1.Connection := MyConnection1;
  MyQuery1.SQL.Text := ;
  try
    MyQuery1.Execute;
  except
   // some code to handle Lost Connection error
  end

- the handler of the Cancel button:

Code: Select all

  MyQuery2.Connection := MyConnection2;
  MyQuery2.SQL.Text := 'KILL ' + IntToStr(id);
  MyQuery2.Execute // this will cause the Lost Connection error for MyConnection1

MyConnection1 and MyConnection2 are connected to the same MySQL Server.

[euthymos]

The code may look like the following:

- the code in the addition thread you are reqired to create:

Code: Select all

  MyConnection1.Connect;
  id := MyConnection1.ThreadId;
  MyQuery1.Connection := MyConnection1;
  MyQuery1.SQL.Text := ;
  try
    MyQuery1.Execute;
  except
   // some code to handle Lost Connection error
  end

- the handler of the Cancel button:

Code: Select all

  MyQuery2.Connection := MyConnection2;
  MyQuery2.SQL.Text := 'KILL ' + IntToStr(id);
  MyQuery2.Execute // this will cause the Lost Connection error for MyConnection1

MyConnection1 and MyConnection2 are connected to the same MySQL Server.

OK, this is for aborting a query attempt.

Now, how can I abort a connection attempt (that's what mostly interests me)? Is it possible to use your technique to achieve that effect?

Sorry for bothering you.

Thanks.

[Antaeus]

There is no possibility to break connecting attempt. You can try to decrease value of the ConnectionTimeout property. After the time interval (in seconds) assigned to this property expires, connection attempt is considered unsuccessful.

[euthymos]

I've seen MyDAC 5.00 beta has been released. Does it support this very feature?

Thank you

[Antaeus]

Yes, in MyDAC 5.0 you can invoke the BreakExec method of TMyQuery or TMyCommand from a separate thread to interrupt a long-duration query.

[Volniy_Strelok]

can I use BreakExec in MyScritp?

[Antaeus]

No, currently you cannot use the BreakExec method in TMyScript. This method has a serious bug. We are going to handle this problem in the nearest time. Try to break the script execution in the TMyScript.BeforeExecute event.

[Volniy_Strelok]

Give an example please

[Antaeus]

Here is a small example:

Code: Select all

procedure TForm1.MyScript1BeforeExecute(Sender: TObject; var SQL: String;
  var Omit: Boolean);
begin
  if  then
    Abort;          // stop execution of the script 
  Omit := ;  //False prevents execution of the current statement
end;