DB Component Settings
After you add a database component to the mapping area, you can configure various database settings in the Component Settings dialog box (screenshot below). You can open the Component Settings dialog box in one of the following ways:
•Double-click the component title bar.
•Right-click the component and click Properties.
•Select the component in the mapping. Then click the Component menu and select Properties from the context menu.
The available settings are listed below.
Database
This group displays database connection information. Click Change to select a different database or to redefine the database objects in the existing database component. Connections to tables with the same names will be retained. You can also change the tables in the component, by right clicking a database component and selecting Add/Remove/Edit Database Objects.
Specifies the absolute or relative path of the current data source.
|
Specifies the name of the connection. This name is generated automatically by MapForce. Typically, it is the same as the data source name, but it may also be an alias if you are connecting with Altova Global Resources. If there are multiple database components with the same connection in the mapping, the connection name will look as follows: <connection1>, <connection2>, etc.
|
Specifies the database type (e.g., SQLite).
|
Displays the current database connection string. This read-only field is generated based on the information you supply when you create or change a database connection.
|
Login settings
The login settings are used for all code generation targets and the built-in execution engine.
Enables you to change the user name for connecting to a database. Mandatory if the database requires a user name to connect.
|
Enables you to change the password for connecting to a database. Mandatory if the database requires a password to connect.
|
JDBC-specific settings
The JDBC-specific settings are relevant when the mapping contains a JDBC connection and is executed by generated Java code or by MapForce Server.
| Note: | ADO, ADO.NET, and ODBC connections are converted to JDBC (and the JDBC settings below apply) when the mapping is run on a Linux or macOS machine. For details, see Database Mappings in Various Execution Environments. |
Displays the currently active driver for the database component. The default driver is automatically entered when you define the database component. You can change the driver entered here to suit your needs. Make sure that the syntax of the entry in the Database URL field conforms to the specific driver you choose.
|
URL of the currently selected database. Make sure that this entry conforms to the JDBC driver syntax of the specific driver entered in the JDBC Driver field.
|
ADO/OLEDB-specific settings
The ADO/OLEDB-specific settings are relevant when the mapping contains an ADO connection and is executed by the generated C# code, C++ code, or by MapForce Server running on Windows. For details, see Database Mappings in Various Execution Environments. The Data Source and Catalog settings are not used by the built-in execution engine.
Displays the name of the ADO data source.
|
Displays the name of the ADO catalog.
|
Displays the currently active provider of the database component.
|
Displays any additional database options.
|
Generation settings
Generation settings apply to all code generation targets as well as the built-in execution engine. The Strip schema names from table names option allows you to strip database schema names from the generated code and retain only the table names. Note that this option works only for SQL SELECT statements generated by MapForce. User-defined SQL statements will not be modified.
The Generation settings option is supported for backward compatibility and should be avoided. To make database object names relative to the default schema, use the approach described in Use Object Names Relative to Default Schema.
Timeout for statement execution
When a database is used as a target component, execution timeouts can occur due to server availability, traffic, long-running triggers, and other factors.
Defines a period of time, in seconds, during which the execution engine must wait for a database response before aborting the execution of a database statement. The default timeout is 60 seconds.
|
When enabled, this option instructs the execution engine to never time out.
|
| Note: | Timeout for statement execution is not applicable to SQLite databases. |
Database transaction handling
During the execution of a mapping that has a database component, there may be various database-related errors (e.g., duplicate index keys, NULL values inserted into non-NULL columns, etc.). To be able to roll back your database data in case of an error, you need to enable database transaction handling. You can enable transaction rollback at database-component level (current setting), at table-action level, and at stored-procedure level. For more information about some of the possible transaction-handling scenarios, see Transaction Rollback: Scenarios.
Some mappings may contain multiple database components that may have the same or different database connections. The outcome of such mappings in case of a database-related error depends on the execution engine:
•If the mapping is run with MapForce, only one target component can be executed when the mapping runs. This is the component where the 
button is enabled. If a database error occurs in that component, and if the Use transactions check box is enabled, all the changes done by the component will be rolled back.
•If the mapping is run with MapForce Server or a MapForce-generated program, all the target components are executed, sequentially. In this case, when a database error occurs, the rollback will take place for the database component where the error occurred. The mapping will stop or continue executing the next target component depending on the value you selected from the When an error occurs drop-down list (see below).
Enables transaction processing for a target database component. Transaction processing is enabled for all tables of the database component when you select this option. Enabling transaction handling at database-component level encloses all database changes in a single transaction that will be rolled back in case of a database-related error.
|
If you have selected the Use transaction check box, you can choose what to do when a database-related error occurs:
•Rollback top transaction and stop: The transaction which encloses all the database changes is rolled back, and the execution of the mapping stops. •Rollback top transaction and continue: Same as above, but the mapping continues to run after the rollback (e.g., to process a second target component).
At database-component level, you control whether processing should continue for other target components. For example, in an XML-DB-JSON mapping, an error has occurred in the database component. In this case, the JSON file can still be processed and retrieved if you have enabled the Rollback top transaction and continue option.
|
Traces
When a mapping writes data to a database, you can enable database tracing and error logging. Tracing is useful if you want to track all the changes made to the database during the execution of the mapping. The changes made to the database are logged in a trace report. If there are errors during the execution, these errors will also be logged. Tracing is compatible only with the Built-In transformation language.
You can enable tracing at different levels:
•Database-component level: Tracing at this level could be useful for mappings that have multiple target database components, and you need to enable tracing only for some of them. Enabling tracing at database-component level automatically enables it for all tables and stored procedures in that component. To be able to be traced, the relevant tables and stored procedures must be connected to source nodes.
•Table or stored-procedure level: You can decide whether to enable tracing for a specific table or stored procedure. At table level, tracing includes events related to table actions (e.g., Insert All). In the case of stored procedures, events related to the stored procedure call are traced.
•Database-field level: By default, all fields are traced, but you can exclude certain fields from the trace report or choose to include certain fields only in case of an error.
Importantly, the three levels above are hierarchical. This means that in order to set tracing at a lower level, you must enable tracing at parent level first. For example, if you need to set tracing at table level, you must first enable tracing at database-component level. The same principle applies when you narrow down the tracing level. For example, if you limit tracing only to errors at database-component level, it is not possible to use full tracing at table and stored-procedure level.
MapForce allows you to set the following tracing options:
When tracing is enabled, the actions performed by the mapping against the database are logged in a trace file. You can choose to log all actions, only errors, or disable tracing completely.
|
Specifies the file to which database trace information will be written when the mapping runs. This path can be absolute or relative and is influenced by the Save all file paths relative to MFD file check box. The trace file is in XML format. If you prefer the log file to be in a format other than XML, you can map data from it to some other component (e.g., a text file, another database, etc.).
Structure of a trace fileWhen you enable tracing for a database component, a tracing structure becomes available in the lower half of the component (screenshot below).  The screenshot above shows that the top node in the tracing structure is the name of the trace file (Log.xml). The rest of the tracing structure is modeled based on the structure of the database tables or stored procedures that take part in the mapping. In this example, the top element has the same name as the database. The BookCatalog01 element has two child elements: Authors and trace:summary. The Authors element reflects the structure of the table that is added to the database component. The trace:summary element includes an errors attribute which reports the number of encountered errors.
The Authors element contains two child elements: trace:values and trace:actions. The trace:values structure displays all the columns of the database table. By default, all columns are traced, but you can change this by configuring tracing at database-filed level (see above). For stored procedures, this structure displays the parameters of the stored procedure.
The trace:actions element includes information about all the actions that are defined for this particular database table. In our example, two actions have been configured for the Authors table: Ignore If and Insert Rest. Each traced action has a rows-affected attribute that specifies how many rows have been affected by the respective database action. The trace:error element is populated only if an error occurs. This element has two attributes: code and state. The text of the error and the attribute values are supplied by the database driver and will therefore be different for different databases.
Trace file in the Output paneTo preview the trace file, click the Output pane. Note that the trace report displayed in the Output pane is for information purposes only and does not reflect the actual execution results. To produce an actual trace report, run the SQL script from the Output pane. An example of a trace file is given below:
<BookCatalog01> <Authors> <trace:values> <Author>Neil Gaiman</Author> <Website>www.neilgaiman.com</Website> </trace:values> <trace:actions> <trace:ignore/> </trace:actions> </Authors> <Authors>...</Authors> <Authors>...</Authors> <trace:summary errors="0"/> </BookCatalog01>
For more information about tracing, see Scenario 1 in Transaction Rollback: Scenarios.
|
Save all file paths relative to MFD file
When this option is enabled, MapForce saves the file paths displayed in the Component Settings dialog box relative to the location of the MapForce design file (.mfd). Use relative paths if you intend to run the mapping with MapForce Server on a different operating system. See also Relative and Absolute Paths.
Use shared database connection at runtime
This option enables you to choose whether several database components that use the same data source and feature in the same mapping should share the same database connection. By default, this option is disabled; otherwise, it might change the behavior of the mapping, especially when the same connection is shared between one or more source components and a target component.
Sharing the same database connection enables you to solve various issues, for example, with table/row locks, transaction isolation, and the number of server connections (see details below).
•When you read a row from a table and try to update the same row, it might happen (depending on the vendor) that you run into a table/row lock error. With a shared database connection, this issue can be avoided.
•With connection-sharing enabled, you will be able to read already changed rows that are wrapped in a transaction. With separate connections, only committed changes are visible.
•Connection sharing also helps reduce the number of database logins, which will enable you to reduce the overall processing time for mappings with a lot of database components that use the same data source. The database logon procedure can be time consuming, especially with cloud server instances over a slow network or when the database server is busy.