Database Schema Properties (Magic xpi 4.9)
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, Delete or UPSERT operation. The Data Mapper lets you easily update and insert data from any format by mapping it to the database.
The UPSERT operation is only supported for databases of type Oracle and MS-SQL. For rest of the databases the UPSERT operation is not available. (Since version: 4.9)
|
|
-
Database schema entries in the Data Mapper's Source and Destination columns appear in red if there is no connectivity to the database that you defined in the Resources section of the Settings dialog box. In such cases, hover over the entry to see an informational tooltip that further explains the problem. In situations where there is no connectivity, you can still edit mapping and expressions if a cached schema exists.
-
By default, every database schema is opened in cache mode. This means that the table definition is loaded from the local cache and there is no attempt to connect to the database. This behavior is controlled by the Online/Offline toggle button in the database resource switching from Online to Offline mode. If your table structure changes often, it is recommended to use the Online mode. If the table structure does not change often or you are working on a project with no connection to the database, it is recommended to use the Offline mode. It is always possible to refresh an offline schema either by using the schema's Refresh context menu entry or by making changes to the SQL statement. (Since version: 4.5)
-
You can refresh multiple database schemas by clicking the Refresh Database Schemas  button on the toolbar or by clicking the Edit menu and selecting Refresh Database Schemas. This functionality is available only when a Data Mapper document is in focus in the Studio and when there is at least one database schema that belongs to an Online resource. (Since version: 4.5)
-
The Checker does not parse SQL statements. SQL statements are only parsed during the database schema configuration stage, and a visual indication is given on the Data Mapper tree.
|
The Properties pane lets you configure the following parameters:
|
|
|
|
Name
|
Enter a name for the schema.
|
|
Description
|
Enter a description for the schema.
|
|
Wizard
|
You can create an SQL statement visually with the Database Wizard. Click  to open the Database Wizard.
|
|
This parameter is not available when the Dynamic SQL Statement parameter (below) is set to Yes.
|
|
|
SQL Statement
|
When using the Database option, you can choose to create an SQL statement yourself. To do this, click to manually create an SQL Statement.
|
|
UPSERT
|
If you are using the Database option as your Destination, you can configure the UPSERT functionality.
Select one of the following from the drop-down list:
By selecting the Yes option, the user will be able to use the wizard and generate an UPSERT statement.
For more information on creating the UPSERT statements, click here.
(Since version: 4.9)
|
|
Dynamic SQL Statement
|
If you are using the Database option as your Destination, you need to indicate whether you want to write your own INSERT/UPDATE/DELETE statements, which will be executed "as is".
Magic xpi will not parse the statements. Select one of the following from the drop-down list:
For more information on creating the dynamic Destination statements, click here.
|
|
Database Definition
|
Select the database that you want to work with. All the databases that you entered in the Settings dialog box's Resources section are available for selection.
|
|
DB Disconnect
|
If you select Yes from the drop-down list, 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 select this check 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.
|
|
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.
|
|
|
Error Handling Flow
|
If you are using the Database option as your Destination, the Error 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 Error Handling Flow.
If an error occurs when the Error Handling Flow is selected, the flow containing the appropriate error information is executed.
|
|
|
-
Changes made in the database section of the Settings dialog box's Resources section 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.
|
