How Do I Create an SQL Statement Without Using the Data Mapper’s Database Wizard?

The Magic xpi Data Mapper utility provides a wizard to quickly build a simple SQL statement to use when mapping data. To increase development flexibility, you can manually create an SQL statement without using the wizard.

To manually create an SQL statement, drag the Data Mapper utility to a flow. In the Source Destination dialog box, select Database as your Source or Destination, then click Properties to open the Database Schema Properties dialog box. Run the wizard to select the table and columns you need. The next time you enter the Database Schema Properties, click SQL at the bottom of the dialog box to open the SQL Statement screen. Use basic database script rules to enter an SQL statement in the SQL Statement field. If the current object is a Source data object, only a Select statement is allowed. If the current object is a Destination data object, then only Update, Insert and Delete statements are allowed.

When a Flow Variable is entered, it will be surrounded by elbow brackets and question marks as follows: <?flow variable name?>. You can also write other legal SQL statement text, such as Example or Order By.

You can use multiple tables in the statement, with a JOIN between them—or if multiple tables were selected through the wizard, a JOIN will be automatically created according to the selections made in the wizard.

Data Mapper Utility SQL Statement Considerations

• Use ANSI symbols in the statement for support of all Database types.

• The Data Mapper utility does not support unions. They will not be present in the visual mapper.

• The Data Mapper does not support field names with spaces.

• You must enclose all Alpha strings with single quotes (').

• To connect to other databases, you must indicate the database owner. You do not need to indicate the owner if the database you want to connect to is the Magic xpi internal database that you are using for your project.

• You cannot use wildcards for fields or functions. You must specify each one or create an alias for them.

Example

This example explains how to update the following table by creating a manual SQL statement (without using the Data Mapper utility’s wizard).

To add a numeric variable called My_Key:

1. Open an existing flow or create a new flow and drag a Data Mapper utility to the flow.

2. Double-click or right-click on the Data Mapper and click Configuration to open the Data Mapper window.

3. From the Toolbox's Mapper Schemas section, drag a Database type into the Destination area of the Data Mapper window.

4. In the Database Source's Properties pane, select Update as the Database Operation and Database Definition.

5. Click Wizard and run the wizard.

6. In the Database Destination's Properties pane, click SQL to open the SQL Statement screen.

7. Write the following statement:

UPDATE ExampleTable SET ExampleTable.Col1=’T’, ExampleTable.Col2=20, ExampleTable.Col3=’X’, WHERE ExampleTable.Table_Key=<!?My_Key?!>

The record that is updated is determined by the value of the My_Key numeric variable. The table columns are updated with the following values:

• Col1: T

• Col2: 20

• Col3: X