DB-related Functions
When you work with databases, you may need to use various functions to handle null values, generate sequential and unique values, and replace special characters. For more information, see the subsections below.
Handle null values
MapForce provides the following functions to handle null values:
•To check at mapping runtime whether a database field is null, use the is-null and is-not-null functions. To see if a table has null fields, query it using the Database Browser in MapForce (see DB Query Pane).
•To set a database field to null, use the set-null function.
•To replace null database values with a string, use the substitute-null function.
For information about handling NULL values in a database, see Null Equal. See also Null Values in Database Components.
Generate sequential and unique values
When you update database records, you might need to create on-the-fly sequential or unique values for those database fields which do not receive any input data from the source. In such cases, you can use the following functions:
•The auto-number function can be used to generate primary key values.
•The create-guid function creates a globally-unique identifier (as a hex-encoded string) for a specific field.
Note that values for database fields can also be written using database-generated values. This option is available in the Database Table Actions dialog box and is particularly useful when you need to generate primary keys.
Replace special characters
When you update database data, you might need to remove special characters (e.g., carriage return/line feed (CR/LF) characters). To achieve this, you can use the following approaches:
1.You can define a node function for a specific database field (or multiple fields) that you need to process. The node function will receive the value of the database field as input, process this value, and then return the outcome to the mapping. For more information about this approach, see Defaults and Node Functions.
2.You can also process database values with the help of MapForce built-in functions. For example, to identify specific characters, including control characters, you can use the char-from-code function. To replace values, use the replace function.