Database Table Actions and transaction processing
|
| Previous Top Next |
Database Table Actions and transaction processing |
Table actions allow you to define how specific table data are to be manipulated. MapForce supports the table actions: insert, update and delete. One or more fields are used to compare source and target data to determine if the table action is to be executed.
The Table Action dialog box allows you to define the:
| • | fields that will be compared (e.g. PrimaryKey) |
| • | operators used for the comparison (equal, equal ignore case), and |
| • | action taken, when all conditions of each column are fulfilled. |
Data may originate from any data source: XML file, database, text, Constant component etc. The mappings that define which data are to be manipulated, are created using connectors in the Mapping window.
| • | Table Actions are processed from left to right. In the example above, the Update if... column is processed and then the Insert Rest... column. |
| • | All the conditions of one column must be satisfied if the table action is to be executed. When this is the case, all those fields are updated where a mapping exists, i.e. a connector exists between the source and target items in the Mapping window. |
| • | If a condition is not satisfied, then the table action for that column is ignored, and the next column is processed. |
| • | If none of the conditions are "true", no table action takes place. |
Delete data in child tables:
| • | Standard setting when you select the Update if... action. |
| • | Necessary if the no. of records in the source file might be different from the no. of records in the target database. |
| • | Helps keep the database synchronized (no orphaned data in child tables) |
Effect:
| • | The Update if... condition is satisfied when a corresponding key (or any other field) exists in the source XML file. All child data of the parent table are deleted. |
| • | Update if... selects the parent table, and thus the child tables related to it, on which the "Delete data in child tables" works. |
| • | If the update condition (on the parent) is not satisfied, i.e. no corresponding key/field in source XML file exists, then child data are not deleted. |
| • | Existing database records, that do not have a counterpart in the source file, are not deleted from the database, they are retained. |
Ignore input child data:
Use this option when you want to update specific table data, without affecting any of the child tables/records of that table.
For example, your mapping setup might consist of 3 source records and 2 target database records.
You would therefore need to:
| • | define an Update if... condition, to update the existing records |
| • | activate the Ignore input child data check box, of the Update if... column, to ignore the related child records, and |
| • | define an Insert Rest... condition for any new records, that have to be inserted. |
The "Use Transaction" check box allows you to define what is to happen if a database action does not succeed for whatever reason. When such an exception occurs, a dialog box opens prompting you for more information on how to proceed. You then select the specific option and click OK to proceed. Activating this option for a specific table (using the table action dialog box), allows that specific database table to be rolled back when an error occurs.
The transaction setting can also activated for the database component, by right clicking it, in the Component Settings dialog box of the respective database component. In this case, all tables can be rolled back.
No Transaction options set:
If the transaction check box has not been activated in the table options, or in the component settings, and an error occurs:
| • | Execution stops at the point the error occurs. All previously successful SQL statements are executed and the results are stored in the database. |
Transaction option set at database component level:
| • | Execution stops at the point the error occurs. All previously successful SQL statements are rolled back. No changes are made in the database. All previously successful SQL statements for that for the database and all its tables can be rolled back. |
Transaction option set at Table Actions level:
| • | The Transaction exception dialog box appears with the "Rollback all and stop" option disabled. The failed SQL statement for that specific table can be rolled back. |
Transaction option set at both database component and table action level:
| • | The Transaction exception dialog box appears with the "Rollback all and stop" option enabled. All previously successful SQL statements for that for the database and all its tables can be rolled back. |
Hitting the Cancel button, rolls back the current SQL statement and stops.
Please note:
The transaction prompts are only displayed when the transformation is performed interactively!
Generated code performs a rollback (and stop) when the first error is encountered.
|