Example: Transaction Rollback
This example illustrates possible ways to handle database exceptions when running a mapping that modifies a database. The database used in this example stores user records (users table) and addresses (addresses table). Each user can have zero, one, or two addresses (for example, a home address and a work address). Namely, each address in the addresses table contains a user_id field which points to the id field in the users table. The users table is therefore a "parent" table for addresses. The listing below illustrates the SQL creation scripts for both tables (note the syntax is applicable to a SQLite database):
CREATE TABLE |
Source tables
The business requirement is to copy all data from the users and addresses table to some new tables, namely, the new_users and new_addresses tables. These are almost identical to the users and addresses tables. The only difference is that the table new_addresses does not allow null values for the columns is_shipping and is_billing, as highlighted in the code listing below. This is important because it means that exceptions will occur if null values are encountered.
CREATE TABLE new_users (id INTEGER NOT NULL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT UNIQUE NOT NULL);
CREATE TABLE new_addresses (id INTEGER NOT NULL PRIMARY KEY, user_id INTEGER NOT NULL, is_shipping INTEGER NOT NULL, is_billing INTEGER NOT NULL, type TEXT NOT NULL, city TEXT NOT NULL, street TEXT NOT NULL, number INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES new_users (id) ) ; |
Target tables
The mapping that copies all data from the old to new tables is illustrated below. You can find this mapping at the following path: <Documents>\Altova\MapForce2023\MapForceExamples\Tutorial\DatabaseExceptions.mfd.
As illustrated above, this mapping is configured to copy data verbatim from a source database component to a target database component. No other transformations take place, for the sake of simplicity. In this example, both the source and target component belong to the same database, only the source and target tables are different. Click the 
button and notice that all the records are set to be deleted before each insert action. This ensures that the target tables are always empty before each insert.
As stated before, exceptions will occur if the source addresses table contains null values. You can check that the source table contains null values as follows:
1.Click the DB Query pane.
2.Right-click the addresses table, and select Show in SQL Editor | SELECT.
3.Click Execute Query 
.
As illustrated above, there are various NULL fields in the source addresses table. Considering this, when running this mapping, you have various options to deal with exceptions, for example:
•A) If any exception is encountered, roll back all changes. In other words, do not insert any records if the source data contains invalid records.
•B) If any exception is encountered, skip the records where the exception occurs but keep inserting records that are valid.
Scenario A: Rollback all changes on exception
Business requirement: I want to roll back all changes to the database if an exception occurs. To configure the mapping to accomplish this:
1.Right-click the title bar of the target database component and select Properties from the context menu. Alternatively, double-click the title bar. This opens the Database Component Settings dialog box.
2.Select the Use Transactions check box, and choose rollback top transaction and stop.
When the mapping is configured as shown above, any encountered exception will cause the top-level transaction at database component level to be rolled back. To run the mapping with these settings:
1.Click the Output pane.
2.On the Output menu, click Run SQL-Script. At this stage, the mapping encounters an exception because of a null value in the source addresses table. The Database Transaction Exception dialog box appears.
3.Leave the default preselected option unchanged and click OK.
The result is as follows:
•All the changes are rolled back.
•No records are inserted in the new_users table
•No records are inserted in the new_addresses table
Scenario B) Rollback current transaction and continue execution
Business requirement: I want to skip records which generate an exception but keep inserting valid records. To configure the mapping to accomplish this:
1.Click the button next to the new_users table, select the Use transactions check box and choose rollback current transaction and continue
2.Click the 
button next to the new_addresses table, select the Use transactions check box and choose rollback current transaction and continue
When you run the mapping with these settings, the Database Transaction Exception dialog box is displayed. Select the options as shown below to roll back only the current transaction and keep running the mapping:
The result is as follows:
•All erroneous transactions are rolled back.
•5 users out of 5 are inserted (because no user record generated any database exception)
•2 addresses out of 5 are inserted (because 3 addresses contain null values and generated exceptions)
Scenario C) Rollback top transaction and continue execution
Business requirement: If an address fails to be inserted, then the parent user record should also not be inserted. To configure the mapping to accomplish this:
1.Double-click the title bar of the database component, and clear the Use transactions check box.
2.Click the button next to the new_users table, select the Use transactions check box and choose rollback current transaction and continue
3.Click the button next to the new_addresses table, select the Use transactions check box and choose rollback top transaction and continue
When you run the mapping with these settings, the Database Transaction Exception dialog box. Select the options as shown below, and repeat the choice as many times as prompted:
The result is as follows:
•2 users are inserted (Sharda Junker, Tobie Hughey)
•1 address is inserted
Explanation: The user "Sharda Junker" is the only user who does not have an address. No exceptions occurred for this record, so it was inserted. The second user, "Tobie Hughey", was inserted because it is the only user where no exceptions occurred at address level. All other user records were not inserted because they had at least one address where an exception occurred and the transactions were rolled back.
There are two addresses which don't have nulls and thus don't throw exceptions. These are addresses with id=1 and id=4. The insert transaction for the first address, however, was rolled back because the parent user transaction had to be rolled back. Therefore, only the address with id=4 was inserted.
Note that the same result can also be achieved as follows:
1.Click the button next to the new_users table, select the Use transactions check box and choose rollback current transaction and continue
2.Click the button next to the new_addresses table, and clear the Use transactions check box.
Running the mapping with MapForce Server
If you have licensed MapForce Server, you can also run the mapping at the command line, on a Linux, macOS, or Windows machine, as follows:
1.Compile the mapping to a MapForce Server execution file (.mfx) with the menu command File | Compile to MapForce Server Execution File, see also Compiling Mappings to MapForce Server Execution Files.
2.Copy the .mfx file and the ExceptionsDemo.sqlite database to a directory on the server machine. Let's call it the "working directory".
3.Change the command prompt to the working directory and run MapForce Server with the command below:
mapforceserver run DatabaseExceptions.mfx |
Notes:
•mapforceserver is the path to the MapForce Server executable as applicable for your operating system. Change the path to the .mfx file as applicable. For example, on a Linux machine, the path is: /opt/Altova/MapForceServer2023/mapforceserver.
In server execution, you can also run mappings as an API call, or as FlowForce Server jobs, either on demand or on recurring basis. For more information, see Automation with MapForce Server.