Creating Procedures Using the Procedure Editor

Procedures define how Host Integrator locates, retrieves, updates, inserts, and/or deletes data in attributes and/or recordset fields. Procedures make it possible for Host Integrator to fulfill requests through a connector API. The client application can access these procedures using either an ExecuteSQLStatement method or a PerformTableProcedure method.

If you have already defined a table and its columns, you may want to create procedures using a table-first approach. However, if you do not have a table, it is faster to have the Procedure Editor automatically generate the table columns as you create the procedure. There are three steps to create a procedure and automatically populate the table with columns:

1. Set up the procedure information in an "empty" table.
2. Define the traversal path through the host application.
3. Define the procedure parameters and table columns.

Note: You can also use the Procedure Wizard to 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.

Step 1: Set up the procedure information in an "empty" table

1. In the Tables dialog box, click New to display the Create a new table or procedure dialog box.
2. Select Table from the list and click OK.
3. Enter a name and description for the table, but do not enter any columns.
4. Click New in the Tables dialog box and select Procedure from the list in the Create a new table or procedure dialog box. Then click OK.
5. A new procedure appears beneath the current table. Rename that procedure by entering a new name in the Name box.
6. Select a Home Entity from the list.
7. To add a description of the procedure, click Advanced Properties and enter the description in the Description text box.
8. Select the Type of procedure you are generating: Select, Update, Insert, Delete.
9. Clear the Available for SQL queries check box if you want to make this procedure only available using the PerformTableProcedure method.
10. If this procedure is part of a sequence of procedures, you may want to clear the Navigate back to starting point upon completion check box. Although this may provide better performance, it can create navigation errors.

Step 2: Define the traversal path through the host application

Each procedure has a predefined traversal path through the host application. During traversal operations, data is exchanged between parameters and attributes or recordset fields. As you add entities, the traversal path may be marked with a red X because at this point, required parameters have not been defined. You will correct this when you identify and map parameters.

1. In the Tables dialog box, click Procedure Editor to open the Procedure Editor.
2. To insert an entity:
   1. On the diagram, click the icon or path prior to the insertion point.
   2. Then, click the Insert Entity button on the left button bar to open the Insert Entity dialog box.
   3. Select the entity to insert and click OK.
3. To Insert a recordset:
   1. On the diagram, click the entity containing the recordset.
   2. Then click the Insert Recordset button on the left button bar to open the Insert Recordset dialog box.
   3. Select the recordset to insert and click OK.

Step 3: Define the procedure parameters and table columns

Each parameter in a procedure represents a table column, which is mapped to an attribute or field. In the Procedure Editor, use the Data Exchange tab to select the attributes and fields to be used as parameters in the procedure; these parameters are automatically added to the table as columns.

To define entity attributes or recordset fields:

1. Click the entity or recordset in the diagram that contains the attributes or fields to be used. The Data Exchange tab now lists the attributes of that entity or the fields of the recordset.
2. Select the attribute or field to add to your procedure; you can use the [Ctrl] or [Shift] key to make multiple selections. Then, click one of these buttons to identify how the attribute or field is to be used in the procedure:
   • Use as Filter to use this attribute or field to identify the records you want to select, update, or delete.
   • Use as Output to select the data that is to be returned from the select procedure.
   • Use as Data to identify the attributes or fields to update or insert.
3. Click Close to exit the Procedure Editor.

Table Columns Created Automatically

As you select the attributes and fields to use in your procedure, those that are not part of the table are listed in the New Table Columns list box. When you click the button to use the attribute or field in your procedure, that attribute or field is automatically added to the table as a new column. Prior to adding the new column, you can also designate a column type (Text, Integer, or Float) in the New Column Type list box.

To work directly with the table columns in the Procedure Editor, click the Edit Table Columns button to open the Edit Table Columns dialog box. You can add columns, designate a key or column type, and set filter, data, or output columns as not used, required, or optional. You can also rename columns and set minimum and maximum column properties.