Creating Tables

Using Host Integrator tables enables you to create a database abstraction of your host application so that client applications can query it using a subset of the industry standard Structure Query Language (SQL). Table columns are usually associated with attributes and fields in the Host Integrator model. This makes it possible for you to access the data in your host applications via SQL queries even if your host application is not designed to respond to SQL queries. Host Integrator tables themselves don't contain data; rather they provide a database-like view of the underlying host data.

In a query statement, the client application specifies a table, a set of input parameters, and a set of desired output parameters in the form of an SQL statement. From this statement Host Integrator determines the appropriate procedure or procedures to run in order to return the desired results.

Host Integrator accomplishes this through the use of tables and procedures. Using the Tables dialog box, you can create one or more tables for a host application. Each table can have one or more associated procedures. The procedures are comprised of entity navigation paths and parameter mappings that tell Host Integrator how to read, write, and modify host data represented by the table.

Use the Table Wizard or the Tables dialog box to create a database abstraction of the Host Integrator model.

When you export the documentation for your host application model, a list of all table and procedure names is generated. Use this list to access the tables and procedures from one of the Host Integrator APIs.

Creating a table using the Table dialog box

1. In the Design Tool, click Tables on the Model menu to open the Tables dialog box.
2. Then, click New in the Tables dialog box and select Table in the Create a new table or procedure dialog box that appears.
3. Type a name for the new table in the Name box. This is the name Host Integrator will use to identify the table in the Table Editor and that client applications use to query the host application model.

4. Type a description of the table in the Description box. To add columns to the table:

   • Click the Add Column (+) button to the right of the Columns box to add one or more new columns.

   • In the Name field, replace the auto-generated column name (Column1, Column2, etc) with a meaningful name, typically the name of the attribute or recordset field that this column will be mapped to in a procedure.

   • Select the column's data type by clicking the down arrow in the Data Type entry area and selecting one of the options: Float, Integer, or Text.

   • Click the Key box to identify this column as the key.

   • If required, specify the column's minimum and maximum values in the Column properties minimum/maximum boxes.

   • Enter a description of the column in the Description box.

5. If you want the Host Integrator to return a partial set of data to a querying application using SQL, select the Allow SQL SELECT statements to return a subset of columns when all columns are requested box. If you clear this check box, Host Integrator returns an error to a querying application if it cannot return all columns when a wildcard * is specified in SQL.

Defining Table Columns

1. Click the Insert Column button to the right of the Columns box.

2. Type the name of the column in the Name column. Use a name that reflects the name of the attribute or recordset field to keep the table column and the attribute or field closely linked.

3. Select the data type for the column by clicking the down arrow at the far right side of the Data Type box to display the data type list. The options are Integer, String, or Float.

4. If required, specify the column's minimum and maximum properties in the Column properties minimum/maximum boxes.

5. To identify a column as the key, select the Key box.

6. Enter a description of the column in the Description box.