Data Transformation Engine Database Interface Designer Reference Guide

Chapter 7 - Database Functions Using DBLOOKUP and DBQUERY
Database Interface Designer Reference Guide
115
Also assume that this database has been defined in a file named mytest.mdq
using the Database Interface Designer. The name of the database, as specified in
the .mdq file, is PartsDB. Notice the difference between the returned values from
the execution of the following two Syntax1-formatted functions.
Function Returns PART_NAME
DBLOOKUP ("SELECT PART_NAME from PARTS
where PART_NUMBER =1",
"mytest.mdq", "PartsDB")
¼” x 3” Bolt
DBQUERY ("SELECT PART_NAME from PARTS
where PART_NUMBER =1",
"mytest.mdq", "PartsDB")
¼” x 3” Bolt<cr/lf>
where <cr/lf> is a carriage
return followed by a line feed
Using Syntax2, you can also specify the
DBLOOKUP or DBQUERY functions as in the
following examples.
DBLOOKUP("SELECT PART_NAME from PARTS where PART_NUMBER =1",
"-MDQ mytest.mdq -DBNAME PartsDB")
DBQUERY("SELECT PART_NAME from PARTS where PART_NUMBER =1",
"-MDQ mytest.mdq -DBNAME PartsDB")
Note that both the .mdq file name and database name are specified.
The examples below use the Syntax2 format to specify the database type and the
appropriate database-specific adapter commands (in this example, using the
-DBTYPE, -CONNECT, -USER, and -PASSWORD commands for an Oracle database):
DBLOOKUP("SELECT PART_NAME from PARTS where PART_NUMBER =1",
"-DBTYPE ORACLE -CONNECT MyDatabase
-USER janes -PASSWORD secretpw")
DBQUERY ("SELECT PART_NAME from PARTS where PART_NUMBER =1",
"-DBTYPE ORACLE -CONNECT MyDatabase
-USER janes -PASSWORD secretpw")
Example 2 - Using DBQUERY to Obtain Multiple Columns or Rows
Assume that you have a table named PARTS that consists of the following data: