Query protocol, component table

The query protocol is a way of interacting with an RDMI component that involves passing a single row of data to the component and letting it return multiple columns and rows.

The query protocol lets you present data from external sources to the Rules Engine as if it were multi-row data in a relational table.

The typical way to achieve this is to build a component table. Your USoft application can use that component table (in constraint SQL, batch SQL, SQL in a GUI layer...) as if it were a normal database table. How the component table handles its data is programmed in the underlying component that uses the query protocol. The component table and the underlying component are linked on a 1:1 basis because they have the same name.

Examples of data that you may want to make available through the query protocol include:

•Information about files in a folder on the file system.

•Data from a file storage, for example, an XML, JSON, CSV file, or an MS Excel spreadsheet.

•Data from a different database.

If you want the end user to perform an external action (such as sending an e-mail) by pressing a push button or clicking a link, then use the standard protocol and not the query protocol.

How the query protocol works

First you need to make sure your RDMI component has one or more associated query protocols. Each protocol designates one method as its EXE method, and another method as its FETCH method.

Next, instead of calling a method of the component directly, you call a query protocol name. An INVOKE ... WITH SELECT ... statement is processed as follows if the INVOKE calls a query protocol name:

The query protocol works by calling one method of the component as the EXE method, and another method as a FETCH method. The operation breaks down into the following steps:

1.	Execute the protocol's EXE method. This is the equivalent of executing a SELECT statement in an RDBMS.

2.	Execute the protocol's FETCH method and catch the Out parameter(s). Return the record to the caller. This is the equivalent of fetching the first record from an RDBMS query result.

3.	Repeat Step 2 until the component returns no further records, that is, until the FETCH method reports that there are no more records.

This operation is executed each time a runtime user, or a program, calls a query protocol name you have defined, as opposed to calling a method directly. This principle is shown in this example.

Methods

To prepare a component for the query protocol, make sure you offer the following 2 methods in a way similar to the example.

QueryExe()	This method contains input parameters for all columns of the table. The parameters indicate a column restriction. If the value for a column is NULL, there is no restriction. If there is a value, the records with these column values are retrieved.
QueryFetch()	This method contains output parameters for all columns of the table. The method returns zero or one record when called. The records returned meet the same condition as stated for the QueryExe method.

If you want the component table to deal also with INSERT, UPDATE or DELETE, or all three, then you must also implement the following, as required:

InsertRow()	Contains input parameters that correspond with the columns of the table. The method is called on the insert of a record, and inserts the data in the external data source.
UpdateRow()	Contains input parameters that correspond with the columns of the table. The method is called on the update of a record. The method updates the data in the external data source. The primary key of the table is not updatable and therefore it is sufficient to pass only the new values to the method.
DeleteRow()	Contains input parameters that correspond with the primary key columns of the table. The method is called on the delete of a record. The method deletes the data in the external data source.

When coding methods for the query protocol (as for the aggregate protocol), you must return the following standard integer values to express that a record is or is not found. See the example.

-1	Error.
0	No record returned.
1	Record returned.

Query protocol association

The following assumes that your component has a QueryExe and a QueryFetch method.

To associate, for your component, a query protocol name to an EXE and a FETCH method:

1.	In the info window where you coded the component, click the Query Protocol Associations tab page.

2.	Enter the name 'QUERY' for the protocol. This is the name that the component table will be looking for when queried.

3.	Enter the name 'QUERYEXE' in the Execute Method field.

4.	Enter the name 'QUERYFETCH' in the Fetch Method field.

5.	Save/commit.

Stateful component

When you call a query protocol name, the USoft implementation is stateful by default, but if you want to combine the component with a component table then you are required to declare the component as being Stateful. Do this in the State tab in the info window where you coded the component.

As is clear from the example, on this tab, you can declare further details about state, such as the component's lifetime, whether the component must be able to participate in a transaction and (if so) whether it must support Commit, Rollback, and Pre-Commit constraint handling, whether it supports a component table, and whether the component table implements INSERT, UPDATE, DELETE operations.

Component tables, definition

The technology of choice with a component supporting a query protocol is to build a component table "on top of it" so that you can query an possibly manipulate the data handled by the component as if it were an RDBMS table. The component in this construct is a user-defined table component. Do not confuse with internal table components.

Create a table component by choosing Define, Tables, Component Tables from the Definer menu. Make sure you give the component table the same name as the user-defined table component that supports it. See the example for details.

Component tables, features and limitations

Component tables can be accessed as if they were normal database tables. In all USoft contexts where you can specify a SQL statement, you can refer to a component table. Component tables are allowed in all subqueries (i.e. NOT EXISTS, NOT IN etc).

In addition to querying a component table (SELECT), if the underlying component supports it, you can use INSERT, UPDATE, DELETE statements.

Component tables have the following limitations:

•There is no outer-join support.

•CONNECT BY and START WITH are not allowed.

•Component tables cannot be subtypes or supertype tables.

•Primary key columns of Component Tables are non-updatable. This limitation makes the definition of the UpdateRow method easier: it gets parameters for every column. Without the restriction, the old values for the primary key would also have to be passed to the method.

•Binary data is not supported.

•The STDDEV and VARIANCE functions are not supported for queries on component tables.

Component tables, error handling

It is possible to let the InsertRow, UpdateRow, and DeleteRow method fail by raising an exception.

As part of this exception, you can provide a message. This message will then be displayed within a USoft environment. NO rollback will be performed. You can translate these messages by using Application Strings.

This is the same transaction behavior as for regular tables in a USoft environment: If the record cannot be stored, no rollback is performed. If the PreCommit method fails, depending on the situation, a rollback is performed or not.