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:
- Set up the procedure information in an "empty"
- Define the traversal path through the host application.
- 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
- In the Tables dialog box, click New to display the Create a new table or
procedure dialog box.
- Select Table from the list and click OK.
- Enter a name and description for the table, but do not enter any columns.
- 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.
- A new procedure appears beneath the current table. Rename that procedure
by entering a new name in the Name box.
- Select a Home Entity from the list.
- To add a description of the procedure, click Advanced Properties and enter
the description in the Description text box.
- Select the Type of procedure you are generating: Select, Update,
- Clear the Available for SQL queries check box if you want
to make this procedure only available using the PerformTableProcedure method.
- 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.
- In the Tables dialog box, click Procedure Editor to open the Procedure Editor.
- To insert an entity:
- On the diagram, click the icon or path prior to the insertion point.
- Then, click the Insert Entity button on the left button bar to open
the Insert Entity dialog box.
- Select the entity to insert and click OK.
- To Insert a recordset:
- On the diagram, click the entity containing the recordset.
- Then click the Insert Recordset button on the left button bar to open
the Insert Recordset dialog box.
- 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:
- 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.
- 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
- 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.