Database action: Insert
|
| Previous Top Next |
Database action: Insert |
The first example in this section, deals with the simple task of adding a new office orgchart to the Altova table. The only fields available in the Altova table are: PrimaryKey and Name.
The second example inserts related office tables to the new orgchart record.
| 1. | Insert the Altova_Hierarchical.xsd schema (and assign altova-cmpy.xml as the input XML instance). |
| 2. | Insert the MS Access database altova.mdb into the mapping. |
| 3. | Create the following mappings: |
Altova to Altova, and
Name to Name
Please note: If all Altova, Office etc. items are automatically mapped, the option "Auto-connect children" is active. Select undo, and then the menu option Connection | Auto-connect matching children, to disable this option.
| 4. | Right click the Altova entry and select the menu item "Database Table Actions". |
There is currently only one table action column defined in this dialog box, Insert All. (Update if... and Delete if... table actions are selected by clicking the column header combo box, whereas additional table actions, can be defined by clicking the Append, or Insert Action buttons.)
The table action, Insert All, inserts all mapped fields of the current table into the database. We now have to define the status of the new PrimaryKey field for this action.
| 5. | Click OK to confirm the current settings. |
| 6. | Right click the PrimaryKey item, then select the Database Key Settings entry. |
| 7. | Select the Maximum number entry, and click OK to confirm. |
You will notice that the input icon for the PrimaryKey field is now unavailable.
| 8. | Click the Output tab at the bottom of the mapping window to see the pseudo-SQL code that this mapping produces. |
| 9. | Click the Run SQL-Script icon  in the function bar to run the script and insert the table data into the database. If the script was successful, a confirmation message appears. Click OK to confirm. |
| 10. | Open the Altova database in Access to see the effect. |
A new Microtech OrgChart record has been added to the Altova table with the new PrimaryKey 2. The data for this record originated in the input XML instance.
| 11. | Switch back to MapForce. |
You will now see a record of what happened when the SQL script was processed.
Please note:
You can only run SQL scripts once from the Output window, you have to switch back to the Mapping window, and to the Output window again, to re-run the script.
As stated in the comment section shown above, the SQL script is pseudo-SQL and cannot be executed in an SQL tool. You cannot infer anything about date formats, specific database syntax etc. from the pseudo code displayed here! It is meant to be an aid into what has, or will be executed.
Inserting tables and related child tables:
This example uses the previous example as a basis, and extends it by inserting related Office child tables to the Altova parent table.
Table relationships are only generated automatically, when mappings are created between child tables of a "root" table. In this case, mappings are created between the Office fields that appear directly under the Altova parent (or "root") table.
| 1. | Right click the Office entry and select the menu item "Database Table Actions". |
The Insert All... table action is selected by default, you do not have to make any changes here, click OK to confirm.
| 2. | Right click the Office | PrimaryKey field and select the Database Key Settings entry. |
| 3. | Select the Maximum number entry and click OK to confirm. |
| 4. | Create the following mappings between the two components: |
Office to Office
Desc to Desc, and
Email to Email
Established to Established, and
Name to Name.
| 5. | Click the Output tab to see the pseudo-SQL code. |
| 6. | Click the Run SQL script icon to run the script and insert the new tables. |
| 7. | Double click the Altova table to see the effect in MS-Access. |
Two new offices have been added to the Microtech OrgChart.
| 8. | Double click the Office table to see the effect in greater detail. |
The new offices have been added with primary keys of 3 and 4 respectively. Both these new offices are related to the Altova table by their foreign key 2, which references the Microtech OrgChart record.
|