Defining Database Select Adapters

Defining this object instructs Clarify which database, tables, records, and columns to select data from.

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. In the Interactions section, define a Select interaction. Click the Add button.
  5. Select the new Interaction, choose Select 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.
    Typically, for a Select interaction, all tables are selected. However, this is not always the case. In certain situations, you may only want to select data from certain tables.
    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:.
    Select Adapters allow you to configure the following behavior:
    • Settings: Transaction Boundaries (i.e. commit and rollback), Selection Mode
    • Parameters: Runtime values passed to the Adapter for purposes of filtering data results
    • Order: Specify the order in which the records are returned (i.e. which records are returned first)
    • Joins: Specify how tables are linked together (for example, Invoice and Invoice Detail tables may be linked together via Invoice Number)
    • Selection Criteria: Specify which records are returned (i.e. an SQL "where" clause)
  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. 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
  10. In the Parameters tab:
    1. Click on and review the Parameter sub-tab.
      Note: The values for Parameters are usually the same as the over-all interaction.
  11. In the Order tab:
    1. Click the Add button to create ordering/sorting logic. Select the column to be used to sequence the data selected. Indicate whether the order should be Ascending (lowest value first, highest value last) or Descending (highest value first, lowest value last).
      You may add as many columns as are in the table. These entries correspond directly to the Order by SQL clause.
      Note: If the order of the data in the table is not important to your process, you can skip this step. For example, if you are selecting invoices from your database, it may not matter in which order they are selected. However, if the invoice has detail lines, you probably want to order those detail lines by line number in Ascending order (1 through N).
  12. In the Joins tab:
    Click the Add button to join the table with another table.

    Tables that are eligible to be joined must be hierarchically arranged in the Database Schema. For example, if tables are organized hierarchically as parent and child (parent above child, child underneath parent), the parent and child tables can be joined. An example of this is Invoice (parent) and Invoice Detail (child). Only parent tables and child tables can be joined. A parent table may have more than one child tables. Each child table can be joined to the parent table.

    Note: If your tables don't have any parent-child relationships in your EDI schema, you can skip this step.
    1. Click to set a table to join. The list of tables is filtered. Only those tables that are hierarchically organized as children of the current table may be selected.
    2. Click to set related key column. The child table's columns are displayed. Choose the column that links the child table to the parent table.
      Tables are linked by common columns. For example, an Invoice table and Invoice Detail table most likely have a column in common: Invoice Number. That column in the Invoice table may uniquely identify the Invoice record. In the Invoice Detail table, it associates the detail record with a specific record in the Invoice table. The Invoice Number column in those tables link them. These two columns are good candidates for join columns.
    3. Click to set key column. The parent table's columns are displayed. Choose the column in the table that links the parent table to the child table.
    4. (Optional) You may need to add more columns to the join specification. Click the Add button to create additional join relationships. For each, set the related key column and key column, as above.
      Normally, one join relationship between two tables is enough to achieve the desired result: linking parent-child records together. However, at times, more than one join relationship is needed. For example, when sending invoices, it's common to have the Invoice Detail and an Address table joined to the Invoice Header table. Invoice Detail and Address will be siblings (at the same hierarchical level), with Invoice Header being the parent.
  13. In the Selection Criteria tab:
    Specify the SQL Where clause filters. For example, suppose you want to select all invoices that have not been processed. If your Invoice table has a Status column with a value of U for unprocessed, Selection Criteria allows you to select only those records.

    Selection Criteria can be grouped together to form complex expressions. For example, you may want to select Invoice records that are "unprocessed" (Status = "U") and are over $1,000 (Amount >= 1000).

    You can specify All or Any for the group. Expressions entered under an All group level will generate SQL “AND” conditions. Expressions entered under the Any group level will generate SQL “OR” conditions. You can mix these group selections to generate the complex selection criteria needed for your database select adapters.

    Note: If you don't need to specify any Selection Criteria, you may skip this step.
    1. Select a grouping option: All or Any
    2. Add a Selection Criteria Expression by clicking Add.
    3. In the Name column, specify the column to use in the expression.
      The list of columns is filtered based on the current table.
    4. In the Condition column, specify the operator.
      • Equal to: the value in the column is equal to a specified value (A = B)
      • Not Equal to: the value of the column is not equal to a specified value (A != B)
      • Less than: the value of the column is less than a specified value (A < B)
      • Less than or Equal to: the value of the column is less than or equal to a specified value (A <= B)
      • Greater than: the value of the column is greater than a specified value (A > B)
      • Greater than or Equal to: the value of the column is greater than or equal to a specified value (A >= B)
      • Like: used for character or text columns. Commonly used in conjunction with wildcards "%". (A Like "%United States%)
      • Is Null: the value of the column is Null. Null means no value has been assigned to the column.
      • Is not Null: the value of the column is not Null.
      • Between: the value is between two specified values. (A >= B and A <= C)
    5. In the Value column, specify the value for the column.
      Enter the Type:
      • String Constant: a character or text value
      • Boolean Constant: a true or false value
      • Column: another column in the table (example: Order Status Not equal to Ship Status)
      • Parameter: a runtime value supplied by the Business Process in which the Adapter is called
      Enter the Value. The value may appear as a text field for you to enter a constant value. In other cases, it may appear as a drop-down with a list of choices.
    6. Continue to add groupings and expressions until the selection criteria is complete.
  14. Click Save to complete this task.