A Database Adapter is an object used to manage database information through the CIC Integration Engine. Database Adapters support these common SQL operations (and are created and defined in the CIC Studio):

Select
Insert
Update
Delete

Business Use

Database Adapters are used primarily to select data from and insert transformed data into a database. However, they can also aid in the management of database information. They can update columns in a single record or a group of records. They can also delete an individual record or a range of records. Database interaction is confined to a single database, but can span multiple tables.

Database-Select Adapters select data from one or more tables. You can configure which records are selected, how they are ordered, and how tables are joined together.
Database-Insert Adapters insert records into one or more tables. You can configure transaction boundaries by enforcing commit and roll back.
Database-Update Adapters update one or more columns on one or more tables. Common uses include changing a status from one value to another.
Database-Delete Adapters delete records from one or more tables. You can configure transaction boundaries by enforcing commit and roll back.

Batch data processing

Batch data processing is an efficient way of processing high volumes of data is where a group of transactions is collected over a period of time. Data is collected, entered, processed, and then the batch results are produced. The batch size (default = 5000 records) can be adjusted in Database Adapters (V2) through changes made to system files, depending on server type.

There is a system property (com.cleo.database.adapter.batchSize) that is found in ClarifyServer_Standard.ini (for Single Server) and share.properties (for Cluster).

Example:

Default
-Dcom.cleo.database.adapter.batchSize=5000
If you wish to configure to 1000:
-Dcom.cleo.database.adapter.batchSize=1000

Batches are flushed at the end of every DB Adapter execution to ensure nothing gets lost in an un-flushed batch. One caveat: batching also depends on how your DB Objects are configured. For example, if you are only sending the DB Adapter one single item at a time, you're not truly achieving batch ability. Check the relevant DB Schema for configuration details.

Note: You must restart the server to process changes.

How the Object Works

Business Processes use Database Adapters to perform database operations on data. Selecting data for transformation or writing data immediately after transformation activity are the most common uses. In addition to the data, Business Processes can supply runtime directions to the Adapter. For example, you may want to supply the order status of the records you want to select from the database. The Business Process can supply the status value (and other common runtime values) which the Database Select Adapter will use to filter when selecting the data.

Process Summary

To use a Database Adapter, you must:

Create a Database Adapter.
Define a Database Adapter.
Reference a Database Adapter within a Business Process.

Defining Database Adapters

Create a Database Adapter to manage database information. A Database Adapter can delete, insert, select, or update information from a database.

Select File | New | Database Adapter from the main menu bar. The New Database Adapter window appears.
Provide the Source folder, Package, and Name (if not already done so).
Click Finish. An editor appears.

Defining Delete Adapters

Defining this object instructs Studio of the database, tables, records, and columns to delete data from.

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

Under the Settings section's 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.
In the Data Source field, select the Data Source.
The Data Source represents the connection to the Database.
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.
Under the Interactions section, define a Delete interaction. Click the Add button.
Select the new Interaction, choose Delete from the interaction drop-down, and type a name for the interaction.
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.
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
3. Query Timeout
  - Sets a given timeout (in seconds) at the interaction level. Default timeout of "0" indicates JDBC driver standard timeout.
For a table within the Interaction:
Delete Adapters allow you to configure the following behavior:
- Settings: transaction boundaries (i.e. commit and rollback), selection mode.
- Matches: defines the 'where' clause of an SQL statement (Delete x where y = ?)
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
Use the Matches tab to configure where the delete should occur. (Delete x where y = ?)
Click Save to complete this task.

Defining Insert Adapters

Defining this object instructs Studio which database, tables, records, and columns to insert data into.

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

Under the Settings section's 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.
In the Data Source field, select the Data Source.
The Data Source represents the connection to the Database.
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.
Under the Interactions section, define an Insert interaction. Click the Add button.
Select the new Interaction, choose Insert from the interaction drop-down, and type a name for the interaction.
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.
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
For a table within the Interaction:
Insert Adapters allow you to configure the following behavior:
- Settings - transaction boundaries (i,e. commit and rollback), selection mode.
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 insert. 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 insert 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.
Click Save to complete this task.

Defining Select Adapters

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

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

Under the Settings section's 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.
In the Data Source field, select the Data Source.
The Data Source represents the connection to the Database.
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.
In the Interactions section, define a Select interaction. Click the Add button.
Select the new Interaction, choose Select from the interaction drop-down, and type a name for the interaction.
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.
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
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)
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
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.
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).
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 Numbercolumn 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.
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 Ufor 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.
Click Save to complete this task.

Defining Update Adapters

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

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

Under the Settings section's 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.
In the Data Source field, select the Data Source.
The Data Source represents the connection to the Database.
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.
Under the Interactions section, define a Update interaction. Click the green Add button.
Select the new Interaction, choose Update from the interaction drop-down, and type a name for the interaction.
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.
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
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.
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.
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.
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.
Click Save to complete this task.

Database Adapters

Business Use

Batch data processing

How the Object Works

Process Summary

Defining Database Adapters

Defining Delete Adapters

Defining Insert Adapters

Defining Select Adapters

Defining Update Adapters

Comments

Contact Us

Email Us

Support Links

Business Use

Batch data processing

How the Object Works

Process Summary

Defining Database Adapters

Defining Delete Adapters

Defining Insert Adapters

Defining Select Adapters

Defining Update Adapters

Related articles