3

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.

cancel query iamge

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.

Ram Dutt Shukla
  • 1,351
  • 7
  • 28
  • 55
  • What is the error message you're getting? – Incognito Feb 19 '13 at 12:19
  • I presume it `KILL`s the process directly as it does in MySQL. – Boris the Spider Feb 19 '13 at 12:19
  • 1
    @bmorris591 is not a kill. Is a cancel of query. The session do not die. – Florin Ghita Feb 19 '13 at 12:24
  • 1
    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? – APC Feb 19 '13 at 13:10
  • Also, how are you connecting to the database? Are tyou connecting through discrete SQL*Net connections? Or through shared JBDC connection pooling? – APC Feb 19 '13 at 13:13
  • 1
    It's probably done through `javax.sql.Statement.cancel()` –  Feb 19 '13 at 13:24
  • 1
    @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 – Ram Dutt Shukla Feb 19 '13 at 13:46
  • 1
    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. – APC Feb 19 '13 at 14:04
  • 1
    this will probably answer your question http://stackoverflow.com/questions/295920/how-can-i-abort-a-running-jdbc-transaction – kdureidy Mar 01 '13 at 11:15

1 Answers1

2

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.

Claude
  • 1,724
  • 3
  • 17
  • 46