Query Database

The Query Database task is used to construct a SQL SELECT statement query to a database. This task is useful for performing basic queries for looking up information stored in a database table that will be used in the mediation flow.

General Tab

Use the General tab to specify a name and description for the task. This tab is useful for providing documentation for tasks in your mediation flows

Field Description
Name

Assign a name to the task, to identify the task in the mediation flow. This name appears in the tooltip that opens when you hover the cursor over the task icon in the mediation flow.

This is also the name of the element in the mediation exchange that stores the output of this task.

Description Describe the task briefly. This description appears in the tooltip that opens when you hover the cursor over the task icon in the mediation flow.
Shared Resource Property Use the drop-down list on the field to select the available shared resource property. Shared resource properties define database connections that are used to perform the query. Shared resource properties are defined on the Shared Resource Properties tab of the mediation flow.
Max Row Count The maximum number of rows to retrieve. The default value is 1. Specify a positive integer, or use the drop-down menu on the field to select Unlimited to return an unlimited number of rows.
Query Timeout (sec) The timeout for the query.

Query Tab

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

Click the connection button to test the connection and to verify the table and column data. Clicking this button opens a connection, if you have specified a JDBC property in the mediation flow Properties tab, 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) buttons to the right of each list to add and delete items from each list.

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

Clicking the + and x buttons 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 to 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, so you must manually edit the WHERE clause to specify any join conditions for your query.

The SQL Statement field displays a read-only version of the query you have specified. The following table lists the supported SQL types and how they map to XML. Note that length parameters are stripped from the SQP Type, and only the base type is used in the mapping — for example, char(12) becomes char.

Supported SQL types and their mapping to XML
SQL/92 Data Types XML Type Equivalent
TINYINT short
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT float
DOUBLE double
CHAR string
VARCHAR string
NCHAR string (multi-byte)
NVARCHAR2 string (multi-byte)
DATE date
TIME time
TIMESTAMP dateTime

Vendor-specific types are cast to string. You can enable the mapper to automatically recognize these types in one of these 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.
Note: Binary or other complex data types such as JAVA_OBJECT are not supported.

Input Tab

The Input tab is a mapping panel for mapping data from the mediation exchange to the input fields of this task. See Transform Tasks for more information about using a mapping panel.

Field Description
InputValues

A complex element to hold the input for this task. The sub-elements of this element are the input variables defined on the Query tab. Each input variable corresponds to a question mark (?) that appears in the WHERE clause of the query.

Map values from the mediation exchange to fields in the input schema to supply values for the input variables of the query.

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

The Test tab is used to test the database query. You must have a valid JDBC template associated with the JDBC property used by this task. The JDBC resource template is used only in the design environment.

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

Note: It is important for you to ensure that the JDBC resource template you use for testing in the design environment connects to a database that is similar to the database used when the project is put into production.