Abort the execution of a long-term SQL

Asked

Viewed 255 times

3

I have a very time consuming SQL (more than 10 minutes) to return the data.
However, he is executed in a Thread and therefore the Mainthread continues to run normally. While SQL is running, the user can shut down the system, and there is the problem.
Because the system even closes, but the process keeps running in the task manager until SQL returns the requested data.

You can abort SQL execution immediately?

Dice:

  • Delphi XE 7
  • Firedac
  • Firebird
  • No use changing SQL to get faster, it is slow and not has to get away from it.
delphi firedac

  • Mythread.Terminate; in theory would end, but I doubt it will end because the process is 'stuck' in the bank.

  • 1

    Exactly, the problem is not in closing Thread, but in the fact that it is stuck in the bank.

  • I don’t see how. Except by changing the logic. Sort by selecting gradually. So you don’t let the program close when you’re in the bank, knowing that it will come back soon and then decide whether to continue or not.

  • Note that just like your application, SQL Server also has control of processes and threads. I strongly believe there is stored procedure that you can use in the bank to kill a process there. The business is to discover the process id of the query in the bank...

  • Exactly. I thought about Kill( id ), but...

  • I forgot to mention the database: Firebird

  • 2

    It got worse. There is no Kill in Firebird. It does not treat this issue per user killed one, kills all.

  • Create a method (e.g.: FinalizarProcesso) in your thread class with SQL. In it, call TerminateProcess(GetCurrentProcess, 0);. Then, from the main thread you call this thread method with SQL, then call the TerminateProcess(GetCurrentProcess, 0); in the main thread itself. By the way, I think that in the main thread there is no need, just finish normally with the Application.Terminate.

Show 3 more comments

1 answer

1

Here is a suggestion: Create a table structure in your database or create a folder to receive text files with XML/Json content.

After creating the structure above, instead of making a thread and performing the SQL query on this thread, do the following:

Create a Webservice or DLL to perform the method call with SQL, in this method generate an ID and return to your caller. Still inside your WS/COM run the query and after the execution, create an XML/JSON with the data and save it in a database or folder informing the previously informed ID. Example: 89.txt(If saved in folder).

Now it is possible to create a thread that consults the/BD folder from time to time to check if that ID has already been generated and thus present the result in your application.

If your application has been terminated, you will not have any processing pending in process management.

I hope the idea is clear.

Browser other questions tagged delphi firedac

You are not signed in. Login or sign up in order to post.