Friday, January 13, 2012

How to cancel a long running query from the UI (part 1)

I've long wondered if there was any way to cancel a query that is taking too long to complete from the page where the query is being run, similar to hitting the cancel button in SQL Developer.   I noticed that the view object class has a cancelQuery() method on it but how do you call that when the thread is busy running the query.   The javadoc on the cancelQuery function says it can be called from another thread and this is what happens when you set a timeout value on the view object.   ADF spawns a monitor thread that will cancel the query after the timeout elapses.  I did some experimenting and found that if I overrode the executeQuery method in the view and saved the view object on the session I could then use that to cancel the query from another task flow running in another tab or browser window.
   Here is the code
    @Override
    public void executeQuery() {

        Map sessionScope = 
            ADFContext.getCurrent().getSessionScope();
        sessionScope.put("MyQuery",this);
        try{
        super.executeQuery();
        } finally{
          // only keep on the Session 
          //for the duration of the query  
          sessionScope.remove("MyQuery");
       }

On a different page put a button for canceling the query that calls this action method
  
public void cancelQueries(ActionEvent actionEvent) {
    Map sessionScope = ADFContext.getCurrent().getSessionScope();
    ViewObjectImpl vo = (ViewObjectImpl)sessionScope.get("MyQuery");
    if (vo!=null){
        if (vo.isDead()==false){
          vo.cancelQuery();        

        }
      sessionScope.remove("MyQuery");
    }

}
          
This will cause the query to throw a "JBO-27126 Long running query has been canceled" Exception.
In order to prevent the page from becoming broken you must catch this exception in the executeQueryForCollection method and re-throw a new JboException which will be displayed to the user.   See this blog for an example of that.


This seems to work fine, at least on 11G database.  According to this blog there my be problems making it work with the 10G drivers.  The next question is how would I put a button on the same browser tab as the one running the query.   I haven't implemented that but I can think of a couple of ways that might be done.  One would be to have the button call a javascript method that makes a custom AJAX call back to a plain old servlet that retrieves the users session and calls the cancelQuery method.  Another might be to add an inline frame that has a URL to a different task flow and page containing only the single button.  ADF doesn't let you submit a button on the same page until the page is no longer busy but using an inline frame is the same as running the task flow on another tab so concurrent requests could be made. 



2 comments:

  1. Don. Sad to say this is a little dodgy as a technique. The ViewObject Instance is not serializable so you may run into trouble with this in a cluster. It also feels wrong to be reaching out to the UI layer session from within the Service layer.
    That being said I tried to do this the "correct" way last night, where I created a servlet that correctly grabs the binding context and then issues the cancel from an AJAX call kicked off from JavaScript. However, that does not work, perhaps not surprisingly because the binding filter ensures only single threaded access. In a fit of enthusiasm I also created a separate thread in the service layer to see if we could cancel via a JMS message or similar but this was blocked to.
    Maybe you should stick with the timeout mechanism?

    ReplyDelete
  2. Thanks for your response! That's too bad there isn't something built in to do this. Sometimes in the search interface we click the search button and then realize that we should have added some different criteria but have to wait a long time before the results come back to try again. It would be real nice to be able to cancel it.

    I'm guessing that when ADF times out a query it calls the same cancelQuery method from another thread so I think it would at least be thread safe. Maybe some sort of JMX mbean interface could be created that would route the cancel request to the correct server to get around the Clustering problem. At any rate I think it's a valid use case looking for a solution.

    ReplyDelete