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.

Procedure execution does not require SQL--see Executing Procedures Using Connector APIs.

 

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.

 

Related Topics
Bullet SQL syntax
Bullet Creating tables
Bullet Procedures overview