How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1");
vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10));
vo.executeQuery();

2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Manages named variables and their values. The bind variable value set by calling VariableValueManager::setVariableValue(...) may reflect across all RowSets if these RowSets have not set the bind variable values by calling RowSet::setNamedWhereClauseParam(...). Let me try to clarify this part. Please note that ADF BC enables each Rowset to have its own copy of bind variables to produce and process multiple row sets based on different combination of bind variable values. If you chose to call yourRowSet.setNamedWhereClauseParam(...), then this takes precedence over VariableValueManager's copy from the parent/owner ViewObject.


What you may need to know about setting values for Bind Variables when used inside a ViewCriteria?


You can assign the bind variable to a view criteria item. In this case, the bind variables allow you to change the values for attributes you will use to filter the view object row set. Please note that the Search Form built using ViewCriteria gets and sets bind variable values from the VariableValueManager of the ViewObject. This means that, if you need to initialize the bind variable values when used inside a ViewCriteria, please call VariableValueManager::setVariableValue(...). The ViewObject.setNamedWhereClauseParam(...) will not help you here.
ViewObject vo = am.findViewObject("EmployeesView1");
VariableValueManager vm = vo.ensureVariableManager();
vm.setVariableValue("bindVarDeptId", deptId);


Learn More ...


There are a lot more points like this. If  you are curious to learn the internals of the ADF Business Components and ADF Binding Layer,  the following book is for you - Oracle ADF Real World Developer’s Guide.
More details about this book can be found in this post- http://jobinesh.blogspot.in/2012/07/pre-order-your-copy-of-oracle-adf-real.html

Comments

  1. We can set bind variable for the view criteria using your first method even if the VO query doesn't include bind variable. Sample code below (Ref: http://www.adftips.com/2010/10/adf-model-programmatically-executing.html):

    ViewObjectImpl empDeptVO = this.getEmpDeptVO();
    //View Criteria with bind variable 'Bind_deptId'
    empDeptVO.setApplyViewCriteriaName("findByDeptId");
    empDeptVO.setNamedWhereClauseParam("Bind_deptId", deptId);
    empDeptVO.executeQuery();

    Please correct me if I'm wrong or missed something. Thanks.

    ReplyDelete
  2. Looks OK for me if you don't want to reflect this changes in secondary rowsets.

    ReplyDelete
  3. Jobinesh,


    Is it possible to set bind variables through bindings? They don't seem to be taking effect on my queries.


    Anyway, so I decided to set it programmatically, but I'm having a hard time getting the view object as to use the "setNamedWhereClauseParam" method. The part that gets the view object seems to be missing from your code, or simply omitted, and would be very helpful to me.


    Thanks!

    ReplyDelete
  4. Daniel,
    I assume that you are looking for a mechanism to execute VO with bind parameter and display the result during page load.

    Easiest approach may be
    1. Define method in your ApplicationModule. get the VO instance here and set the bind variable

    2. Expose the same to client using the 'client interface' option in AM

    3. Drag and drop the above method as method activity on your adfc config or task flow xml. Redesign the navigation such a way that the above said method activity comes as intermediate flow between the page where you start the navigation and the view where you need to display the result

    ReplyDelete
  5. Can you explain this more detail?

    3. Drag and drop the above method as method activity on your adfc config or task flow xml. Redesign the navigation such a way that the above said method activity comes as intermediate flow between the page where you start the navigation and the view where you need to display the result

    ReplyDelete
  6. Hi Jobinesh,

    This is really a good article. I have a requirement where I have to pass list of values or more than one values in where clause parameter. for example "select * from emp where name in ('x','y','x')"
    Is it possible to create a viewCriteria for the same usecase? or run time how do I assign the values?

    Thanks

    ReplyDelete
  7. Saurav
    Pls check out these two posts

    http://jobinesh.blogspot.com/2011/07/tips-on-using-oraclejbodomainarray-as_24.html

    http://jobinesh.blogspot.com/2010/12/using-oraclejbodomainarray-with.html

    ReplyDelete
  8. Thanks Jobinesh,

    Your blog is really very helpful, we got lots of info which we don't know.

    Thanks,
    Saurav

    ReplyDelete
  9. Hi,

    I am trying to create rowSet (Secondary) which should filter out rows that are updated in ViewObject from UI (probably transient attribute updation). I tried to set the quey mode of the viewObject to "ViewObject.QUERY_MODE_SCAN_VIEW_ROWS" and tried executing the query and get the rowSet. But instead of providing the filtered vlaued form the ViewObject, it queries form database and provide me false rowSet as follows,

    ViewObject view = this.getEmp1();
    RowSet rowSet = view.createRowSet(null); //secondary rowSet
    rowSet.setNamedWhereClauseParam("BindFirstName","Jenifer");

    view.setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
    rowSet.executeQuery();
    System.out.println(rowSet.getEstimatedRowCount());

    Instead of me getting 2 in the sop I get 107 as result. But I am able to see my updated records in the ViewObject. Is there a way to get out a filtered secondary row set form the updated transient values.

    Regards,
    Vijai

    ReplyDelete
  10. Vijai,
    Can you try using RowMatch or in memory mode ViewCrtieria

    ReplyDelete
  11. Hi Jobinesh,

    I tried RowMatch and set it to the ViewObject. But it affected the default rowSet also, which changed the results in UI also. But I prefer to filter out values from an updated viewObject and get a separate rowSet which will not affect the UI rowSet (default RowSet). I guess the ViewCriteria also works the same way I suppose.

    Pls give a sample if the above is possible as u suggested.

    Thanks and Regards,
    Vijai

    ReplyDelete
  12. Vijai,
    RowMattch + VC are applied VO level. Not for individual RowSet. What is your use case here?

    ReplyDelete
  13. Hi Jobinesh,

    My useCase is like,
    I have a transient attribute (say a check box) updated ViewObject. I need a secondary RowSet to find the rows that are checked in that viewObject.
    This filtering out should not affect the default RowSet in UI.Since this secondary RowSet is used only for backend processing

    ReplyDelete
  14. Well, here you go ...
    ViewRowSetImpl rowSet = (ViewRowSetImpl)getCountriesView1().createRowSet(null); //secondary rowSet
    ViewCriteria vc = countriesViewImpl.createViewCriteria();
    vc.setCriteriaMode(ViewCriteria.CRITERIA_MODE_CACHE);
    ViewCriteriaRow vcr1 = vc.createViewCriteriaRow();
    vcr1.setAttribute("CountryName", "LIKE A%");
    vc.add(vcr1);
    //Override findByViewCriteriaForViewRowSet in VOImpl and make it public
    RowIterator rowIter =
    countriesViewImpl.findByViewCriteriaForViewRowSet(rowSet, vc, 100, ViewObject.QUERY_MODE_SCAN_VIEW_ROWS,
    null, null);

    ReplyDelete
  15. Hi Jobinesh,

    Great suggestion once again,Thank you very much, I will try this and let you know.

    Thanks,

    ReplyDelete
  16. Hi Jobinesh,

    The way you suggested above worked very well.... thanks a lot....

    Now I have a different suggestion/Clarification that I would like to discuss with you......

    In my named bind variable can I use a groovy script which will get a value from another viewObject like this

    Value: adf.object.applicationModule.findViewObject("managerIDView1").first().getAttribute("ManagerId")

    This is updating the bind variable for the first time.... but even when the viewObject changes at run time its not reflecting in the bind variable.... it fetching records with the value it acquired...

    Any way to make this happen please....

    ReplyDelete
  17. If you use View Criteria, call saveState on VC.

    ReplyDelete
  18. Hi jobinesh,

    Thanks 4 the reply.... But I dont use ViewCriteria and the save state it will save the state right.... But I rather needed to change the state of the bind variable decoratively (without programming) to change the current state of the bind variable.

    ReplyDelete
  19. Hi jobinesh,

    when i drag and drop the view object on the page i need to capture those values and store it in some variable and then pass it to another region.

    please help me.

    is there any way of doing this without using any methods.

    Thanks,
    Hari

    ReplyDelete
  20. Hari
    Using property inspector for task flow, select the following option - 'Share data controls with calling task flow'. This will allow your second task flow to refer same AM instance(in effect same VO instance/EO instance)

    ReplyDelete
  21. Oceans
    Send me a a sample. I'll take a look

    ReplyDelete
  22. hi i am new in ADF. how to pass the variable in vo.
    Example
    Query :
    select * from Employees where employee_id in (123,23,123,123);

    here how to pass the those number in vo.

    ReplyDelete
  23. Mani
    See this post http://jobinesh.blogspot.in/2011/07/tips-on-using-oraclejbodomainarray-as.html

    ReplyDelete
  24. I have one VO which has a bind parameter on it's primary key. Now the requirement is, I need to initialize my VO with different Ids.
    The code I am using is :

    ViewObjectImpl myVO = //some code to get VO instance.
    myVO.setWhereClauseParams(null);
    myVO.setWhereClause(null);
    myVO.setNamedWhereClauseParam("Bind_Id", Id);
    myVO.executeQuery();

    Issue is, first time it gives me the row corresponding to the ID I am passing in this method, but second time I call this method I am not able to get the corresponding row. It gives me the row with older id value.

    I tried keeping
    myVO.resetExecuted();
    dint work.
    The Bind_parameter is also set as updatable in VO.

    What else I can try?

    Thanks in adv.

    ReplyDelete
  25. Try clearing the bind variable that you added previously before setting it again- http://docs.oracle.com/cd/E24382_01/web.1112/e16182/bcquerying.htm#ADFFD19647

    ReplyDelete
  26. I want to achieve the same not programmatically but using pagedef.

    I believe the same can be achieved using executeWithParams and i have seen many examples for executeWithParams using where clause bins variables but i am looking for executeWithParams using view criteria bind variables.

    Please advise.

    ReplyDelete
  27. I have a problem. I tried query with
    vtree bind variable.

    I set variable with this code.

    ViewObjectImpl view = this.getVVenue1();
    VariableValueManager vm = view.ensureVariableManager();
    vm.setVariableValue("Vtree", 84);
    view.executeQuery();

    Everything is ok until I trie to access VVenue1 iterator, I get null exception. Do you have any idea what is wrong. Thanks

    ReplyDelete
  28. Hi ,
    I am setting a where clause programmatically , for that first i setWhereClause(), where the attribute name and the bind variable name is given then I set defineNamedWhereClauseParam() where the bind variable name is defined and I setNamedWhereClauseParam() where the bind variable value is given.
    It is as given in the documentatation: http://docs.oracle.com/cd/E15523_01/web.1111/b31974/bcquerying.htm#CHDEBEAF

    But still its not returning the row.
    Is there anything wrong in what I'm doing ?

    ReplyDelete
  29. nigelonline,
    I assume you are calling executeQuery() on VO after setting required conidtions.What is the query that you see in the console when you enable debug mode. Make sure you follow the same sequence as in this doc
    http://docs.oracle.com/cd/E24382_01/web.1112/e16182/bcquerying.htm#ADFFD19647

    ReplyDelete
  30. Batimba,
    Your code looks fine, can you send me the stack trace.

    ReplyDelete
  31. Hi Jobinesh,

    When i create bind variables for a VO and when i look at the VOImpl.java that has been generated , the getter and setters of few bind variables are like :

    public String getbindTaskType() {
    return (String)ensureVariableManager().getVariableValue("bindTaskType");
    }


    public void setbindTaskType(String value) {
    ensureVariableManager().setVariableValue("bindTaskType", value);
    }

    and for few others its like :

    public Long getbindOrchAppId() {
    return (Long)getNamedWhereClauseParam("bindOrchAppId");
    }

    public void setbindOrchAppId(Long value) {
    setNamedWhereClauseParam("bindOrchAppId", value);
    }

    Why is this difference ?? Please explain

    ReplyDelete

Post a Comment

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of my employer.