Data Transformation Engine Database Interface Designer Reference Guide

Database Interface Designer Reference Guide
110
Chapter 7 - Database Functions
This chapter discusses two functions (DBLOOKUP and DBQUERY) that are designed
exclusively for use with databases. These functions can be used in component
rules in the Type Designer and map rules in the Map Designer when creating a
map to be used with a database.
Accessing Database Information in a Map Rule
In many cases, database information for a map will be one of the following:
♦ the results of a database query or a stored procedure defined as the data
source for an input card
♦ the rows to update or insert into a table defined as the target for an output card.
However, there are certain situations in which you might not want to define the
entire query as a data source or the table as a target. For example, you may have a
very large table that is used as a source for cross-reference in your map. However,
because of its size, reading in and validating all of the information as an input card
is impractical. In other situations, you may want to call one map from another
using the
RUN function when one of the data sources is a dynamically built query.
Two functions provide the ability to access database information from within a
rule:
DBLOOKUP and DBQUERY. The difference between the two functions is subtle
and is based upon how the data resulting from either function is returned, as
explained in this chapter.
Using DBLOOKUP and DBQUERY
A single-text-item is returned by either the DBLOOKUP and DBQUERY function. If
your SQL statement is a SELECT statement, the
DBLOOKUP or DBQUERY function
returns the results of the query in the same format as a query specified in a map
input card. If your SQL statement is anything other than a SELECT statement,
these functions return NONE.
Both functions return the results of a query (SQL SELECT statement) in the same
format as a query specified for a map input card, using the delimited row format.
However, the
DBLOOKUP function strips off the last carriage return/line feed.
Because this information is stripped, it is easier to make use of a single value
extracted from a database.