Data Transformation Engine Database Interface Designer Reference Guide

Chapter 7 - Database Functions Using Bind Values in Database Functions
Database Interface Designer Reference Guide
119
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 to
DBLOOKUP 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")