Skip to main content

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

Anonymous said…
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.
Jobinesh said…
Looks OK for me if you don't want to reflect this changes in secondary rowsets.
Daniel said…
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!
Jobinesh said…
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
Anonymous said…
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
saurav said…
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
Jobinesh said…
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
saurav said…
Thanks Jobinesh,

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

Thanks,
Saurav
oceans said…
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
Jobinesh said…
Vijai,
Can you try using RowMatch or in memory mode ViewCrtieria
oceans said…
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
Jobinesh said…
Vijai,
RowMattch + VC are applied VO level. Not for individual RowSet. What is your use case here?
oceans said…
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
Jobinesh said…
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);
oceans said…
Hi Jobinesh,

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

Thanks,
oceans said…
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....
Jobinesh said…
If you use View Criteria, call saveState on VC.
oceans said…
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.
hari said…
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
Jobinesh said…
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)
Jobinesh said…
Oceans
Send me a a sample. I'll take a look
Mani R said…
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.
Jobinesh said…
Mani
See this post http://jobinesh.blogspot.in/2011/07/tips-on-using-oraclejbodomainarray-as.html
Dishi Jain said…
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.
Jobinesh said…
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
Anonymous said…
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.
Batimba said…
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
nigelonline said…
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 ?
Jobinesh said…
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
Jobinesh said…
Batimba,
Your code looks fine, can you send me the stack trace.
sai goutham said…
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
Sumit Yadav said…
hi JObinesh,
I have learnt a lot from your blogs . So thank you.

i have a requirement that in af:query if a checkbox (a transient attribute) is not checked .
I have to filter the records whose endDate are equal or greater than sysDate.
how can i achieve it using a query listener .
Any pointers

Thanks
Sumit

Popular posts from this blog

Happy New Year 2018 !

We can't go back and change the beginning, but we always can start where we are and change the ending. Believe in yourself and you will be unstoppable!

Wishing you and your family a very happy new year 2018 !!!