Clarifying Some Bind Variable Confusion
Your view object can use either Oracle-style bind variables, or JDBC-style. You can set the style on the Query panel of the View Object editor.
JDBC-style is a little slower to process when using the Oracle driver, and it uses the JDBC standard bind variable place holder character, the ?. The Oracle-style is faster, but only supported when using the Oracle JDBC driver. It's faster because the first thing the Oracle JDBC driver does internally with ?-style bind variables is convert them to Oracle-style. Using Oracle-style, you avoid this step. Oracle-style uses bind variables that have placeholder names like :NNN where NNN is some positive integer. In reality, bind variables should be allowed to be of the form :AnyNameYouLike, but a long-standing bug in the Oracle JDBC driver prevents bind variables other than the :NNN style from working predictably. So let's stick with what works. With the ? notation, it's clear that you have to set the bind variables by the position number that they appear in the query. Heck, they have no names. What else could you do?
With Oracle style, while it's not obvious to many, the exact same rule holds. Just ignore the bind variable names, and treat them as if they were positional. You can chose the bind variables to be named :0, :1, :2, etc., but this is just a human affordance to remember which comes in what order. At runtime, the driver just processes them in sequential order, regardless of the name. Due to other bugs in the JDBC driver that I believe are still there, it's also best not to repeat the same bind variable name in the query to have the most predictable behavior. If semantically you need the same value to be bound several times, it's safest to just assign each bind variable a unique name, then bind the same value to all of the bind variable slot-numbers that need to have the same value.
The follow example shows that the names do not matter. Assume you create an expert-mode view object with the following query:
select :54 as first, :2 as second, :17 as third, :2 as fourth from dual where to_number(:11) = to_number(:99)
And take the following sample program that tests an instance of this TestQuery view object.
package test; import oracle.jbo.client.Configuration; import oracle.jbo.*; public class TestClient { public static void main(String[] args) { String _am = "test.TestModule", _cf = "TestModuleLocal"; ApplicationModule am = Configuration.createRootApplicationModule(_am,_cf); ViewObject vo = am.findViewObject("TestQuery"); vo.setWhereClauseParam(0,"one"); // bindvar :54, first in query vo.setWhereClauseParam(1,"two"); // bindvar :2, second in query vo.setWhereClauseParam(2,"three"); // bindvar :17, third in query vo.setWhereClauseParam(3,"four"); // bindvar :2, fourth in query vo.setWhereClauseParam(4,"1.0"); // bindvar :11, fifth in query vo.setWhereClauseParam(5,"1.000"); // bindvar :99, sixth in query vo.executeQuery(); Row result = vo.first(); System.out.println(result.getAttribute("First")); System.out.println(result.getAttribute("Second")); System.out.println(result.getAttribute("Third")); System.out.println(result.getAttribute("Fourth")); vo.setWhereClauseParams(new Object[]{"A", // bindvar :54, first in query "B", // bindvar :2, second in query "C", // bindvar :17, third in query "D", // bindvar :2, fourth in query "1.0", // bindvar :11, fifth in query "1.000"}); // bindvar :99, sixth in query vo.executeQuery(); result = vo.first(); System.out.println(result.getAttribute("First")); System.out.println(result.getAttribute("Second")); System.out.println(result.getAttribute("Third")); System.out.println(result.getAttribute("Fourth")); Configuration.releaseRootApplicationModule(am,true); } }
The result produced is the following:
one two three four A B C D
Note the positional bind variable number is zero-based in BC4J, and as the example illustrates, you can either set one bind variable at a time, or all of them at once using an array.
After reading this explanation, you might ask yourself, "Does this mean that if my query uses the same logical parameter 5 times that I have to bind all 5 occurences?" The answer is yes, and that you should not repeat the same :1-style bind variable name. You would have to use :1, :2, :3, :4, and :5, and bind the same value to it 5 times. This answer might further cause you to wonder, "If the VO developer changes the query later to have a sixth occurence, would all users of the VO will have to add a sixth setWhereClauseParam?" The answer is yes, unless as the developer of the VO you write some code to provide a default value for that new bind variable.
|