Overview

Using tables and procedures in your host application model enables you to create a database abstraction of the host data. Client applications can then query this data using a Verastream connector to interact with the model.

A table is a structure that contains columns that are used as input and output parameters for procedures. Typically, table columns are named to match their corresponding model attributes and fields.

A procedure defines how Host Integrator locates, retrieves, updates, inserts, and/or deletes data when it fulfills a request submitted by a client application using a Host Integrator API.

Tables and procedures are interlocked functionally. Tables are a way of "organizing" host data into a database-like view of the data, and procedures manipulate that data. The only way to access the abstracted table data is through a procedure.

Client applications interact with tables and procedures using either a subset of the industry standard Structure Query Language (SQL), or by interacting directly with the procedures. You can access the data in your host applications via SQL queries even if your host applications are not designed to respond to SQL queries.

• SQL Overview
• Tables Overview
• Procedures Overview

SQL Overview

With the Table/Procedure feature, you can abstract your host application so that client applications can perform queries using a subset of the industry standard Structure Query Language (SQL). 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.

In a query statement, client applications can select, update, insert, and delete data in the host application by specifying a context, a set of input parameters, and a set of desired output parameters in the form of an SQL statement. Using this statement Host Integrator determines the proper query to run and returns the desired results.

Use the table option to set up a table definition consisting of a set of columns. Use procedures to tell Host Integrator how to navigate to the host application screens where the data resides and pass any commands to the host application necessary to select, update, insert, or delete the host data. Once abstracted, the host application model responds to SQL queries from client applications in exactly the same way that a true SQL database does.

How Host Integrator Fulfills SQL Queries

When the Host Integrator receives an SQL query from a client application, it determines which procedure or set of procedures it must use to satisfy the query, and then executes those procedures.

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.

For SELECT statements, Host Integrator will use the necessary procedures to return set of data that exactly matches the WHERE clause in the query. Any data that does not exactly match the WHERE clause is thrown out during a process known as post-fetch filtering. This filtering is not used for LIKE expressions, thus all data found by the procedure concerning a LIKE expression is returned. This implementation of LIKE diverges from the SQL-92 standard.

SQL Syntax

Host Integrator supports a subset of the SQL 92 standard for SELECT, UPDATE, INSERT, and DELETE statements. This section describes the syntax convention that Host Integrator supports.

Case Sensitivity

The SQL-92 language standard requires that the names of objects be compared without regard to letter case. Comparisons between column values, however, are case sensitive by default.

If you do not see the results you expect because of case sensitivity, you can add COLLATE CASE_INSENSITIVE to explicitly specify a case-insensitive comparison of text column values. In this example, the state value ('ri') will be compared without case:

`SELECT Name, ContractDate, AcctNumber FROM Accounts WHERE MiddleInitial = 'c' AND
State = 'ri' COLLATE CASE_INSENSITIVE AND LastName = 'smith'

1. Pass in an SQL string.
2. Remove the portions not supported by Verastream.
3. Pass the remaining SQL to the model for processing.
4. Modify the results based on the custom extensions from the original client string.

Table and Column Names

Table and column names in Host Integrator are identified using the following convention:

TableName=Identifier

ColumnName=Identifier

Identifier=RegularIdentifier | DelimitedIdentifier

A regular identifier is a string of not more than 128 characters; the first character must be a letter (upper or lower case), while the rest can be any combination of upper or lower case letters, digits, and the underscore character. No SQL reserved words can be used.

A delimited identifier is any string of not more than 128 characters enclosed in double quotes. The double quote character is represented by two immediately adjacent double quotes.

Table and column names are not case sensitive.

Literals

Literal={CharacterString | Number }

Character strings are written as a sequence of characters enclosed in single quotes. A single quote character is represented by two immediately adjacent single quotes. Any comparisons between literals and columns must be between the same type: strings can only be compared to strings and numbers can only be compared to numbers.

Expressions

Expression = { Expression + Expression | Expression - Expression | Expression * Expression | Expression / Expression | - Expression | ( Expression ) | Literal | ColumnName }

Conditions

Condition = { Condition OR Condition | Condition AND Condition | NOT Condition | (Condition) | Comparison }

Comparison = Expression { = | <> | < | <= | > | >= | LIKE } Expression

SimpleCondition = { SimpleCondition OR SimpleCondition | SimpleCondition AND SimpleCondition | (SimpleCondition) | SimpleComparison)

SimpleComparison = ColumnName { = | LIKE } Literal

A distinction is made between Conditions and SimpleConditions because only SimpleConditions can be used as inputs to a procedure. SimpleConditions can be used in any WHERE clause, but Conditions may be used only in a SELECT statement's WHERE clause because the results can be filtered. Both Conditions and SimpleConditions can refer only to columns from one table. Joins and subqueries (SELECT statements inside another SQL statement) are not supported.

SELECT Statement Syntax

Use the following syntax for SELECT statements (Arguments between ([ ]) are optional and those between ({ }) are required:

SELECT [DISTINCT] {column-list} FROM {table} [WHERE {condition}] [ORDER BY {column-list}]

Arguments

• [DISTINCT] -- Specifies that all rows returned be unique. If there are two identical rows, one is removed from the output.

• {column-list} -- Any valid table column name(s).

• {table} -- The name of the table.

• {condition} -- Any condition or simple condition.

SELECT statements return only those rows exactly matching the WHERE clause. The results are sorted in the order specified in the ORDER BY clause. For procedure resolution, the table name is taken from the FROM clause, the filter parameters are taken from the WHERE clause, and the outputs are taken from the SELECT and WHERE clauses.

Examples

SELECT * FROM Patients WHERE AdmitNum = 20000

SELECT AdmitNum, SSN FROM Patients WHERE LastName LIKE 'W'

SELECT DISTINCT AdmitNum, SSN FROM Patients WHERE LastName LIKE 'W'

SELECT * FROM Patients WHERE LastName LIKE 'W' ORDER BY AdmitNum

SELECT * FROM Patients WHERE LastName LIKE 'W' AND FirstName = 'JOHN' ORDER BY AdmitNum

UPDATE Statement Syntax

Use the following syntax for UPDATE statements:

UPDATE {table} SET {{column} = {value} [, ...]} [WHERE {simple-condition}]

Arguments

• {table} -- The name of the table.

• {column} -- Any valid table column.

• {value} -- Valid values are characters and numbers.

• {Simple-condition} -- Simple conditions can contain characters and numbers and comparisons (=, <>, <, <=, >, and =).

UPDATE statements update all records matching the WHERE clause with the values in the SET clause. For procedure resolution, the table name is taken from the UPDATE clause, the filter parameters are taken from the WHERE clause, and the data parameters are taken from the SET clause.

Arguments between ([ ]) are optional and those between ({ }) are required.

Example

UPDATE Patients SET FirstName = 'Colin', LastName = 'Moulding', AdmitYear = 1999 WHERE AdmitNum = 56564

INSERT Statement Syntax

Use the following syntax for INSERT statements:

INSERT INTO {table} [({column-list})] VALUES {value-list}

Arguments

• {table} -- The name of the table.

• {Column-list} -- Any valid table column name(s). If no column list is defined, the order of the columns is taken from the table definition.

• {values-list} -- Valid values are characters and numbers.

INSERT statements add a record to the specified table. For procedure resolution, the table name is taken from the INSERT INTO clause. Data parameters are taken from the VALUES clause.

Arguments between ([ ]) are optional and those between ({ }) are required.

Examples

INSERT INTO Patients (AdmitNum, FirstName, LastName, Room) VALUES (31415, 'Doe', 'John', '123')

INSERT INTO Patients (AdmitNum, FirstName, LastName, Room) VALUES (31415, 'Doe', 'John', '123'), (31416, 'Doe', 'Jane', '131')

DELETE Statement Syntax

Use the following syntax for DELETE statements:

DELETE FROM {table} [WHERE {simple-condition}]

Arguments

• {table} -- The name of the table.

• {Simple-condition} -- Simple conditions can contain characters, numbers, and comparisons (=, <>, <, <=, >, and >=).

The delete statement deletes a record from the specified table. For procedure resolution, the table name is taken from the DELETE FROM clause and the filters are taken from the WHERE clause.

Arguments between ([ ]) are optional and those between ({ }) are required.

Example

DELETE FROM Patients WHERE AdmitNum = 31415

SQL Syntax Restrictions

Host Integrator supports the SQL-92 arguments and features, with the following exceptions:

• SELECT statements containing GROUP BY or HAVING clauses.
• DEFAULT and NULL may not be used for column values in UPDATE or INSERT statements.
• INSERT statements that specify inserting DEFAULT VALUES.
• Nested queries are not supported.
• Parameter references in expressions are not supported.
• The AVG, BIT_LENGTH, CASE, CAST, CHAR_LENGTH, CHARACTER_LENGTH, COALESCE, CONVERT, COUNT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, EXTRACT, LOWER, MAX, MIN, NULLIF, OCTET_LENGTH, POSITION, SESSION_USER, SUBSTRING, SUM, SYSTEM_USER, TRANSLATE, TRIM, and UPPER functions are not supported in expressions.
• JOIN operations are not supported.
• The SET clause can only accept literal values.
• For UPDATE and DELETE statements, all comparisons in the WHERE clause must be between columns and literals.
• For SELECT statements, you can use any valid expression in the WHERE clause. However, only comparisons between columns and literals will be used in a procedure. The other expression will be used to filter the output of the procedure(s). Any data that does not exactly match the WHERE clause is thrown out during a process known as post-fetch filtering.
• Host Integrator supports a usage of the LIKE operator that turns off post-fetch filtering. In this case, LIKE is equivalent to =, but post-fetch filtering is not performed on the column. if you expect to get everything the host sends, use LIKE. If you want to filter the results (including filtering for case sensitivity), use =. For example:
  SELECT Name, ContractDate, AcctNumber FROM Accounts WHERE MiddleInitial = 'c' AND State LIKE RI AND LastName = 'smith'

SQL predicate (logical test) restrictions

• Only TRUE predicates are allowed. The forms IS NOT * TRUE and IS FALSE are not supported.
• The BETWEEN and NOT BETWEEN predicates are not supported.
• The IN and NOT IN predicates are not supported.
• The LIKE operator is used to provide an input to a procedure without post-fetch filtering. The NOT LIKE and LIKE … ESCAPE predicates are not supported.
• Predicates involving quantifiers (ALL, SOME, ANY, EXISTS, UNIQUE) are not supported.
• Predicates involving MATCH are not supported.

Tables Overview

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

More information

• Creating tables

Procedures Overview

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.

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.

More information

• Creating procedures
• Parameter mapping
• Creating tables