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?.
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:
Below are examples of the different SQL statements for the Source and Destination schemas.
INSERT INTO customer (city,fax_area,fax_num,house_num,name,number_id,phone_area, phone_descr,phone_num,street,zip_code)
|
SELECT customer.city,customer.street FROM customer WHERE customer.name = G.name1
|
UPDATE customer SET city=city, street=street WHERE customer.name = G.name2
|
SELECT customer.city FROM customer WHERE customer.city = G.city
|
DELETE FROM customer WHERE customer.city = MyDel
|
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.
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.
|
|
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.
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.