Stored Procedures and Local Relations

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > Databases and MapForce > Stored Procedures >

Stored Procedures and Local Relations

Local relations are logical relationships between database fields that you can create in MapForce, saving you the need to change the underlying database, see also Defining Local Relationships. You can define local relations not only for database fields, but also for stored procedures as well, both in source and target components.

 

In source components, local relations make it easy to read data from related objects, for example, read IDs from a database table and call a stored procedure with each of these IDs to retrieve related information. It is also possible to call a stored procedure with data retrieved from another procedure.

 

In target components, local relations enable you to define a hierarchical order in which multiple related procedures are to be called. For example, you can first call a stored procedure that creates an ID value, and another one that inserts related information into a table. It is also possible to mix stored procedures and tables in local relations. For example, you can perform the insert directly on a related table instead of calling another procedure, see Using Stored Procedures to Generate Keys.

 

 

To create a local relation:

1.Right-click the title bar of a database component and select Add/Remove/Edit Database Objects from the context menu. The "Add/Remove/Edit Database Objects" dialog box opens.
2.Click Add/Edit Relations.
3.Click Add Relation and select the objects between which you want to create the relationship.

mf_sp_19

As illustrated above, a local relation consists of a primary/unique key object and a foreign key object. Think of it as a parent-child relationship. On the mapping component, the object (table, view, procedure, and so on) where the primary/unique key is will appear as a parent while the object where the foreign key is will appear nested under it. For example, in the database component illustrated below, a local relation was defined between a recordset column (RS1.Department) and a table column (Department.Name). Consequently, the Department table appears as a child of the stored procedure on the mapping. This example is discussed in more detail in Local Relations in Source Components.

mf_sp_23

The following table lists all the possible fields between which you can define local relations. Mixed relationships are possible (for example, mapping the output of a stored procedure to a database column). The fields taking part in the relationship must have the same, or a compatible, data type.

 

Primary/unique key

Foreign key

Column of a database table or view
Output parameter or return value of a stored procedure, see also Stored Procedures
Column of a recordset returned by a stored procedure*
Column of a user-defined SELECT statement, see also SQL SELECT Statements as Virtual Tables.
Column of a database table or view
Input parameter of a stored procedure
Input parameter of a user-defined SELECT statement

 

* Applicable if the stored procedure is called either as data source (without parameters) or as a function (with input and output parameters). In order for the recordset to become available for selection, you must execute the stored procedure once, to retrieve the recordset.


© 2019 Altova GmbH