Behavior when modifying database column values from optional source elements

When updating or inserting database columns, you can define different behaviors for a missing, empty, or nil source.

Behavior with no Database Policy

When Inserting or Updating data into a database column by connecting a transform which is defined as optional in the schema model, you might want to consider the behavior for the possible source input states: Missing, Empty, or Nil. The behavior can be default or customized by enabling a Database Policy.

Table 1 defines the source states and the behavior without a Database Policy enabled.

Tables 2 and 3 define the behavior of enabling a Database Policy to check the source state and to then take a specific configured action.

Table 1. Behavior with no Database Policy on transforms linked to a column in an Insert or Update operation
Source state Definition Behavior
Missing Source The input document does not contain the source element. The column will not be passed in the SQL statement sent to the database server. The outcome is determined by the definition of the target column in the database:
  • If the column is defined with a default value, this value is set by the database system.
  • If the column is defined as nullable, and no default is defined, the column is set to null by the database system.
  • If the column is defined as not nullable, and no default is defined, the database will return a SQL exception.
Empty Source The input document contains the source element, but that source is empty. IBM® Integration Bus passes the value returned by "getValue" for the source element as the parameter value for the column in the SQL statement sent to the database. For example, an element of the String type will return the empty String value, so the target database column would be set with an empty string, "".
Nil Source The input document contains the expected source, and it is nil. The value returned by "getValue" is set to NULL.

Behavior for Insert with an enabled Database Policy

When Inserting data into a database column, you can enable a database policy on each transform mapping a single value from a source element. This allows you to choose one of the following actions for each of the input source states: Missing, Empty, or Nil.

Table 2. Behavior with a Database Policy enabled on transforms linked to a column in an Insert operation
Actions for source state Behavior
Exclude column from database operation Insert the database default value for the column. The column is excluded from the SQL statement sent to the database. This option is only enabled if the target database column has a default value defined in the database model from the associated .dbm file.
Insert the empty String value "" This option is only enabled if the target database column is defined as any character string type in the database model from the associated .dbm file.
Set to NULL This option is only enabled if the target database column is defined as nullable in the database model from the associated .dbm file.
Throw a map error Produces a map error:
  • Missing: BIP3970
  • Empty: BIP3971
  • Nil: BIP3972
For more information, see: BIP3000-3999: Built-in nodes.

Behavior for Update with an enabled Database Policy

When Updating data in a database column, you can enable a database policy on each transform mapping a single value from a source element. This allows you to choose one of the following actions for each of the input source states: Missing, Empty, or Nil.

Table 3. Behavior with a Database Policy enabled on transforms linked to a column in an Update operation
Actions for source state Behavior
Exclude column from database operation The column is excluded from the SQL statement sent to the database. The value of the column currently in the database is not changed.
Set to the empty String value "" This option is only enabled if the target database column is defined as a character string type in the database model from the associated .dbm file.
Set to NULL This option is only enabled if the target database column is defined as nullable in the database model from the associated .dbm file.
Throw a map error Produces a map error:
  • Missing: BIP3970
  • Empty: BIP3971
  • Nil: BIP3972
For more information, see: BIP3000-3999: Built-in nodes.