This section contains the procedures for configuring the
following single-table outbound database activities.
These activities include:
- Insert Rows - This activity takes data from variable or
variables that are mapped to the input parameters of the activity
and inserts this data as rows into the specified database table.
- Update Rows - This activity takes data from variable or
variables that are mapped to the input parameters of the activity
and updates the rows of the specified database table with this new
data.
- Delete Rows - This activity takes data from variable or
variables that are mapped to the input parameters of the activity
and deletes rows of the specified database table based on this data.
For the
Update Rows and the
Delete Rows activities,
the Integration Appliance integrates the database table to determine
which columns in the table are primary key columns. When the
Update
Rows or the
Delete Rows activities are started during run
time, the Integration Appliance uses the value of the incoming variable
that contains the primary key number to determine which rows to update
or delete.
For example, the
PK column is defined
in the database as being a primary key column for the database table.
The
Use check box is selected for the
PK column
in the
Pick Table task of the
Checklist for the
Delete
Rows activity. In the Map Inputs pane of the
Delete Rows activity,
the variable called
myIncomingPK is mapped to
the
PK element of the row as shown in
Figure 1:
Figure 1. myIncomingPK is
mapped to the PK element of the row
During run time, if the value of the
myIncomingPK variable
is equal to 3 the row with the
PK equal
to 3 is deleted from the table as shown in
Figure 2:
Figure 2. The row with the primary
key equal to 3 is deleted
If no primary key columns are specified in the
Pick Table task
of the
Checklist, specify at least one column to act like the
primary key. Specify acting primary key column or columns using the
Configure Primary Keys dialog box, available by clicking
Keys in
the
Pick Table task of the
Checklist for the
Delete
Rows and
Update Rows activities. If no primary keys are
specified in the database table or selected as acting primary keys
in the Configure Primary Keys dialog box, all the rows of the database
table are affected by the delete or the update operation.
You can also use the Configure Primary Keys dialog box to specify
additional columns to act as primary key columns. For example for
the table in the preceding figure, you could define that the Name column
should act like primary key column in addition to the PK primary
column that was defined in the database as being a primary key column.
Add a single-table outbound 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 wanted single-table outbound database activity (Insert
Rows, Delete Rows, or Update 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 an endpoint: Select New. The Create
Endpoint pane is displayed.
- Create or edit an 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 are 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.
Specify the target database table for the activity
- Click Pick Table from the Checklist.
The Pick Table pane is displayed.
- Click Browse... to select the target database
table. 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. If a column is not selected, it is not displayed in
the Map Inputs pane and is not updated at run time. 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.
- 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 wanted 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
is not converted 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
wanted 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 are updated in this pane.
- For the Update Rows activity only - Select the Insert
row if row does not already exist check box, if during
run time you want the Update Rows activity to first try and
update the specified row but if the row does not exist,insert the
row into the database table.
- For the Update Rows activity only - if you want to save
both the new values and the original values in the database, follow
these steps:
- Click Old Values. The Old Value Columns
for Updates dialog box is displayed.
- 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 that store
the original values during run time. Double-click in the appropriate Column
Name to Store Old Value cell.
- Click OK. The selected columns are displayed
in the To Activity of the Map Outputs pane prefixed with the string: old_.
You can map to both the new and original values from the Map Outputs
pane.
- For the Update Rows or Delete Rows activities only
- To specify acting primary key columns in addition to any primary
key columns already defined by the database, follow these steps:
- Click Keys. The Configure Primary Keys
dialog box is displayed.
- To specify a column as an acting primary key column, click the
column then click >>.
- Click OK.
Note: If no primary keys columns (as defined by the database)
were specified in the Pick Table task of the Checklist,
you must specify at least one column as an acting primary key column.
Repeat
this step to specify more acting primary key columns.
- 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 Outbound Database Activities.
Map the input of the activity
- Select the Map Inputs task in the Checklist.
The
input parameters for the activity are displayed under the
row element
in the To Activity pane. The input parameters displayed in the From
Activity pane correspond to the columns you selected in the
Pick
Table task. If the
Batch Multiple Rows Per Message check
box was selected in
Delivery Rules, the multiple occurrences
symbol displays next to the row element.
Note: For the Get Updated
Rows activity only, if 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.
- Map all the input parameters. See Creating a map for general instructions on
mapping. You must map all the input parameters of the activity.
Map the output of the activity
- Select the Map Outputs task in the Checklist.
The
GetRowsCount check
box determines what is displayed in the Map Outputs pane:
- If the Get Row Counts check box is cleared
in the Delivery Rules, the Map Outputs pane is blank.
- If the Get Row Counts check box is selected
in the Delivery Rules, the Map Outputs pane displays the rowCount element.
If the Batch Multiple Rows Per Message check
box is selected in the Delivery Rules, the multiple occurrences
symbol is displayed next to the rowCount element.
- If the RowCount output parameter is displayed,
map the RowCount output parameter if wanted.
See Creating a map for general instructions
on mapping. You are not required to map the output parameters of the
activity.