Using bind values in database functions

For information about the availability and usage of the bind facility for your specific database and platform, see the database-specific adapter reference guides.

When the Database Management System (DBMS) receives an SQL request, the request is cached because many applications repeatedly issue the same SQL statement. If the SQL statement differs from one that the DBMS has recently processed, the statement is reevaluated. (The DBMS performs parsing, derives an execution plan, and so on.)

Similarly, if a DBLOOKUP or DBQUERY function repeatedly issues the same statement, the second and subsequent executions of the statement execute much faster than the first execution. However, if any element of the statement varies, the DBMS considers the statement to be new and does not take advantage of caching. For example, the two following statements are distinct to a DBMS:

SELECT * FROM MyTable WHERE CorrelationID=123
SELECT * FROM MyTable WHERE CorrelationID=124

Use the bind facility for DBLOOKUP and DBQUERY functions to submit such statements to the DBMS so that the statements are syntactically identical. By binding a value to a placeholder in the SQL statement, the actual syntax of the statement can be made static.

The syntax for specifying a value in the SQL statement as a bind value is:

               :bind( 
               value 
               )
            

For example, to use a bind variable in the statement above, the SQL statement would be:

SELECT * FROM MyTable WHERE CorrelationID=:bind(123)

The database adapter strips out the :bind keyword and binds the value 123 to a placeholder in the statement.

The value in the parentheses is always a text item. Single quotation marks should not be specified around string literals. For example, if you had the statement:

SELECT Artist FROM CDList WHERE Title = 'Goodbye'

and you want to bind the value for the title, the syntax would be:

SELECT Artist FROM CDList WHERE Title=:bind(Goodbye)

Within the context of a DBLOOKUP or DBQUERY function, the elements of the statement to be bound are dynamic elements. For example, if the following call toDBLOOKUP is in a map:

DBLOOKUP         ("SELECT Name FROM MyTable WHERE ID="+ Item1:Row + 
          "and CorrelationID= '" + Item2:Row + """, 
          "DB.mdq",
          "MyDB")

The call could be modified to benefit from binding values as follows:

DBLOOKUP         ("SELECT Name FROM MyTable WHERE ID=:bind("+ Item1:Row +") 
                       and CorrelationID=:bind(" + Item2:Row +")",
                      "DB.mdq", 
                      "MyDB")

There is no performance benefit unless all values that change from one invocation of the statement to the next are bound. For example, if the ID value is bound and not the CorrelationID value, the statement will vary because the CorrelationID value varies.