Commit

<< Click to Display Table of Contents >>

Navigation:  EDP > Professional EDP > Package >

Commit

Commit Types

Example

Errors When Committing Data

 

The Commit step is the process where EDD records are inserted into an EQuIS database. After an EDD package is created in EDP, click the Commit button (located in the Package group on the Professional tab of EDP). Clicking either the icon or the drop-down arrow will display a drop-down menu with the various commit types. Select the desire commit type to commit the EDD package.

EDP_Commit_Options

Click the drop-down selection above to go to the commit type documentation.

 

 

Commit Types

 

See below for an example of what data is loaded to the database based on the commit type selected.

 

Commit Type

Does

Does NOT

Description

Insert Only

Inserts new records.

Replace any existing records.

This option inserts new records, but does not replace existing records. If a record with matching primary keys already exists in the database, the new record remains in the package in EDP. An error is reported, because the new record cannot be inserted, and the existing record is not modified.

Merge Only

Replace existing null records.

Replace existing records that are not null.

Insert new records.

This option merges data if it already exists in the database, but prevents new records from inserting. Only null values in the existing record will be replaced by the incoming non-null values. If the primary key does not match what is already been loaded, the data will not be committed.

Update Only

Replace existing records, but not if new data is null.

Replace existing records if new data is null.

Insert new records.

This option merges data if it already exists in the database, but prevents new records from inserting. All values in an existing record will be replaced by new incoming values, unless the incoming value is null. Any null values will not replace non-null data. If the primary key does not match what has already been loaded, the data will not be committed.

Replace Only

Replace existing records.

Insert new records.

This option replaces data if it already exists in the database, but prevents new records from inserting. If a record with matching primary keys already exists in the database, it will be completely overwritten by the new incoming record. If the primary key does not match what has already been loaded, the data will not be committed.

Insert and Merge

Replace existing null records.

Inserts new records.

Replace existing records that are not null.

This option inserts new records and replaces existing null values with incoming values. This type of commit will only replace values in an existing record if the existing value is null.

Insert and Update

Replace existing records, but not if new data is null.

Inserts new records.

Replace existing records if new data is null.

This option inserts new records and updates both null and non-null values in existing records. All values in an existing record will be replaced by new incoming values, unless the incoming value is null. Any null values in the incoming data will not replace non-null data in the existing records.

Insert and Replace*

Replace existing records.

Inserts new records.


This option inserts new records and replaces existing records. If a record with matching primary keys already exists in the database, it will be completely overwritten by the new incoming record.

Format Specific



Commit type is set for each table in the format's custom handler. For example, a format may be designed to commit data to DT_SAMPLE and DT_FIELD_SAMPLE tables through Insert and Merge, but all other tables would use Insert and Update.

 

These commit types are also available under the EDP section in the EQuIS Enterprise Administration Setup tab. This will determine the default commit type for EDDs being processed by EQuIS Enterprise. During the EDP Commit process, there is an option to cancel during the transaction. Learn more here.

 

*Note: The Insert and Replace commit type will replace existing records with NULL values with the exception of the refvals format. This is due to the fact that when the format is opened, it loads lookup values from the database. Then, during the Create step, those values are combined with the EDD values. Values that appear null in the EDD will not appear as nulls in the database after the Commit step.

 

 

Example

 

In this example a portion of the MW-01 data already exists in the database. The EDD contains some data already in the database, adds new values to the MW-01 location, and adds a new location, MW-02. The breakdown of what data is added to the database for each commit type is shown in this example.

 

Data

Location

Total Depth

Driller

Loc_Type

Exists in Database

MW-01

 

E. Miller

MW

In EDD

MW-01

30

J. Smith

 

MW-02

25

J. Smith

 

 

 

This is the data in the database after the EDD is loaded based on the Commit Type selected:

 

Commit Type

SYS_LOC_CODE

TOTAL_DEPTH

DRILLER

LOC_TYPE

Description

Insert Only

MW-01

 

E. Miller

MW

The new MW-01 in the EDD does NOT get inserted and the old record remains the same. The MW-02 is "new" and inserted in its entirety.

MW-02

25

J. Smith

 

Merge Only

MW-01

30

E. Miller

MW

Total Depth is merged in for MW-01, and everything else remains the same. The MW-02 is "new" and is not inserted.

Update Only

MW-01

30

J. Smith

MW

Total Depth is filled in for MW-01 and Driller is updated with the new value. The MW-02 is "new" and is not inserted.

Replace Only

MW-01

30

J. Smith

 

Everything is replaced for MW-01 (except the primary key), including the new NULL value for Loc_Type. The MW-02 is "new" and is not inserted.

Insert and Merge

MW-01

30

E. Miller

MW

Total Depth is merged in for MW-01, and everything else remains the same. The MW-02 is "new" and inserted in its entirety.

MW-02

25

J. Smith

 

Insert and Update

MW-01

30

J. Smith

MW

Total Depth is filled in for MW-01 and Driller is updated with the new value. The MW-02 is "new" and inserted in its entirety.

MW-02

25

J. Smith

 

Insert and Replace

MW-01

30

J. Smith

 

Everything is replaced for MW-01 (except the primary key), including the new NULL value for Loc_Type. The MW-02 is "new" and inserted in its entirety.

MW-02

25

J. Smith

 

Legend:

New values added to database.

 

 

Errors When Committing Data

 

In the event that an error occurs at the Create or Commit step in EDP Professional, the process should be canceled and the errors resolved before completing the data load.

 

In the event that an error occurs at the Create or Commit step in Enterprise EDP, only the Administrator for that Workflow Agent (also referred to as the FileProcess in EDP) is notified.

 

The administrator is determined by looking in ST_SERVICE_RESERVATION.USER_ID for the FileProcess agent. That USER_ID value is then joined to the ST_USER table to find the user account (i.e. the administrator for that workflow agent).

 

The data submitter (and others subscribed) will only see the "accept" or "reject" notice if the data either passes, or if there are errors with the data in the EDD itself. The data submitter (and others subscribed) are not notified of create or commit errors, because they are related to either the format or the database, and technically are not the submitters responsibility.
 

Tip: Format Controls Which Commit Types are Available: Users with write access to the ST_CONFIG table can now limit which "Commit" types are available for a given EDP format. For example, the following records in ST_CONFIG will only allow "Insert and Replace" to be shown:
 

CONFIG_SECTION    CONFIG_KEY    OBJECT_TYPE    OBJECT_VALUE    STRING_VALUE

EarthSoft.EDP.EddCustomHandler.ShowUpdateMode    EFWEDD    commitmode    Merge    false

EarthSoft.EDP.EddCustomHandler.ShowUpdateMode    EFWEDD    commitmode    None      false

EarthSoft.EDP.EddCustomHandler.ShowUpdateMode    EFWEDD    commitmode    Update   false

 

Control of Commit types for a specified format as well as the name of the Commit type, can now be implemented via a format's custom handler (i.e., *.vb file).