Single-Table Inbound Database Activities

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

  1. Create or open an orchestration. A graphical representation of the orchestration is displayed.
  2. Select the Activities tab and expand the Database folder.
  3. Drag the desired single-table inbound database activity (Get Inserted Rows, Get Deleted Rows, or Get Updated Rows) onto the orchestration.
  4. Select the activity.

Create, select, or edit a database endpoint

  1. Click Pick Endpoint task from the Checklist and choose from one of the following actions:
    • Select an existing endpoint:
      1. Select Browse. The Project Explorer is displayed.
      2. Select an existing endpoint and click OK. Skip to the "Configure the activity" procedure.
    • Select an existing endpoint and edit the endpoint:
      1. Select Browse. The Project Explorer is displayed.
      2. Select an existing endpoint and click OK.
      3. Click Edit. The Edit Endpoint pane is displayed.
    • Create a new endpoint: Select New. The Create Endpoint pane is displayed.
  2. 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

  1. Click Pick Table from the Checklist. The Pick Table pane is displayed.
  2. Click Browse... to select the database table that is to be the source for inbound messages. The Browse Database Tables dialog box is displayed.
  3. Select a table from the list and click OK. Column information for the selected table is displayed in the Pick Table pane.
  4. 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.
    Note: Some double-byte characters are not converted using the SHIFT-JIS encoding. For more information, see Using the Shift-JIS encoding.
  5. 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.
  6. 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.
  7. 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:
    1. Click Old Values. The Old Value Columns for Updates dialog box displays.
    2. Select the Store check box for the database columns that you want to save the original values.
    3. 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.
    4. 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.  
    Note: For more information see Defining Columns for Old Values in Updates.
  8. 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.
  9. Click Reset to reset all column properties to their defaults. This also resets the Use check box for all columns.
  10. 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.
  11. 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.
  12. Configure the retry options. For more information, see Retry Options For Inbound Database Activities.

Map the output of the activity

  1. 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.
  2. 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.