Anyone familiar with programming in PL/SQL or writing queries in the database is used to working with bind variables with logical names. For example, you might go into SQL*Plus and run a query with named bind variables like this:
SQL> var minsal number
SQL> var maxsal number
SQL> var name varchar2
SQL> begin :minsal := 100; :maxsal := 3000; :name := 'E'; end;
2 / PL/SQL procedure successfully completed. SQL> select empno, ename, :minsal as min_sal, :maxsal as max_sal
2 from emp
3 where sal between :minsal and :maxsal
4 and ename like '%'||upper(:name)||'%'; EMPNO ENAME MIN_SAL MAX_SAL
---------- -------- ---------- ----------
7499 ALLEN 100 3000
7566 JONES 100 3000
7698 BLAKE 100 3000
7844 TURNER 100 3000
7900 JAMES 100 3000
7934 MILLER 100 3000 6 rows selected.
The bind variables has logical names like minsal, maxsal, and name and you can repeat these logical bind variable names any number of times in any order. In PL/SQL it's even nicer since any local variable or program unit formal parameter name can just be freely used inside SQL statements as logical named bind variables, also appears in any order and any number of times.
When you go assign values to the bind variables, as we did above with a little block of PL/SQL executed from the SQL*Plus command line:
SQL> begin :minsal := 100; :maxsal := 3000; :name := 'E'; end;
You assign the named bind variables values once, regardless of what order the names appear in the SQL statement and independent of how many times your SQL statement might require those bind variables to repeat.
In Java-land, developers don't have it so nice, unfortunately. You are forced to use a positional placeholder symbol (?) for bind variables, and then you must set their values positionally. That is, you would have to write the above query like this:
select empno, ename, ? as min_sal, ? as max_sal
from emp
where sal between ? and ?
and ename like '%'||upper(?)||'%'
In order not forget what each ? symbol represents, you might take advantage of SQL comments to remind yourself of their logical meaning. For example, you might write the above query like this:
select empno, ename, ? /* minsal*/ as min_sal, ? /* maxsal */ as max_sal
from emp
where sal between ? /* minsal */ and ? /* maxsal */
and ename like '%'||upper( ? /* name */)||'%'
But at the JDBC level, you are forced to set their values in a positional way like this:
preparedStmt.setLong(1,100); /* minsal in positional 1 */
preparedStmt.setLong(2,3000); /* maxsal in positional 2 */ preparedStmt.setLong(3,100); /* minsal in positional 3 */ preparedStmt.setLong(4,3000); /* maxsal in positional 4 */ preparedStmt.setString(5,"E"); /* name in positional 5 */
Heaven forbid if you need to rework your WHERE clause, which we know is often much more complex than the simple example above. Even though might might still only have the three logical bind variables minsal, maxsal, and name, you will need to change your code since if you've adjusted the positional bind variable order at all.
How utterly unfriendly!
JDBC 3.0 has added support for named parameters in CallableStatement objects, however from my experimentation this appears to only be useful to pass only certain stored procedure parameter values, allowing those not passed by name to take on their default values declared at stored procedure creation time. It is not a general solution to allowing named bind variables in your SQL in the way you might be expecting from SQL and PL/SQL.
In a future release of ADF, we want to help make this a lot easier and restore the ease of use of the PL/SQL and SQL named bind variables to the J2EE developer. One enabling ingredient is provided by our Oracle 10g JDBC driver which, in addition to supporting the above JDBC 3.0 feature, also supports proper named bind variables on the OraclePreparedStatement interface using the set<datatype>AtName() API.
As I'm writing the functional spec and requirements for it this week and the beginning of next, if you are a BC4J/ADF user and have ideas about features you would like to see in our Named Bind Variable support, please feel free to drop me an email with your thoughts about it.
7:17:46 PM 
|
