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.
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.
The following table lists all the possible fields between which you can define local relations. Mixed relationships are possible (e.g., mapping the output of a stored procedure to a database column). The fields taking part in the relationship must have the same or compatible data types.
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. Applicable if the stored procedure is called as a 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. •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 |