Modifying data in a database by using mapping

You can use database transforms in your graphical data maps to insert new rows of data, or to update or delete existing rows of data, in your database tables. For each database transform in your graphical data map, the Graphical Data Mapping editor uses a database definition file (.dbm file) to determine the name and structure of the database that you want to access. You can start the wizard to create a database definition file when you create a database transform in a graphical data map.

If you connect elements from an input object to database columns within a database transform in your graphical data map, every input message that is processed by your map at run time must include those elements. If a message is either missing an element that is connected to a database column, or does not provide a valid value for that database column, an exception is raised when the message is processed by the map. Input elements that you connect to nullable database columns must provide either a valid value, or the NULL value. For more information about null values, see XMLNSC empty elements and null values.

When you add a database Insert, Update or Delete transform to a graphical data map, the transform is displayed as an additional output target to which you can connect input objects. When your map is run, a database transform calls a single operation on the configured database server. If you connect a repeating input element to the database transform, the Graphical Data Mapping editor moves the database transform inside a nested "For Each" transform from the repeating input.

A database Insert, Update, or Delete transform are created as a transform group, comprising the database operation and a Return transform. The database operation transform for Insert and Update are nested transforms in which the individual mapping to the database table columns are made. The Return transform is an optional transform that allows a nested mapping to be entered if the database operation is successful. If you do not want to use the Return transform, you can delete it from the transform group. If you must provide some mapping for when a failure is returned from the database operation, you can add a Failure transform into the transform group. The Failure transform provides a nested transform that is entered if the database system returns a failure.

If the insert, update, or delete is conditional on a test result, you can change the Insert, Update, or Delete transform to an If transform. Before you change the transform, ensure that the Insert, Update, or Delete transform is not part of a transform group. Remove any Return or Failure transform then select an If transform in place of the Insert Update or Delete. The Insert, Update, or Delete transform is moved into the nested mapping of the If transform. You can then add any required Return and Failure transforms.

The following topics describe how to modify data in a database table:

• Inserting data into a table
• Updating data in a table
• Deleting data from a table