Altova MapForce 2023 Enterprise Edition

When running mappings that modify a database, MapForce (or MapForce Server, or a MapForce-generated program) may potentially encounter various database-related errors. For example, the database account may not have enough privileges to perform a specific database action, or some database constraints might be in place that prevent the mapping from inserting or updating invalid data. The latter may happen, for example, when a table column is mandatory but the mapping does not supply a value for it.

To make it possible to roll back data if database exceptions are encountered, you can configure the mapping to use database transactions. As further described below, you can enable transactions at database component level and at table action level.

The transaction rollback settings you configure in MapForce are preserved when you run the mapping either with MapForce Server or with a MapForce-generated program.

Transactions at database component level

To enable transactions at database component level, do the following:

1.Right-click the database component, and select Properties from the context menu (Alternatively, double-click the title bar of the database component). This opens the Database Component Settings dialog box.

2.Select the Use transactions check box.

Doing this encloses all changes done by the database component inside a transaction that will be rolled back in case of error. When an error occurs during mapping execution, the outcome depends on the option you select from the drop-down list:

•Rollback top transaction and stop — The transaction which encloses all the changes done by the database component is rolled back and execution of the mapping stops.

•Rollback top transaction and continue — Same as above, but the mapping continues to run after rollback (for example, in order to process a second target component, if one exists).

Some mappings may contain multiple database components. These, in their turn, may all use the same database connection, or connections to completely different databases. The outcome of such mappings in case of database error depends on the execution engine:

•If the mapping is run with MapForce, only one target component can be executed when the mapping runs. This is the component where the Preview button is enabled. If a database error occurs in that component, and if the Use transactions check box is enabled, all the changes done by the component will be rolled back.

•If the mapping is run with MapForce Server or a MapForce-generated program, all the target components are executed, sequentially. In this case, when a database error occurs, the rollback will take place for the database component where the error occurred. The mapping will stop or continue executing the next target component depending on the value you selected from the When an error occurs drop-down list, as mentioned above.

For reference to all the settings available on the Database Component Settings dialog box, see Database Component Settings.

Transactions at table action level

Transactions at table action level enclose each individual table action (insert, update, and so on) inside a transaction. With this setup, if some insert or update action fails on a record, it is possible to roll back only the current failed action and continue processing other records. To enable transactions at table action level, select the Use transactions check box from the Database Table Actions dialog box (see also Database Table Actions Settings).

The available options are as follows:

•Rollback top transaction and stop — If the mapping is going to update multiple tables having a parent-child relationship, it is possible to enable transactions not only for the parent table, but also for the child table. Since transactions can be enabled at multiple levels, "top" refers to the transaction at the highest level in the hierarchy. This is the transaction from the database component level, if you enabled transactions at that level. Otherwise, it is the transaction defined in the topmost table action, assuming that the component contains multiple nested table actions. To understand this option, recall that it is possible for a component to update multiple tables hierarchically, as described in Inserting Data into Multiple Linked Tables. For example, a component may have a parent "Insert" action that inserts person records and a nested "Insert" action that inserts one or more addresses for each person record into a separate table. Since you can enable transactions for both insert actions, "top" refers to the highest level in the hierarchy (in this example, the people insert action). Therefore, when an exception is encountered while inserting an address, this rolls back the top transaction (which is at person table level, in this example) and execution stops. In other words, no person record will be inserted if it wasn't possible to insert the person's address in first place.

•Rollback top transaction and continue — Same as above, but the mapping continues to run after rollback (for example, to process another target component, if one exists).

•Rollback current transaction and stop — When an exception is encountered, this rolls back only the changes enclosed in the current transaction; changes done previously outside of the current transaction will be committed.

•Rollback current transaction and continue — Same as above, but the mapping continues to run after rollback.

Stored procedure-level transactions

You can also enable database transactions for stored procedures that are called as data target. For an example, see Stored Procedures in Target Components. Transactions are not supported for stored procedures that are called as a data source (without input parameters), or those that are called like a function (with both input and output).

Rollback

To see if transactions are going to occur at mapping runtime, click the Output pane and observe the pseudo-SQL code generated by MapForce. The image below illustrates the preview of a mapping where transactions are enabled at component level and table action level. As shown below, a main transaction encloses the entire operation (identified by the comments --begin transaction and --commit transaction). Also, multiple smaller transactions occur for each insert operation (these are identified by the SAVEPOINT command).

When the mapping runs in MapForce Server, or from a MapForce-generated program, the rollback will take place based on the options you have configured while designing the mapping, as described above. For example, if you selected the option Rollback top transaction and stop, a failed insert operation will cause all the changes to be rolled back, even if some other inserts may have been successful. However, if you selected Rollback current transaction and continue, only the failed insert will be rolled back to the nearest savepoint, and the mapping will continue to attempt inserting other records.

If you run a transaction-enabled mapping directly in MapForce with the menu command Output | Run SQL-Script, a dialog box informs you when the database exception is encountered, for example:

You can then change the subsequent behavior as follows:

Rollback this transaction and stop

•The current transaction is already rolled back at the time the dialog comes up

•An error is shown at the top of the preview window and in the Messages window

•Any parent transaction is committed

•Mapping execution stops with an error code

Rollback this transaction and continue

•The current transaction is already rolled back at the time the dialog comes up

•The error is treated as a warning, and will be shown in the Messages window when mapping execution finishes.

•Any parent transaction is not affected

•Mapping execution continues with the next record (if this transaction is not on component level) or with the next component (MapForce Server only)

Rollback top and stop

•The current transaction is already rolled back at the time the dialog comes up

•An error is shown at the top of the preview window and in the Messages window

•Any parent transaction is rolled back

•Mapping execution stops with an error code

Rollback top and continue

•The current transaction is already rolled back at the time the dialog comes up

•The error is treated as a warning, and will be shown in the Messages window when mapping execution finishes.

•Any parent transaction is rolled back

•Mapping execution continues with the next record (if this transaction is not on component level) or with the next component (MapForce Server only)