Data Transformation Engine ODBC Adapter Reference Guide

ODBC Adapter Reference Guide
24
Chapter 6 - Binding Values in
DBLOOKUP/DBQUERY
When using a DBLOOKUP or DBQUERY function, use the Bind facility to submit
similarly constructed SQL statements to the database server 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, which may
improve performance. For more information about using bind values in database
functions, refer to the Database Interface Designer Reference Guide.
Limitation When Using ODBC
When binding values using ODBC, a limitation exists because ODBC cannot
determine the data type of the column to which the value is being bound at the
time the SQL statement is executed. Therefore, the data type of the value being
passed must be explicitly specified. Because of this ODBC limitation, the ODBC
database adapter determines the data type of the value according to the following
rule:
If the value contains only numeric characters or numeric characters with a
decimal point, the value is assumed to be numeric; otherwise, the value is
assumed to be text.
This rule provides the expected results in most cases; however, there may be
some situations in which the rule may not apply and it may be necessary to
specify the exact data type. For example, if the value is a text field consisting
solely of numeric characters, the value is incorrectly interpreted as numeric unless
correctly specified.
Specifying the Data Type
To override the default behavior and to explicitly specify the data type, a data
type indicator must precede the bind value. The syntax for this is:
:bind([T|N|D],
value )