Configuration Tabs of the Query Database Task

General Tab

• On the General properties tab, you can specify a name and description. You must also select one of the resource templates defined for the meditation flow. See JDBC Resource Templates.

• The Max Row Count field specifies the maximum number of rows to accept from the query results. For example, a positive integer of 1 returns only one row. The choice of Unlimited allows an unlimited number of rows in the result set.

• The Query Timeout field specifies the timeout, in seconds, for a query statement to execute before an exception is thrown.

Query Tab

You use the Query tab to define the SELECT statement for the query.

If you specified a JDBC property in the mediation flow Properties tab, clicking the connection icon opens a connection and compares the table and column data with the metadata from the database. If the connection is not successful, an error notifies you of the reason.

Three lists enable you to select tables, input data, and output columns for use in the WHERE clause of your SELECT statement.

Input data is used in the WHERE clause of your SELECT statement. Use the add (+) and delete (x) icons to the right of each list to add and delete items from each list:

• When a database connection is present and valid, the + icons display information from the database for selecting tables and output columns.
• When no database connection is present, the + icons allow you to add items to each list, but you must name each item and specify a type if necessary.

Clicking the + and x icons on the Input table attempt an automatic update of the WHERE condition. If you have modified the WHERE condition, the delete might not update it and you must fix it manually.

Use the Where Condition field on the Query tab to edit the WHERE clause of the query. You can add an input variable to a condition by typing a question mark (?) in the condition. Each input variable appears in the mapper panel on the Input tab, and you can supply data from the mediation exchange for the input variable. For example, if you want to create a condition where you look up a ZIP code supplied in the input message, you can add the condition table.ZIP = ?. When you add a question mark into the WHERE clause, an input variable appears in the Input Data list. Supply a name for the input variable, then data from the mediation exchange can be mapped to the input variable.

Table join conditions are never automatically added to the WHERE clause. To specify any join conditions for your query, you must manually edit the WHERE clause.

The SQL Statement field displays a read-only version of the query you have specified. Length parameters are stripped from the SQP Type, and only the base type is used in the mapping. For example, char(12) becomes char.

Vendor-specific types are cast to string. You can enable the mapper to automatically recognize these types in two ways:

• Force vendor-specific types to a compatible XML type using the mapper cast.
• Override the type that is retrieved from the database for the column to a similar SQL/92 type.

Input Tab

The Input tab is a mapping panel for mapping data from the mediation exchange to the input fields of this task. See Transforming Data in a Mediation Exchange for information on using a mapping panel.

Output Tab

The Output tab is a read-only display of the output schema for this task. The output schema is determined by the output columns selected on the Query tab.

Test Tab

Use the Test tab to supply test data for values of input variables and test the query against the database associated with the specified JDBC resource template. To test the statement, a valid database connection must be present.

You can use a custom JDBC driver to test the database query. For information about configuring a custom JDBC driver, see Composite Development.

You must have a valid JDBC resource template associated with the shared resource profile used by this task. The JDBC shared resource is used only in the design environment.