Substitution and Bind Variables

What exactly are substitution and bind variables?

Substitution and input bind variables are both methods for replacing a variable in a query or procedural code with a corresponding value from your program.

Example query:

        select
                first_name,
                middle_initial,
                last_name
        from
                $(schema).people
        where
                person_id=:id
                and
                age>=:youngage
                and
                age<=:oldage

In this query, $(schema) is a substitution variable and :id, :youngage and :oldage are input bind variables.

Output bind variables allow values to be passed from procedural code into buffers in your program.

Example procedural code:

        BEGIN
                :returnval:=100*50;
        END;

In this code, :returnval is an output bind variable.

Substitution variables are processed first, by the API. Input bind variables are processed second, by the underlying RDBMS or by the connection daemon in the event that the RDBMS doesn't support bind variables. Output bind variables are processed by the RDBMS as the query or procedural code is executed.

Substitution variables may be anything and appear anywhere in the query. They are frequently used to ammend where clauses with additional constraints and specify schemas. A substitution value may even contain bind variables.

Bind Variable Syntax

Different RDBMS's have different syntax for bind variables. Oracle bind variables are names preceeded by a colon. In DB2 and Interbase, bind variables are represented by question marks.

When using SQL Relay bind functions, to refer to an Oracle bind variable, you should use it's name without the preceeding colon. To refer to a DB2 or Interbase bind variable, you should use it's position number.

For example...

        select
                first_name,
                middle_initial,
                last_name
        from
                $(schema).people
        where
                person_id=:id
                and
                age>=:youngage
                and
                age<=:oldage

In this Oracle syntax query, you should use "id", "youngage" and "oldage" as variable names in the inputBind functions.

        select
                first_name,
                middle_initial,
                last_name
        from
                $(schema).people
        where
                person_id=?
                and
                age>=?
                and
                age<=?

In this DB2 or Interbase syntax query, you should use "0", "1" and "2" as variable names in the inputBind functions.

Why should I use input bind variables instead of just using substitution variables for everything?

To improve performance and because output bind variables allow values to be passed from procedural code into buffers in your program.

RDBMS's which support bind variables parse the query then plug input bind variables into the already parsed code. If the same query is run a bunch of times, even with different values for the input bind variables, the RDBMS will have the code cached and won't have to parse the query again. If you don't use input bind variables, the RDBMS will parse the query each time because the where clause will be slightly different each time and the code for all those slightly different queries will clog the cache.

Generally speaking, you should use input bind variables for substitutions in the where clause if you can.

What if my RDBMS doesn't support bind variables?

Use substitution variables instead, the bind variable API calls will be useless to you.