Defining Database Update Adapters

Defining this object instructs Clarify as to the specific database, tables, records, and columns to update.

If you have not already created this object, see Creating Database Adapters.

The following steps take place in the Database Adapter editor, which has these sections: Settings, Interactions, and Data Bindings

  1. Under the Settings section's Clarify Database Schema field, select the Database Schema.
    The Database Schema represents the list and structure of the tables and fields that will be selected from the Database. After selecting the Schema, a list of tables in the schema appears in the Data Bindings section. The tables in the schema may be organized hierarchically, but the Data Bindings section displays them as a flat list. You can expand each table to view its fields.
  2. In the Data Source field, select the Data Source.
    The Data Source represents the connection to the Database.
  3. The Data Bindings section is where associations between the tables in the Database Schema and the tables in the DataSource are created/maintained. For each table in the Schema, click Click to set Data Source Table. Select the appropriate table from the list of tables.
    Typically the name of the table in Schema is identical to the table in the Data Source. In some cases, however, a table with a different name will have an identical list of columns. As long as columns match between two tables, the Adapter will function successfully and select the data from the specified table.
  4. Under the Interactions section, define a Update interaction. Click the green Add button.
  5. Select the new Interaction, choose Update from the interaction drop-down, and type a name for the interaction.
  6. Select and drag the desired tables from the Data Bindings section and drop them on the Interaction.
    Note: If you are not selecting all tables in the schema, you must ensure that when selecting a child table you also select the parent table.
    Note: If any of the entries in the Data Bindings section display Click to set Data Source Table, they will not be able to be dropped onto the Interaction.
  7. The Properties view displays and allows for specifying additional details.For the interaction itself:.
    1. Transaction Level
      • None: no commit or roll-back boundaries will be deployed
      • Full: all commit and roll-back boundaries will be deployed
    2. Select Mode
      • All records: Selects all the records in the table, filtered by Selection Criteria (i.e. Where clause information)
      • Related Records: For hierarchical Schemas, this selects only those records where the child records are joined to the parent records
  8. For a table within the Interaction:
    Update Adapters allow you to configure the following behavior:
    • Settings: transaction boundaries (i.e. commit and rollback), selection mode.
    • Set: Specify the columns to be updated.
    • Where: Specifies the columns to be updated.
  9. In the Settings tab:
    1. Transaction Level
      • None: no commit or roll-back boundaries will be deployed
      • Full: all commit and roll-back boundaries will be deployed
    2. Auto Truncate (Version 1 only): check to enable; uncheck to disable.
      If the length of data for a column is longer than the column' maximum length, you can instruct the Adapter to truncate the data before the update. For example, suppose you have a text column that is 50 characters long. If you are attempting to insert 60 characters of data for that column, you can have the Adapter truncate the data to 50 characters. If you do not enable AutoTruncate, the Adapter will fail to update the record.
      Note: To truncate means to cut off an ending portion of the data. In the example above, if 60 characters are truncated to 50, the first 50 characters will be preserved; the last 10 will be cut off and not processed.
  10. In the Set tab, configure the columns to be updated.
    1. Click the Add button to add a column to be updated.
    2. In the Field field, specify the column to be updated.
      The list of fields is filtered and correspond to the table selected previously.
    3. In the Action field, specify where the column's data will be derived from.
      • Transformation Value: The column will be updated with a value that was assigned in a Ruleset.
        Note: Using this option assumes that a Ruleset was performed before the execution of the Update Adapter. Within the Ruleset, rules were performed that populated the column with a value.
      • Constant Value: The column will be updated with a hard-coded value (which is specified in the next step).
      • NULL: The column will be set to NULL.
    4. In the Value field, specify a value for the column.
      This is used to hardcode values into columns. For example, you may want to always update a Status column to P for processed.
      Note: Value is only used with the Constant Value action.
  11. Use the Where tab to configure the columns to be updated.
    The information specified in the Where tab builds the underlying SQL Where clause.
    1. Click the Add button to add a column.
    2. In the Field field, specify the column to be used.
      The list of fields is filtered and corresponds to the selected table in the Interactions section.
    3. In the Action field, specify where the column's data will be derived from.
      Note: The operator for action is Equal to. For example, suppose you selected Invoice Number as the column to place in the Where clause. The Adapter will construct a Where clause that's equivalent to Where Invoice Number = ?. The value for ? will come from the following list of sources:
      • Transformation Value: A value that was assigned in a Ruleset will be used.
        Note: Using this option assumes that a Ruleset was performed before the execution of the Update Adapter. Within the Ruleset, rules were performed that populated the column with a value.
      • Constant Value: A hard-coded value will be used (which is specified in the next step).
      • NULL: NULL will be used.
    4. In the Value field, specify a value for the column.
      This is used to hardcode values into columns. For example, you may want to always update records where the EDI Status column equals U. You can specify a value of U for the EDI Status column.
      Note: Value is only used with the Constant Value action.
  12. Click Save to complete this task.