Altova MapForce 2023 Enterprise Edition

To change a database component on the mapping so that it uses a new database connection, open the database component settings, click Change, and follow the wizard steps to connect to the new database. If you need to change only the database objects that are displayed on the mapping, right-click the title bar of the database component, and select Add/Edit/Remove Database Objects from the context menu, see also Database Basics.

Be aware that, by default, changing the database does not remap all existing mapping connections to or from the database component. With most database kinds, however, it is possible to preserve the mapping connections after switching a database, if you take some preparation steps as described in this topic.

When selecting database objects as described in Adding, Editing, and Removing Database Objects, you can optionally choose whether database object names should be considered as relative to a default schema. In the context of databases, a "schema" is a logical grouping of database objects, typically, for security purposes such as assigning permissions. Schemas have slightly different behavior depending on the database kind.

In MapForce, treating database objects names as relative to a schema is important if you plan to switch the mapping to a different database later (for example, to a production database). This is also useful if the database schema has been renamed on the server at some point, and so you need to update the mapping accordingly. If the new schema has the same structure as the one used at mapping design time, you can switch to it without having to change manually all mapping connections. This is possible only if you configure the object names to be relative to a default schema as described below.

Note the following:

•Using object names relative to a default schema is possible for those database types that support schemas: IBM DB2, IBM Informix, IBM Db2 for i (iSeries), Oracle, PostgreSQL, Progress OpenEdge, SQL Server and Sybase.

•This feature is supported starting with MapForce version 2020.

•It is not possible to use relative names if the database component includes local relationships or SELECT Statements as Virtual Tables.

To treat database objects names as relative to the default schema:

1.Right-click the title bar of the database component on the mapping and select Add/Remove/Edit Database Objects from the context menu.

2.Select one or more objects that belong to the default schema, or to the default catalog (database) and schema, as applicable. The default database and schema are shown in bold.  In the example below, the default catalog is Sandbox and the default schema is user. This structure is specific for SQL Server databases and may vary in other database kinds. For more information about the displayed structure, see Adding, Editing, and Removing Database Objects.

3.Select the Use object names relative to default schema check box. Note that this check box is grayed out if the database does not support relative object names.

If the objects that you require on the mapping are in a different schema (not the default one), you have the following alternatives:

•Connect as another database user that has access to the required default schema.

•Reconfigure the database server so as to change the default schema of the existing database user, provided that you have the required privileges.

Taking SQL Server as example, a database administrator could change the default schema of a database user with a statement such as the one below (which assumes the current catalog is "Sandbox" and both the user and the schema already exist).

Database objects that are relative to the default schema are shown on the mapping with a relative name. Consider differences between the following two database components:

In the left component, the Use object names relative to default schema check box was selected, therefore, the table is shown with a relative name. In the right component, the check box was not selected, so the table name includes the schema name.

When database objects are referenced in a relative way, it becomes possible to switch the mapping to a new database or schema without losing the mapping connections, as follows:

•Open the database component settings and click Change. Follow the wizard steps to connect to the new database as a new user. If you have qualified the objects with a relative name, and if the new database has the same structure, then all the connections on the mapping will be updated automatically. Specifically, they will now match the default catalog and schema of the new database user.

•If you need to perform the switch on a recurrent basis, it is recommended to define the database connection as a Global Resource. For example, the Global Resource could have two configurations: a default one for the development database, and a production configuration. Assuming that both databases have the same structure, you can switch between databases by changing the required configuration from a drop-down list:

For more information about this scenario, see Example: Switch Databases.

Code generation

The Use object names relative to default schema check box affects the generated C#, C++, or Java program code. When this check box is selected, all the database references become relative in the generated code also. The following is an example of a generated SQL statement (in C#) when the check box is not selected. Note that the table name includes the schema name.

The following is an example of a generated SQL statement (in C#) with the check box selected: