DBLOOKUP

The SQL statement can be any permitted by your database management system or ODBC driver.

When the DBLOOKUP function is used in a map, the default OnSuccess action is adapter specific. The default OnFailure action is to rollback any changes made during map processing. The default Scope will be integral unless the map is defined to run in bursts (which is the case when one or more inputs have the FetchAs property set to Burst).

There are two ways to specify arguments for DBLOOKUP.

You can use DBLOOKUP to execute an SQL statement when you want to execute a SELECT statement to retrieve a specific column value in a large table in a database using the value of another input, rather than defining the entire table as an input card and using the LOOKUP, SEARCHDOWN, or SEARCHUP functions.

You can use DBLOOKUP to execute an SQL statement when you want to execute a SELECT statement to retrieve a specific column value from a table or database that might vary based on a parameter file. Using Meaning 2 of the DBLOOKUP function allows these parameters to be dynamically specified at run time.

Syntax:

DBLOOKUP ( single-text-expression , single-text-expression , [ single-text-literal ] )

Meaning:

1. DBLOOKUP ( SQL_statement , mdq_filename , database_name )
2. DBLOOKUP ( SQL_statement , parameters )

Returns:

A single text item

The DBLOOKUP function returns the results of the query in the same format as a query specified for a map input card, except that it does not include the last carriage return/linefeed. Because this information is removed, it is easier to make use of a single value extracted from a database.

Arguments for meaning 1

DBLOOKUP ( SQL_statement , mdq_filename , database_name )

• SQL_statement

  The first argument is an SQL statement as a text string. This can be any valid SQL statement permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.

• mdq_filename

  The second argument is the name of a database query file (.mdq) produced by the Database Interface Designer. It contains the definition of the database that the SQL statement is to be executed against. If the .mdq file is in a directory other than the directory of the map, the path must be specified.

  Note: The .mdq file is accessed at map build time and is not needed at run time.
• database_name

  The third argument is the name of a database in the database query file (.mdq) as defined in the Data Interface Designer.

  If used in this way, both the .mdq filename and database name must be literals.

Arguments for meaning 2

DBLOOKUP ( SQL_statement , parameters )

• SQL_statement

  The first argument is an SQL statement as a text string. This can be any valid SQL statement permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.

• parameters

  The second argument is a set of parameters, either:

  • -MDQ mdqfilename -DBNAME dbname

    -or-

  • -DBTYPE database_type [database specific parameters]

    The keyword -MDQ is followed by the name of the database query file (.mdq) produced by the Database Interface Designer. This .mdq file contains the definition of the database. If the .mdq file is in a directory other than the directory of the map, the path must be specified. The .mdq filename is followed by the keyword -DBNAME and the database name as specified in the Database Interface Designer.

    Using this syntax, the .mdq file is accessed at run time and must be present.

    The keyword -DBTYPE is followed by a keyword specifying the database type (for example, ODBC or ORACLE) followed, optionally, by database-specific parameters.

    This syntax does not use an .mdq file, because the database-specific parameters provide the information required to connect to the database. See the Resource Adapters documentation for detailed information about the database-specific parameters that can be specified.

When used with Meaning 2, DBLOOKUP must conform to these rules:

• All keywords (for example, -DBTYPE) can be upper or lowercase, but not mixed.
• A space is required between the keyword and its value (for example, -DBTYPE ODBC).
• The order of the keywords is not important.

  All database-specific parameters are optional.

Examples

Assume that this database has been defined using the Database Interface Designer in a file named mytest.mdq and that the name of the database, as specified in the .mdq file, is PartsDB.

• DBLOOKUP ( "SELECT PART_NAME from PARTS where PART_NUMBER =1", "mytest.mdq", "PartsDB")

  Returns: ¼" x 3" Bolt

Using Meaning 2, you can specify the DBLOOKUP this way:

• DBLOOKUP( "SELECT PART_NAME from PARTS where PART_NUMBER =1", "-MDQ mytest.mdq -DBNAME PartsDB" )

  where both the .mdq file name and database name is specified.

Using Meaning 2, you can also specify the database type and the appropriate database-specific parameters:

• DBLOOKUP( "SELECT PART_NAME from PARTS where PART_NUMBER =1" , "-DBTYPE ORACLE -CONNECT MyDB -USER janes " )

Related functions

• DBQUERY
• EXTRACT
• FAIL
• LASTERRORCODE
• LASTERRORMSG
• LOOKUP
• SEARCHDOWN
• SEARCHUP
• VALID

For more examples using the DBLOOKUP function, see the Database Interface Designer documentation.