Clarifying Some Bind Variable Confusion

Send me a mail
 Dive into Oracle ADF   Click to see the XML version of this web page.   (Updated: 2/3/2008; 9:24:23 PM.)
Tips and tricks from Steve Muench on Oracle ADF Framework and JDeveloper IDE

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.



© Copyright 2008 Steve Muench. Click here to send an email to the editor of this weblog.
Last update: 2/3/2008; 9:24:23 PM.