Database Mapping with Database Exception Handling

Critical business processes depend on reliable data and database administrators and other data analysts want to be confident in the integrity of information stored in database tables. During automated ETL (Extract Transform Load) operations or other database import tasks, invalid data might be encountered that jeopardizes success of the procedure. Altova MapForce includes database exception handling to roll back the affected data when an error occurs and optionally proceed with the rest of a database mapping.

For instance, an error in a single record need not prevent execution of a mapping from continuing, such as when certain database constraints prevent the mapping from inserting or updating invalid data.

Enabling database exception handling encloses all changes to the database component inside transactions that can be rolled back in case of error. Users can opt to either proceed with the rest of the mapping or stop further execution. Transactions can be enabled at the database component level, at table level, or for stored procedures.

Exception handling can even be combined with database output logging (tracing) to create a record of errors that occur during automated processing.

Let’s see how it works.

The MapForce Examples folder includes a database mapping and SQLite database that illustrate transaction handling and roll back. Of course, these features are supported for all popular relational databases, for common task like PostgreSQL to SQLite transformation, mapping XML to a SQL Server, converting JSON to Access, and so on.

Data mapping example to illustrate database exception handling

This mapping copies data from two database tables into new tables with similar structure except for one important difference. In the new_addresses table of the target database, is_shipping and is_billing are not permitted to contain null values, whereas they were allowed to be null in the addresses table of the source. This means errors will occur during mapping execution if missing values are encountered.

We will look at three situations, using database exception handling options to manage errors in different ways:

On the first error, roll back all changes and stop mapping execution
Roll back only the transaction with the error and continue
Roll back the top transaction and continue

Roll Back All Changes

In the first situation, we want to apply database exception handling to leave the target database completely unchanged if any error occurs during mapping execution. To implement this option, we can open the Properties dialog for the target database component and enable transaction handling:

Transaction setting example for database exception handling

We can now execute the mapping by clicking the Output button at the bottom of the mapping pane to generate the SQL execution script, followed by choosing the Run SQL-Script option from the Output menu above the script window:

Run SQL script example for database exception handling

When the first error occurs during execution a dialog is displayed that allows us to confirm or override the transaction settings we chose above:

Confirming transaction settings for database exception handling

We’ll simply click OK to confirm our original settings. Mapping execution halts and the Messages window describes the error:

Messages window displays the first error during database exception handling

We can use the built-in MapForce DB Query window to select examine the new_users and new_addresses tables. We expect both to be empty:

A database query verifies transaction roll back

This verifies that any data inserted before the error was rolled back, in other words, removed from the database.

During automated execution under the control of MapForce Server, the component transaction settings would be used without any interruption.

Roll Back One Transaction and Continue

Imagine a case where a huge file of external data needs to be imported into a database, say ten thousand records or more. We expect the external file to be valid, but if a few records have bad data, we want to import all the good ones and document the errors. This is database exception handling situation 2 from the list above, combined with database tracing described in an earlier post.

To implement this database exception handling option for the new_addresses table, we click the table actions button and select these properties:

Transaction settings for next database exceptions handling example

Now we can generate a new SQL script and execute it using the same Run SQL-Script command shown above. The Messages window will log several errors. We can re-run the SQL query in the DB Query window to see the data that was mapped successfully:

SQL query and results for roll back and continue database exception handling example

Additionally, we generated an XML trace file. The image below shows a partial view in the XMLSpy XML editor window for one error, highlighted on line 54:

Trace file logging errors during database exception handling

A trace file like this makes it easy to find and fix a few bad records out of ten thousand!

Roll Back the Top Transaction and Continue

Database exception handling situation 3 satisfies this business requirement: if an address fails to be inserted, then the parent user record should also not be inserted, but the rest of the mapping should continue.This example is described in detail in the MapForce Online Help installed automatically with MapForce and all the demo files shown here. We’ll leave it to you to download a free trial and work through that scenario, or jump right in to implement database exception handling in your own database mappings!