During run time, single-table inbound database activities
poll if a database operation has occurred on any rows in the specified
columns of a database table at the specified time interval.
This section contains the procedures for configuring the following
single-table inbound database activities:
- Get Inserted Rows - During run time, the Get Inserted
Rows
activity polls for rows being added to the buffer table from an INSERT
trigger event. The Get Inserted Rows activity then maps the buffer
data to variable or variables.
- Get Updated Rows - During run time, the Get Updated
Rows
activity polls for rows being added to the buffer table from a UPDATE
trigger event. The Get Updated Rows activity then maps the buffer
data to variable or variables.
- Get Deleted Rows - During run time, the Get Deleted
Rows
activity polls for rows being added to the buffer table from a DELETE
trigger event. The Get Deleted Rows activity then maps the buffer
data to variable or variables.
Figure 1 shows
the transfer of example data in the UPDATE trigger event and the
Get
Updated Rows activity:
Figure 1. UPDATE
trigger example
After publishing your orchestration with an inbound database
activity to the Integration Appliance, you must create the buffer
table and triggers using the Web Management Console (WMC) before the
orchestration is deployed. For the Oracle database, you must also
create a sequence using the WMC.
No Activity for that Operation Run-time Error
If
an orchestration accesses a buffer table containing a row for an activity
that is currently not present in the orchestration, the orchestration
fails and following error is reported during run time:
The buffer table contains the OperType operation
but there is no activity in the orchestration for that operation.
Where
OperType is
type of operation; for example, INSERT. The values of the IH_OPERATION_NAME
column in the buffer table for the orchestration must be consistent
with the database activities in the orchestration. For example, if
a row in the buffer table contains the value: INSERT in the IH_OPERATION_NAME
column but the orchestration does not contain a
Get Inserted Rows activity,
this error is reported. Each orchestration must have a separate buffer
table. In addition, the triggers in the source table must be consistent
with database operations specified in the buffer table. For example
if the orchestration only contains a single inbound
Get Updated
Rows activity, the source database table must only contain a UPDATE
trigger.
Add a single-table inbound database activity to the
orchestration
- Create or open an orchestration. A graphical representation of
the orchestration is displayed.
- Select the Activities tab and expand the Database folder.
- Drag the desired single-table inbound database activity (Get
Inserted Rows, Get Deleted Rows, or Get Updated Rows)
onto the orchestration.
- Select the activity.
Create, select, or edit a database endpoint
- Click Pick Endpoint task from the Checklist and
choose from one of the following actions:
- Select an existing endpoint:
- Select Browse. The Project Explorer is
displayed.
- Select an existing endpoint and click OK.
Skip to the "Configure the activity" procedure.
- Select an existing endpoint and edit the endpoint:
- Select Browse. The Project Explorer is
displayed.
- Select an existing endpoint and click OK.
- Click Edit. The Edit Endpoint pane is displayed.
- Create a new endpoint: Select New. The
Create Endpoint pane is displayed.
- Create or edit a HTTP endpoint, see Creating or editing an HTTP endpoint.
Select the columns in the source database table that
during run time the Integration Appliance polls for changes
- Click Pick Table from the Checklist.
The Pick Table pane is displayed.
- Click Browse... to select the database
table that is to be the source for inbound messages. The Browse Database
Tables dialog box is displayed.
- Select a table from the list and click OK.
Column information for the selected table is displayed in the Pick
Table pane.
- If the character encoding for this table is different from the
encoding for the database, enter another encoding using one of the
following options:
- From the Encoding list, select one of the
default encoding types.
- Enter your encoding type directly by clicking in the Encoding field
and typing in your encoding type.
This sets the encoding for all the columns of the resulting buffer
table.
- By default all columns in the source table are selected. Clear
the Use check box to remove a column from resulting
buffer table. Click Use All to reselect all
the columns.
- Click in the heading of the column to sort
the rows by the column value. For example, to sort the rows by their
column name, click the Column Name heading
of the column. An arrow is displayed to the right of the column header
name. An up arrow indicates the column is sorted by alphabetic order
starting with a and continuing to z. A down arrow indicates
the column is sorted by reverse alphabetic order starting with z and
continuing to a. Click the arrow to reverse the alphabetic
order.
- For the Get Updated Rows activity only - if you want the
resulting buffer table to contain both the new values and the original
values of the updated columns follow these steps:
- Click Old Values. The Old Value Columns
for Updates dialog box displays.
- Select the Store check box for the database
columns that you want to save the original values.
- You can change the default names assigned to columns to store
the original values. Double-click the appropriate Column
Name to Store Old Value cell.
- Click OK. The selected columns are displayed
in the From Activity of the Map Outputs pane
prefixed with the string: old_. You can
map both the new and original values from the Map Outputs pane.
- If necessary, change the settings of the Data Type, Encoding,
or Nullable values. Click the field to make a
new selection.
- Data Type - Defaults to the data type for
the column in the source database table. If the required data type
for the column in the buffer table is different from the data type
of the source table, select a different data type by clicking in the Data
Type cell and selecting another Data Type from the list.
You must select an appropriate data type for those data types that
are marked UNSUPPORTED.
Note: For the XMLTYPE data type in an Oracle
database and the MONEY data types in a SQL Server database, Studio
does not automatically associate the correct data type so you must
manually select the appropriate data type.
Attention: You
must select a compatible data type with the source data type to avoid
invalid data during run time. For example, do not select the SMALLINT
data type for the buffer table if the source data type is INTEGER
because during run time a large number stored in an INTEGER column
will not convert correctly to the SMALLINT data type.
- Size - Size of the column defined in the
table. This size is retrieved from the database table and is not configurable.
- Encoding - Defaults to current default encoding
that was set either at the database level or the table level. If the
required encoding for the column is different from the current default
encoding, select another encoding by clicking in the Encoding cell
and selecting another Encoding from the list.
- Nullable - Defaults to the nullable setting
for the column in the source database table. The Use check
box must be selected for those columns where nullable=false.
A column marked nullable=false cannot
not contain a null during run time.
- Click Reset to reset all column properties
to their defaults. This also resets the Use check
box for all columns.
- Click Refresh to update the list of columns
in reference to the latest state of the source database. Recent changes
to the source is updated in this pane.
- Configure the Specifying Delivery Rules for Database Activities.
Note: If
you select the Exactly once option in the Delivery
Rules task, you must create control tables that are used by the
Integration Appliance during run time. You create the control tables
after publishing the orchestration from Studio to the Integration
Appliance. You create the control tables using the Web Management
Console (WMC) before you deploy the project.
Note: If you select
the
Exactly Once option, you must enable persistence.
For more information, see
Enabling persistence.
- Configure the retry options. For more information, see Retry Options For Inbound Database Activities.
Map the output of the activity
- Select the Map Outputs task in the Checklist.
The
output parameters for the activity are displayed under the rows element
in the From Activity pane. The output parameters displayed in the
From Activity pane correspond to the columns you selected in the Pick
Table task. If the Batch option was selected
in Delivery Rules, the multiple occurrences symbol displays
next to the row element.
Note: For the Get Updated Rows activity
only, if some columns were selected in the Old Values Columns for
Updates dialog box (in the Pick Table task), these additional
output parameters (by default prefixed with the string: old_)
also display under the rows element. You must map all the old value
columns visible in the Map Outputs pane.
- Map the required output parameters to variable or variables. See Creating a map for general instructions on
mapping. You are not required to map the output parameters for these
activities.
Note: After publishing your orchestration with an inbound
database activity to the Integration Appliance, you must create the
buffer table using the Web Management Console (WMC) before the orchestration
is deployed.