Step 2 - Defining the database insert transport map

This section describes the steps to define a transport map for a database insert operation. A transport map defines the parameters that come from a trigger (your application) and the mapping or formatting of those parameters to fields in the data that gets sent to the database server.

Assumptions

The following is assumed:

• The Workbench is started and you have logged on.
• You have completed the steps in Step 1 - Defining the database transport.

Procedures

Follow these steps to create a transport map that uses a database transport.

1. From the Workbench left pane, expand the node where you want to define the transport map.
   
   
2. Expand Enterprise, right-click the Transport Maps icon to display its pop-up menu, and then select New.
   The Transport Map window appears.
   
   
3. In the Name parameter, enter MyFirstTransportMap as the unique name for the transport map. A transport map name can be up to 32 characters and include letters, numbers, and the underscore character. Spaces are allowed.
   
4. Use the Transport Type down-arrow, and then select DB.
   Only DB transports that are available on the node will be listed in the Transport Name drop-down list.
   
5. From the Transport Name drop-down list, select the transport you created in Step 1 - Defining the database transport, MyDatabaseTransport.
   
   The To Enterprise section changes to accommodate the database transport. This is the data that will be stored on the enterprise system. The data can be any type including an engine serial number, machine torque reading, part count, and so forth.
   
6. From Version Mismatch drop-down list, accept the default Fail.
   The Fail option will immediately fail any trigger event that has a version mismatch between the current transport map and the transport map that was used in the trigger.

Adding a database table

When you selected the database transport, the To Enterprise section of the Transport Map window changed to accommodate a database table.

The first step is to select the table you want to send data to.

1. Go to the To Enterprise section under the Input tab. You will see parameters to select the schema, action, and database table.
   
   
2. Select the Schema down-arrow. For this example, ACUSER is selected.
   The schema will contain tables whose columns and rows you want to access.
   
3. Select the Table down-arrow, and then select the appropriate table. For this example, TABLE01C.
   
   
4. Select the Action down-arrow, and then select Insert.
   Insert will add a new row of data into a database table whenever a trigger event occurs.
   An Insert tab becomes populated with the column information for that table.
   

Defining the map variables

Now that you have selected the table, you can define the input map. The input transport map will consist of a collection of map variables. The application developer will see these variables when defining the trigger. During runtime, the variables are mapped to physical device variables (when a plant floor event occurs).

Since there are 5 required columns in the example table, you will want to map 5 data items (from the production line) into the table. These data items will be defined by using 5 logical names (not the real device variable or alias names). The resolution of these map variables to production line data will occur when you create the trigger.

There are two methods for defining the map variables:

• Create each map variable separately.
• Create the map variables automatically at one time.

Creating each map variable separately

To create each map variable separately, use the Add button on the Input tab on the Transport Map window.

1. On the Input tab, under From Trigger, select Add.
   
   The New Item window appears.
   
   
2. In the Name parameter, enter log1 as the name for the map variable.
   The map variable name can be up to 32 characters and include letters, numbers, underscore, dash characters, and spaces. Special characters such as < > ' (single quotation) " (double quotation) are not allowed.
   
3. Select the Type down-arrow to display a list of supported data types, and then select the data type that you want assigned to the name. For this example INT2.
   
   
4. In the Count box, accept the default 1. The value specifies the dimension of the map variable (for this example a scalar).
   If the variable was an array, you would change the value in the Count box to the number of elements in the array.
   
5. Select Add.
   A row appears on the Input tab with the information you added.
   
   
6. Repeat the steps to add all required rows naming the map variables appropriately.
   The final Input tab might look like this:
   
   

Automatically creating the map variables

To automatically create map variables, select the Map Table button (on the ToEnterprise section).
All of the columns in the table will be populated with a name and appropriate data type.

1. From the To Enterprise section, select MapTable.
   
   The map variables are automatically added to the Input tab.
   
   
2. Once you have created the map variables, you can edit the columns as follows:
   • To change the name under the Name column, double-click the name, and then retype it.
   • To change a data type, select the row under the Logical column, and then select a data type from the list.
   • To change a value that specifies the dimension of the map variable, double-click the row under the Count column, and then type a different value.

Note: Output map - Because the transport map is based on a database Insert operation, an output map is not applicable. If a transport map is bi-directional (doe example a Select operation) then the trigger will expect data that is queried from the database server to be returned based on the definition in the output map.

Associating map variables with table columns

Now that you have created the map variables, the last step is to associate the map variables with the database table columns. This is the format of the data sent to the database server. For this example, the data is for an SQL Insert operation.

1. Go to the table at the bottom of the To Enterprise section.
   
2. Select the first database column. For this example, CO1.
   
3. Select the Variable column.
   A list appears with the map variables that you just created.
   
   
4. Select the map variable that you want associated with the database column. For this example, log1.
   
5. Repeat steps 2 through 4 for each column in the table.
   The completed To Enterprise section will be similar to the following.
   
   
6. When you complete the transport map, select Validate.
   The format of the data (the Insert operation with the columns from the table) is displayed in a window.
   
7. If no errors are received, click Save.
   The new transport map is saved to the node, and the name is added to the Transport Maps tab.

The transport map also becomes available from the Transport Map drop-down list when creating a trigger with a Transaction action.

The next step is to define the trigger that identifies the data to insert into the database table. Go to Step 3 - Defining a trigger to insert data in a database table