Creating Procedures Using the Table-First Approach

Procedures define how Host Integrator locates, retrieves, updates, inserts, and/or deletes the data contained in table columns when it fulfills a request through SQL or through an API.

Using the table-first approach means that you have already created a table and populated it with columns. There are three steps to creating a procedure using the table-first approach:

1. Set up the procedure information in the Tables Dialog Box.
2. Defining the traversal path through the host application.
3. Map the procedure parameters to attributes or fields.

Note

You can also use the Procedure Wizard set up the table information and map the parameters; the wizard creates the traversal paths based on the parameter mapping. To add branching and error entities, you need to use the Procedure Editor.

Set up the procedure information in the Tables dialog box

1. Click the table that contains the data you need in the Tables and procedures box.
2. Click New in the Tables dialog box.
3. Select Procedure from the list, and then click OK
4. A new procedure appears beneath the current table, rename the procedure by entering a new name in the Name box.
5. Select a Home Entity from the list.
6. To add a description for the procedure, click Advanced Properties and enter a description in the Description text box.
7. Select the Type of procedure you are generating: Select, Upgrade, Input, or Delete.

8. Select the parameters by checking the appropriate table columns. Depending on the type of procedure being created, you will define one or more of these parameters:

   • Filter parameters**— Identify the records you want to select, update, or delete
   • Output parameters**— Contain data that is to be returned from the select procedure
   • Data parameters**— Fields or attributes to update or insert

9. In the Req column, select all table columns that are required in a query. Host Integrator returns an error to the client application if it doesn't include all required columns in its query statement.

10. Select Hide in web services and Web Builder if you do not want the procedure to be visible in either Web Builder or in the WSDL available from the Web Services Explorer. This option marks a procedure as hidden. Selecting this option does not prevent someone from invoking the procedure, it merely treats it as internal or private.
11. To make this procedure only available using the PerformTableProcedure method, clear the Available for SQL queries check box.
12. If this procedure is part of a compound procedure, you may want to clear the Navigate back to starting point upon completion check box. This allows one procedure to begin where another procedure ends. Although this may provide better performance, it can create navigation errors.

Defining the traversal path through the host application

Each procedure has a predefined traversal path through the host application. During the traversal operations, data is exchanged between parameters and entity attributes or recordset fields.

1. In the Tables dialog box, click Procedure Editor to open the Procedure Editor.

2. To insert an entity:

   a. Use the diagram to click the icon or path prior to the insertion point.

   b. Then, click the Insert Entity button on the left button bar to open the Insert Entity dialog box.

3. To insert a recordset:

   a. Use the diagram to click the entity containing the recordset.

   b. Then click the Insert Recordset button on the left button bar to open the Insert Recordset dialog box.

Map the procedure parameters in the procedure editor

Each parameter in a procedure represents a column name in the table, which is then mapped to an attribute or field from one of the entities or recordsets in your procedure. In the Tables dialog box, click Procedure Editor to open the Procedure Editor. Use the Data Exchange tab to map table column names to attributes and fields used in the procedure.

To map table columns to entity attributes

1. Click the entity in the diagram that contains the attributes to be mapped.

2. The Data Exchange tab lists the entity's attributes. Map these to procedure parameters by clicking the down arrow in the appropriate Data Exchange column:

   • Update attribute from parameter - Select the procedure parameter whose value will be written to this attribute when the procedure is executed.
   • Write attribute to parameter - Select the procedure parameter to receive this attribute's value when the procedure is executed.

To map table columns to recordset fields

1. Click the recordset in the diagram that contains the fields to be mapped.

2. The Data Exchange tab lists the recordset's fields. Map these to procedure parameters by clicking the down arrow in the appropriate Data Exchange column:

   • Update field from parameter - Select the procedure parameter whose value will be written to this field when the procedure is executed.
   • Write field to parameter - Select the procedure parameter to receive this field's value when the procedure is executed.

Note

As a mapping shortcut, select the attribute or field on the Data Exchange tab and click the Use as Filter, Use as Data, or Use as Output button. This has the additional benefit of automatically creating a column in the procedure's table and designating it as a parameter of this procedure, if it does not yet exist.