Tuesday, January 24, 2012

Executing iterator binding in deferred Mode when there are required bind variable parameters

An iterator binding can be set to execute in "deferred" mode.  This is the default behavior if you don't change it.   What this means is that the query will not execute unless the page needs to display it's data.   This is great for use on a page that doesn't initially display the data to the user, like in a popup or tab that is not initially disclosed.  If the popup is displayed or the tab is disclosed, then and only then will the query be executed.  Also for tables that are set to lazy load (retrieve it's data after the page initially loads) this prevents the query from executing until the page is first displayed and the "Fetching data..."  message is displayed over the table.  A problem arises when the view object requires named bind variables.  The bind variables do not get set unless you add an ExecuteWithParams action binding to the task flow.   Calling an ExecuteWithParams will execute the query but that defeats the value of setting the iterator binding to "deferred"   So how do I set the bind variables without executing the query?

Here is one technique:
  • Edit the View object, select the Java panel and check the "Generate View Object Class" and "Include bind_variable accessors.
  • Edit the Client Interface and add the setMyValueBind()  method to the interface.
  • Select the Data Control panel and click the refresh icon to refresh the data controls.
  • Find your view object in the data controls panel and expand it.  You should see the setMyValueBind method under the list of attributes.
  • Drag the method call over onto your task flow and add an EL expression to set the bind variables value.
  • insert the method call into the chain of operations that execute prior to displaying the page.
That is all that is needed.  The first time the data is displayed, the iterator binding will execute the query using the set bind parameters and if the user never displays the popup or tab that displays the data, the query will not need to be executed.

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.