Create Database Maps

Database Maps

Create a Database Map

Set up the database connection

Create a Join statement

Create a Where statement

Create an Order By statement

Create a Database Map with multiple Select statements

Database Maps

A Database Map (see Maps) is used to map data to or from a database. Database Maps are available for the following database types: SQL Server, MySQL, Oracle.

The input to, or output of a Database Map is always a Database Business Document. You can create the following types of Database Maps:

  • Select
  • Insert
  • Update
  • Delete
  • Stored Procedure

Note: You can only use SQL expressions that are transaction-enabled. Do not use any SQL statements that commit the transaction, for example: CREATE TABLE, DROP TABLE, and so on.

You use a Database Map to:

  • Update, insert, or delete data in a database
  • Select data from the database
  • Execute a Stored Procedure

Update, insert, or delete data in a database

You can write database queries to update, insert, or delete data in a database. These types of Database Maps do not require output Business Documents. The input Business Document is used to provide the lookup value to find what to update, where and what to insert, or what to delete.

Select data from a database

You can write SQL queries that select data from a database. The input Business Document is used to provide the lookup value for what to select.

Execute a Stored Procedure

You can define a SQL Map that executes Stored Procedures on a database. The input Business Document is used to provide the parameters to your Stored Procedure. You cannot create a new Stored Procedure in Mapping Services; you can only execute Stored Procedures that already exist.

Database Map with multiple Select statements

Database Maps can contain several Select statements. For this particular case, the output Business Document that is to be generated or used contains a number of root leaves equal to the number of Select statements. This is the only case when a Database Business Document can contain more than one root element.

Create a Database Map

To create a Database Map in an existing DML Mapping Project:

  1. From the main menu, select File > New > Map.
  2. Alternatively, you can right-click in the Project Explorer and select New > Map.
  3. A wizard appears to help you create a new Map.
  4. Specify where you want to save the Map. To select a path either use the directory tree or enter the path directly.
  5. Enter a File name for your Map. Click Next.
  6. For Map Type, select Database.
  7. Click Next.
  8. Optionally, select a database connection or create a new one using the Connection drop-down list. For details see Set up the database connection
  9. From the SQL Statement drop-down list, select the type of Database Map. By default, it is set to SELECT.
  10. Select the input and output Database Business Documents.
  11. For Source, click Browse to select the path to the input Business Document.
  12. For Target, click Browse to select the path to the output Business Document.
  13. If you check Generate, default names for the Source and Target Business Documents to be generated are displayed. You can edit these names.
  14. If you want to edit the Join, Where, or Order By statements, click Next. Otherwise click Finish.
  15. Optionally, edit the Join statement of the SQL expression of the Database Map (see Create a Join statement) and click Next.
  16. Optionally, edit the Where statement of the SQL expression of the Database Map (see Create a Where statement) and click Next.
  17. Optionally, edit the Order By statement of the SQL expression of the Database Map (see Create an Order By statement) and click Finish.

Mapping Services generates the Database Map in the selected project’s Map folder and creates an entry in the Project Explorer pane tree structure. If the automatic generation for input and/or output Business Documents has been selected, Mapping Services also generates these Business Documents in the Business Document folder.

The Map opens on a new tab in the Database Map editor. For details refer to Use the Database Map editor.

Set up the database connection

Optionally, in the New Map wizard, you can select a database connection using the Connection drop-down list. Or, create a new connection by selecting New Connection.

Selecting a connection will fill in the Tables pane with the database schemas containing tables, views, and stored procedures. Check the desired tables, views, and stored procedures to be stored in the Map cache, which allows you to work on the Map in disconnected mode.

Create a Join statement

Optionally, when creating a Database Map, you can create the Join statement of the Database Map SQL expression if the type of the Database Map is SELECT.

You can add, delete, or move joins. Each join expression allows you to select the join type, the right table to build the join, and the join condition.

The wizard allows you to select from the following Join Types:

  • inner join
  • left outer join
  • right outer join
  • full outer join

Note: You can edit the name of the right table or select it from a drop-down list containing the tables you selected previously from the database. If no tables were selected, no list is displayed.

Create a Where statement

Optionally, when creating a Database Map, you can also create the Where statement of the Database Map SQL expression if the type of the Database Map is SELECT, UPDATE, or DELETE.

You can add, delete, move, edit, and group where conditions.

Click... To
AND group icon Insert an AND group. It groups all nested conditions and they will have the AND conditional operator between them.
OR group icon Insert an OR group. It groups all nested conditions and they will have the OR conditional operator between them.
NOT AND group icon Insert a NOT AND group. It groups all nested conditions and they will have the AND conditional operator between them and the entire group is negated.
NOT OR group icon Insert a NOT OR group. It groups all nested conditions and they will have the OR conditional operator between them and the entire group is negated.
Condition icon Insert a condition.

Create an Order By statement

Optionally, when creating a Database Map, you can also modify the Order By statement of the SQL expression.

All of the columns of the tables or views you checked previously for the current Select are on this page. If those tables or views contain primary keys, they will be automatically checked. You can change the selection, meaning the rows obtained from querying the database will be ordered by the columns you check here.

Create a Database Map with multiple Select statements

To create this particular case of Database Map, follow the same steps as for a normal Database Map. You can add multiple statements and for each one you can define a Join, Where, and Order By expression.

Note The connection you set when creating the Database Map with multiple Select statements is the same for every statement.

Related topics

Use the Database Map editor

Maps

Map editor

Create Maps

Related Links