How does "cancel query" work in SQL Developer

Question

In SQL Developer, clicking on the Cancel Task button stops the execution of a query immidiately. I have to implement the same functionality in our project.

I am using BC4J as an ORM tool to execute the queries. I have to cancel a search query execution called thorugh view objects that calls several database functions/procedures to get the result.

I have tried using viewObject.cancelQuery(); but it doesn't have any effect; the query keeps executing to the end.

I am connected through JDBC connection pooling, handled by BC4J.

Answer 1

My suggestion is

• When query is submitted
  • block any UI command accept "cancel" (easiest way: use a modal dialog for this purpose, more user convenient: block only local view commands)
  • Start the query in a separate thread different from the UI thread, use a Runnable implementation which
    • has its own cancel() method which
      • calls cancelQuery resp. cancel of your query
      • signal this event to your query runnable using <query thread>.interrupt(); for this purpose you need to store a reference to your query thread in <query thread>. Active IO operations are sometimes interrupted by this signal!
    • is able to cope with InterruptedException and SQLException in run(): if these exceptions are catched rollback transaction (if one is started at all for a read only query)
    • if there are multiple long running statements in this runnable then check Thread.currentThread().isInterrupted() after each statement, cancel() if result is true
• on finishing the query synchronize its results with your UI
• on cancel:
  • call cancel() of your query runnable
  • forget the thread (but be sure not to exhaust your system resources or your connection pool if too many canceled threads still have not yet finished)
  • unblock your UI

There are helper classes in Swing as well as in Eclipse RCP which support this design.