Database Schema Properties (Magic xpi 3.x)
When you select Database as your Source or Destination type, you are either selecting data to convert or you are manipulating data in a database table.
When you use a database as Source data, you are determining what data to use to map to a Destination. To do this you create an SQL statement that defines the data you want to use. The Data Mapper supports the selection of multiple tables as Source data. In this case, you must create a JOIN command to combine the tables.
When you use a database as the Destination data, you are editing the data in a database table. For this operation, you indicate whether you are executing an Insert, Update, or Delete operation. The Data Mapper lets you easily update and insert data from any format by mapping it to the database.
When you select Properties, the Database Schema Properties dialog box opens. This dialog box lets you configure the following parameters:
|
|
|
|
Name
|
Enter a name for the schema.
|
|
Description
|
Enter a description for the schema.
|
|
DB Operation
|
If you are using the Database option as your Destination, you need to indicate which SQL operation you want to execute. You do this in the DB Operation drop-down list. This list is available only for Destination. The options are:
-
Delete: This allows you to delete selected or mapped data from the database tables that you indicate in the SQL statement.
-
Dynamic: This allows you to write your own INSERT/UPDATE/DELETE statements, which will be executed "as is". Magic xpi will not parse the statements. For more information on creating dynamic Destination statements, click here. (Since version: V3.2)
-
Insert: This allows you to insert selected or mapped data from the database tables that you indicate in the SQL statement.
-
Update: This allows you to update selected or mapped data from the database tables that you indicate in the SQL statement.
|
|
Database Definition
|
Select which database you want to work with. Available databases must be entered in the database section of the Resource Repository. From the Database Definition drop-down list, select the database that you want to work with. All available databases that are entered in the Resource Repository are displayed in the list.
|
|
DB Disconnect
|
If you check this box, all open connections to the specified database are disconnected at the end of the step. If you are working with parallel Data Mapper steps, and you check this box for a specific database definition, the disconnection might not occur until the final step completes its operation and releases the connection. No indication of failure will be sent to the log or to the Monitor.
|
Note:
|
The DB Disconnect field closes the connection to the database. If you have a flow with a Data Mapper step that selects records from the database and then calls a flow, you should not use DB Disconnect in the linear path of the called flow as this closes the connection to the parent Data Mapper while records are still being processed.
|
|
|
Exception Handling Flow
|
The Exception Handling Flow field lets you handle exceptions using the error flow mechanism. Click  to open the Flow List. Here, you should select the flow that you want to execute when an error occurs. You should design and select an error flow that helps you to identify exactly where the error occurred. You should also make sure that you define flow variables that will accept the input data in the Exception Handling Flow.
If an error occurs when the Exception Handling Flow is selected, the flow containing the appropriate error information is executed.
|
This dialog box contains the following additional buttons:
|
|
|
|
Resource
|
If the database you want to work with is available in your network, but you did not define it in the Resource Repository, you can add it on-the-fly. Click Resource to open the Resource Repository, and add additional databases to the Repository.
|
|
Wizard
|
You can create an SQL statement visually with the Database Wizard. Click Wizard to open the Database Wizard.
|
|
SQL
|
If you are using the Database option as your Source, you can choose to create an SQL statement yourself. To do this, click SQL to manually create an SQL Statement.
|
|
Note:
|
-
Changes made in the database section of the Resource Repository are seen immediately when you return to the list box view.
-
Magic xpi internal databases are not shown in the selection list.
-
BLOB fields are not supported in a database Destination.
|
|
