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.

Solution

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.

Comments

[Florin Ghita]

@bmorris591 is not a kill. Is a cancel of query. The session do not die.

[APC]

Why does everybody think we telepathic? Instead of saying "it doesn't work" please explain what happens. Do you get an error message? Does it die eventually? Or does the query run to completion?

[Ram Dutt Shukla]

@APC: Well "doesn't work" means calling cancelQuery() not making any affect, the query execution keep going on. Yes, I am connected through JDBC connection pooling handleled by bc4j

[APC]

In future please edit your question to provide us with additional information instead of using comments. The idea is to improve your post. Long trails of comments actually make your question less readable.

[kdureidy]

this will probably answer your question stackoverflow.com/questions/295920/…

[a_horse_with_no_name]

It's probably done through javax.sql.Statement.cancel()