DBQUERY

The DBQUERY function executes an SQL statement against a database. The SQL statement can be any permitted by your database management system or ODBC driver.

When the DBQUERY 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 the arguments for DBQUERY. You can use DBQUERY [Meaning 1] to execute an SQL statement when you want to look up information in a database using a parameterized query that is based on another value in your data. If your SQL statement is a SELECT statement, the DBQUERY function might be used in conjunction with the RUN function to issue dynamic SELECT statements whose results can be used as input to another map.

You can also use the DBQUERY function [Meaning 2] to execute an SQL statement when the database, table, or other database parameters might vary; perhaps being supplied by a parameter file.

Syntax:

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

Meaning:

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

Returns:

A single text item

If your SQL statement is a SELECT statement, the results of the query in the same format as a query specified as a map input card, including row delimiters and terminators, and so on.

If your SQL statement is anything other than a SELECT statement, "none".

Arguments for meaning 1

DBQUERY (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 that is 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 Database Interface Designer.

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

Arguments for meaning 2

DBQUERY ( SQL_statement , parameters )

• The first argument is an SQL statement as a text string. This can be any valid SQL statement that is 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.
• 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.

    Note: 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.

    Note: This syntax does not use an .mdq file, because the database-specific parameters provide the information required to connect to the database. See the appropriate database adapter documentation for detailed information about database-specific parameters.

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

• All keywords (for example, -DBTYPE) can be upper or lower case, 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

Also 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.

Returns 1|¼" x 3" Bolt<cr><lf>2|¼" x 4" Bolt<cr><lf>

where <cr><lf> is a carriage return followed by a line feed.

Using Meaning 2, you can also specify the DBQUERY this way:

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

Or, specify it this way, using Meaning 2 by specifying the database type and the appropriate database-specific parameters:

Assume that you have an input file containing one order record. To map that order to another proprietary format, you also have a parts table with pricing information for every part for every customer, a very large table. Rather than using the entire parts table as the input to your map, you might use the RUN function with a DBQUERY to dynamically select only those rows from the parts table corresponding to the customer in the order file, as follows:

RUN ( "MapOrder.MMC" ,
  "IE2" + DBQUERY ( "SELECT * FROM Parts WHERE CustID = "
 + CustomerNo:OrderRecord:OrderFile + " ORDER BY PartNo" ,
"PartsDB.MDQ", "PartsDatabase" ) )

Related functions

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