Creating Procedures

Procedures tell Host Integrator how to fulfill the queries it receives from client applications. The procedures you create for your table determine what host data can be read, inserted, updated, or deleted. Each procedure has a unique signature that describes what it does. The signature includes a procedure type (SELECT, UPDATE, INSERT, and DELETE) and a set of parameters. Host Integrator uses these signatures to translate SQL statements into a set of procedures.

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.

You can use any VHI procedure type (SELECT, UPDATE, DELETE, or INSERT) to modify host data, but only a SELECT procedure type can return data.

Procedures use one or two of the three types of parameters:

• Filter parameters— Specify which records will be acted upon
• Data parameters— Specify new values for the records
• Output parameters— Specify what values to return

The key component of a procedure's definition is the parameter mapping. Each parameter in a procedure corresponds to a column in the table and is mapped to an attribute, a recordset field, or another parameter. Each procedure has a predefined traversal path through the host application; during the traversal operations, data is exchanged between parameters and attributes and recordset fields. The following chart shows which parameters are used in which procedures:

Procedures should be as complete as possible: if you do not provide a procedure for a particular operation, it is not be possible for a client application to access or modify that table data. Procedures should also contain robust error handling to recover from unexpected or incomplete queries. Using the Procedure Editor, you can include error entities that define errors returned from a procedure.

Use the Procedure Wizard to quickly create a basic procedure. For more complicated procedures, create the procedure using the Tables dialog box and the Procedure Editor.

After adding procedures to your model, you can use Web Builder to quickly and easily generate a web application or a component interface, such as a web service or JavaBeans, based on the procedures of a host application model.

You can create procedures using the Procedure Editor or by using the table-first approach.

More information

• Creating Procedures Using the Table-first Approach

Creating a Procedure Without SQL

The procedure feature in Host Integrator is often associated with SQL, but you can also explicitly execute a procedure directly through the Host Integrator connector APIs using the PerformTableProcedure method. This enables you to bypass procedure resolution and post-fetch filtering. Depending on the structure of your host application and whether you benefit from the additional capabilities provided by SQL, executing procedures directly might improve the performance of your deployed models.

The process for creating procedures is the same whether you execute the procedure using SQL or the PerformTableProcedure method.

See Using Host Integrator Connector APIs for information on using the PerformTableProcedure method.

More information

• SQL Overview
• Creating Tables

Insert Procedure Example

1. Load the example model CICSAccts.modelx into the Design Tool. The Main entity appears in the model window.
2. Select Tables from the Model menu.
3. Expand the Accounts table by clicking the plus sign (+) next to it. The procedures for the Accounts table appear below.
4. Click the InsertAccount procedure.

Evaluate the structure of this procedure in the Procedure Editor and in the Tables dialog box.

Select Procedure Example

1. Load the example model CICSAccts.modelx into the Design Tool. The Main entity appears in the model window.
2. Select Tables from the Model menu.
3. Expand the Accounts table by clicking the plus sign (+) next to it. The procedures for the Accounts table appear below.
4. Click the GetAccount procedure. Evaluate the structure of this procedure in the Procedure Editor and in the Tables dialog box.

Creating Compound Procedures

A compound procedure is a procedure that consists of two or more subprocedures. Compound procedures must contain at least one SELECT subprocedure combined with a SELECT, UPDATE or DELETE subprocedure. By combining one or more subprocedure into a compound procedure, you can perform more than one query level task at the same time, like selecting several records and updating or deleting them. Compound procedures cannot include an INSERT subprocedure.

When you create a compound procedure, the order the subprocedures are listed is the order they will be invoked. The first subprocedure in a compound must be a SELECT procedure. Output parameters from a prior procedure supply the filter parameters for the following procedure.

Before you can create a compound procedure, you must already have created the subprocedures that make up this compound procedure.

To create a compound procedure:

1. Click the table in the Tables and procedures box for which you want to create the new compound procedure.
2. Click New in the Tables dialog box and select Compound procedure from the list in the Create a new table or procedure dialog box.
3. A new compound procedure appears beneath the current table; enter a name for the new compound procedure in the Name box.
4. Enter a description of the compound procedure in the Description box.
5. Select the compound procedure's type: Compound procedures can have a type of either SELECT, UPDATE, or DELETE. A compound procedure cannot include an INSERT procedure. If a compound procedure is built from subprocedures of differing types, the compound procedure's type is considered to be the last subprocedure's type.

6. Click the Insert button next to the Select procedures box and select the first SELECT subprocedure to add to this procedure.

   Note

   The first subprocedure in a compound must be a SELECT procedure. The SELECT procedures available in the Select procedures box are all the SELECT procedures in the table. Next to each SELECT procedure is a green dot, a yellow dot, or a red X. If the SELECT procedure is marked with a green dot, it can be used as a valid subprocedure. If it is marked with a yellow dot, the subprocedure doesn't provide any additional parameters that aren't already present. If it is marked with a red X, the inputs for this subprocedure are not available as outputs from a previous SELECT subprocedure.

7. Repeat step 6 for each SELECT subprocedure you want to add to the compound procedure.

8. To change the order that the SELECT subprocedures are invoked, highlight a subprocedure and click the up or down arrow to change its place in the execution order.
9. Optionally, select the UPDATE or DELETE subprocedure to add to the compound procedure by clicking the down arrow next to the Update/Delete procedure list.

The UPDATE or DELETE procedures in the list are those available in the current table. A compound procedure can contain only one UPDATE or DELETE subprocedure, and it is always the last subprocedure in the compound procedure.

Mark Procedures as Hidden

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.

Compound Procedures that Use PerformTableProcedure

You can also create procedures that use the Host Integrator connectors' PerformTableProcedure rather than the SQL API. In this case, the Available for SQL queries check box at the bottom of the Tables dialogue box should be cleared. Review the information on Executing Procedures Using Connector APIs.

Compound Procedure Example

1. Load the example model CICSAccts.modelx into the Design Tool. The Main entity appears in the model window.
2. Select Tables from the Model menu.
3. Expand the Accounts table by clicking the plus sign (+) next to it. The procedures for the Accounts table appear below.
4. Click the CompoundNameSearch procedure.

Evaluate the structure of this procedure in the Procedure Editor and in the Tables dialog box.

Executing Procedures Using Connector APIs

The procedure feature in Host Integrator is often associated with SQL, but you can also explicitly execute a procedure directly through the Host Integrator connector APIs using the PerformTableProcedure method. This enables you to bypass procedure resolution and post-fetch filtering. Depending on the structure of your host application and whether you benefit from the additional capabilities provided by SQL, executing procedures directly might improve the performance of your deployed models.

The process for creating procedures is the same whether you execute the procedure using SQL or the PerformTableProcedure method.

See Using Host Integrator Connector APIs for information on using the PerformTableProcedure method.

More information

• SQL syntax
• Creating tables

Deleting a Procedure

1. Load the example model CICSAccts.modelx into the Design Tool. The Main entity appears in the model window.
2. Select Tables from the Model menu.
3. Expand the Accounts table by clicking the plus sign (+) next to it. The procedures for the Accounts table appear below.
4. Click the DeleteAccount procedure.

Evaluate the structure of this procedure in the Procedure Editor and in the Tables dialog box.

Debug Procedure

Use the Debug Procedure dialog box to test and step through the procedure logic that Host Integrator uses to fulfill SQL requests. This allows you to debug your procedure definitions before deploying your model.

You can display the Debug Procedure dialog box in two ways, by either clicking the Debug button in the Test Procedure dialog box, or by clicking the Debug button in the SQL Test dialog box.

To perform procedure debugging, you must either be connected to the host and have access to the application the model is based on, or you must load model in the Host Emulator and connect to it.

To debug a procedure, follow these steps:

1. Click Debug in either the Test Procedure or Test SQL dialog box.

   The Debug Procedure dialog box appears. In the Stack context box, Host Integrator displays the context of the currently executing procedure or SQL query. If you are testing an SQL statement in the Test SQL dialog box, the statement you are testing displays and the filters from the WHERE clause of the SQL statement display in the Filters box. If you are testing a procedure in the Test Procedure dialog box, the procedure you are testing displays in the format TableName.ProcedureName.

2. Click Run to test the procedure. Host Integrator will test the procedure and display the results of the query in the Outputs box (if any).

Stepping through a Procedure's Logic

To step through a procedure's logic one step at a time, follow these steps:

1. Click Debug in either the Test Procedure or Test SQL dialog box.

   The Debug Procedure dialog box appears. In the Stack context box, Host Integrator displays the context of the currently executing procedure or SQL query. If you are testing an SQL statement in the Test SQL dialog box, the statement you are testing displays. Any data or filter parameters that are compared to recordset fields or output parameters (in the case of an SQL query requesting a subset of what the procedure provides) appear in the Filters box. If you are testing a procedure in the Test Procedure dialog box, the procedure you are testing displays in the format TableName.ProcedureName.

2. Click Step Into. In the Commands box, the complete logic for the current procedure displays. The yellow arrow points to the line being evaluated.

   Note

   The syntax of the command language is generated by Host Integrator. You cannot modify it.

3. To step forward through the procedure logic one line at a time, click Step.

   Each time you click Step, Host Integrator proceeds to the next line; As it finds the data that fulfills the procedure, the data displays in the Output box. If the Terminal window is visible, you will see the Design Tool navigate to the appropriate host screen while it fulfills the SQL query. 4. Click Step Out to end the step through and return to the previous level.

Setting a Break Point

The Debug Procedure feature includes the ability to set a break point in a procedure which pauses the operation at a chosen point. This aids in debugging procedure logic.

1. In the Commands box, click the line of code where you want to set the break point.
2. Click Set Break. A red circle appears to the left of the line, indicating the break point that you set. The next time you run a procedure test, the Design Tool will stop the operation at this line.
3. To remove the break point, click the line containing the break point and click Remove Break.