Adding database tables your message map

To retrieve data from the database, you must define which database tables the message map uses.

Before you begin

Configure the AddressBook service to include the data design project AddressBook_DD that contains the CLIENTS.dbm database definition file. For more information, see Configuring an integration solution to access database resources.

About this task

Note: When you add database tables to your message map, you should add all the tables under the same database schema together, that is, one resultset per set of tables. You reduce the number of connections that IBM® App Connect Enterprise requires to retrieve database information from those tables.

Procedure

Complete the following steps to add PERSON, ADDRESS, and PHONE database tables under the ADDRESSBOOK schema:

  1. Open the message map FindAddress by completing the following steps:
    1. In the Application Development view, navigate to AddressBook > Resources > Maps > AddressBook.
    2. Double-click on FindAddress.map.

    The following figure shows the navigation tree where you can find the message map FindAddress.map:

    This figure shows the navigation tree where you can find the message map FindAddress.map

    The message map FindAddress.map opens in a new tab.

  2. Click the Select rows from a database icon.

    The following figure shows the icon that you must choose to select the option Select rows from a database:

    This figure shows the icon that you must choose to select the option Select rows from a database.

    The New Database Select wizard opens.

    This figure shows the New Database Select wizard window.

  3. Select the schema ADDRESSBOOK, and the database tables PERSON, ADDRESS, and PHONE.

    This figure shows the schema ADDRESSBOOK, and the database tables PERSON, ADDRESS, and PHONE selected.

  4. Define the SQL where clause expression that you use to extract a single address record from the database.

    To define the expression, you can drop or double-click a column, an operation, or an input into the SQL where clause pane, use copy and paste, or use content assist (CTRL+Space).

    You can use the following SQL where clause expression:

    ADDRESSBOOK.PERSON.LASTNAME IN ? AND ADDRESSBOOK.PERSON.COUNTRY = ?1

    where ? represents the XPath expression:

    $MessageAssembly/SOAP_Domain_Msg/Body/{http://AddressBook}:FindAddress/FindAddress/{http://addressbook.com}:Name

    and ?1 represents the XPath expression:

    $MessageAssembly/SOAP_Domain_Msg/Body/{http://AddressBook}:FindAddress/FindAddress/{http://addressbook.com}:Country

    The following figure shows the expression defined within IBM App Connect Enterprise:

    This figure shows graphically how the expression is implemented in IBM App Connect Enterprise.

  5. Select OK.

Results

Under Message Assembly, the Select from CLIENTS section is added. This section contains one resultset. The resultset has three tables. To see which table an element belongs to, select the element in the resultset, and then view the Properties tab.

Beware that tables are included in alphabetical order.

The following figure shows the resultset that you get when you include the tables PERSON, ADDRESS, and PHONE.

This figure shows the resultset that you get when you include the tables PERSON, ADDRESS, and PHONE.

What to do next

You must configure the Select transform in your message map. For more information, see Configuring the Select transform in a message map.