How Do I Create Data Mapper SQL statements and Make the Mapping Connections? (Magic xpi 3.x)

This section describes how you create the SQL statements when you use the Data Mapper, and how you make the mapping connections. For information about how to define the Source and Destination databases, see How Do I Set Up Database Operations In the Data Mapper?.

Preparing the SQL Statements

You can define the Source and target database schemas in one of these ways:

• Direct SQL statement: You manually enter the SQL statement

• Wizard: You select the required database, table, column, etc. through a “wizard” interface

The results are the same whichever method you use.

You can define different sections of the SQL statement as dynamic. You can determine the values of those dynamic definitions as follows:

• Runtime: Global or Flow Variable tags only have a value during runtime. The syntax of these variables in an SQL statement is: G.name1

• Mapping: You can define dynamic types from any Source element or from constant values. The syntax of these variables in the SQL statement is: MyDel

Below are examples of the different SQL statements for the Source and Destination schemas.

Insert Statement

INSERT INTO customer (city,fax_area,fax_num,house_num,name,number_id,phone_area, phone_descr,phone_num,street,zip_code)

Update Statement

SELECT customer.city,customer.street FROM customer WHERE customer.name = G.name1

UPDATE customer SET city=city, street=street WHERE customer.name = G.name2

Delete Statement

SELECT customer.city FROM customer WHERE customer.city = G.city

DELETE FROM customer WHERE customer.city = MyDel

Select Statement

Preparing the Mapping Connections

After you define the Source and Destination schemas, you can then move to the mapping phase. This is when you connect the Source elements (the information retrieved from the Source schemas) to the Destination elements.

You can define different types of connections between the Source and Destination schemas. Below are the types of connections that are relevant for database-type schemas:

• Simple Connection: Connecting between an element of a simple compound to another element in a Destination simple compound

• Complex Connection: Connecting between one compound to another compound

• Dynamic Tag (Mapping) Connection: Connecting a dynamic tag definition to a constant, another element, or a Flow or Global Variable

The diagrams below are examples of the different mapping connections that you can find in the sample project.

Insert Mapping

For the Insert operation, the simple elements are mapped from the Source XML schema to the Destination database schema.

Note: The Phone_desc element in the Destination schema is connected to itself. This means that the value entered at runtime is retrieved from a calculated expression.

Update Mapping

For the Update operation, the City and Street elements in the Destination schema are dynamic tags. These tags are part of the Update SQL statement and receive their real values during the mapping process and not as part of the SQL statement definition.

Delete Mapping

For the Delete operation, the MyDel element in the Destination schema is a dynamic tag. This tag is part of the Delete SQL statement WHERE clause and receives its real value in the mapping process and not in the SQL statement definition.