Database Basics
This topics explains how to add a database to your mapping, select, remove, and edit database objects.
Add a database to your mapping
To be able to add a database to your mapping, you must select one of the following transformation languages: Built-In, C++, C#, or Java. SQLite databases are supported only in Built-In. If you intend to deploy the mapping to FlowForce Server, execute it with MapForce Server, or use features such as Bulk Transfer and stored procedures, you must select Built-In.
Once the desired transformation language is selected, you can add a database to the mapping in one of the following ways:
•Select Database in the Insert menu.
•Click the toolbar button.
When you take any of these actions, the database connection wizard appears, guiding you through the steps required to connect to the database. For more information about how to connect to a database, see Connecting to a Data Source. Once the database connection is successfully established, you are prompted to select the database objects that should appear in the mapping (see subsections below).
Databases can also be added to the mapping as variables. When you add a database structure as a variable, the same database connection wizard appears.
Add database objects
As soon as you have connected to the data source, you are prompted to select data objects you would like to include in your mapping. In our example, we are using the Altova.sqlite database, which is available in MapForceExamples\Tutorial.
The Insert Database Objects dialog shows the structure of the database. To include a database object in the mapping, select the check box next to it and click OK. In our example, we have included all the user tables.
Structure of Insert DB Objects dialog
The top node in the structure indicates the database connection. The subsequent structure varies depending on the database kind. For example, Oracle and IBM DB2 databases have a schema node under the connection node, while other database kinds have a catalog (database) node. The bold font indicates the default catalog (database) or schema, as applicable.
If your database user account has access to multiple databases or schemas on the server, you can switch to the required one by clicking the icon (see below).
Options available in Insert BD Objects dialog
The (Filter) button allows you to filter objects by name. Once you click the Filter button, the filter icon is available next to objects which support filtering (in this example, Tables). Click the filter icon to choose one of the following options: No Filter, Contains, Does Not Contain, Start With, Ends With, Equals. In our example, we have decided to include only tables whose names start with A (see below).
The (Show checked objects only) button displays those items where a check box is active.
The (Object Locator) button allows you to find specific database items. Select a particular object or type its name in the combo box which appears in the lower area of the dialog box.
The Add/Edit SELECT Statement button enables you to add and edit custom SELECT statements for the current database. The data returned by such statements becomes available as a mapping source. For more information, see SQL SELECT Statements as Virtual Tables.
The Add/Edit Relations button enables you to define local primary and foreign key relationships between fields in the database, in addition to those that may already be present. For more information, see Defining Local Relationships.
The Add/Edit Recordset Structures button applies to databases that support stored procedures. It is only enabled if a stored procedure is currently selected from the database tree.
The Show Preview button enables you to quickly preview the data of the currently selected table or view. Note that you can also browse and query a database independently of the mapping process, by using the Database Browser. For more information, see Browsing and Querying Databases.
The Use object names relative to default schema check box allows displaying the relative name of the table. For more information, see Switching Databases and Schemas.
Edit DB objects
To change database objects, right-click the database component and select Add/Remove/Edit Database Objects (see below).