Data access

Current values
Historical data
ESL RPC procedure call
Limitations
Description of columns containing status information

Current values

Basic current values are sorted out by a value type in the following five tables.

1. AnalogPoints - real values and relative time values.

2. IntegerPoints - integer values.

3. EnumPoints - enumerated types including Boolean type.

4. TimePoints - absolute time values.

5. TextPoints

Variables of the types Value array and Structured variable are in separated tables. The table name is derived from the name of the variable so that the dot character "." is substituted by the underscore character "_" in the name.

Each object of the Value array type creates a table of the following format:

Each object of the Structured variable type creates a table of the format corresponding to a form of the structure variable. In addition, ROW_ID column, determining an order number - structured variable row, is added to this table.

Historical data

The variables of Historical value type create separated tables. The table name is derived from the name of the historical variable so that the dot character "." is substituted by the underscore character "_" in the name.

Each variable of the Historical value type creates a table in the following format:

There is implemented in the table ArchivValues which enables getting the values of several archive objects at the same time. By a selection from this table, it is possible to enter several names of archive objects within one command.

For example:

SELECT TIME,VALUE01,STATUS01,VALUE02,STATUS02 FROM ArchivValues WHERE TIME >{ts '2000-04-11 10:00:00'} and TIME <{ts '2000-04-11 12:00:00'} and NAME01="H.AAA1" and NAME02="H.AAA2" and STEP=60

The structure of the table ArchivValues:

Where n is at most 12.

ESL RPC procedure call

D2000 ODBC Driver also enables ESL RPC procedure call which corresponds to CALL action. There are created the special tables, which names are derived from the D2000 EventHandler name, on which the procedure is to be done, the object of event type and the RPC procedure name. They are joined by the character "^" (e.g. SELF_EVH^E_event^proc). The dot characters "." in the name are replaced by an underscore character "_". The tables without the Event handler process name in their name (e.g. E_event^proc) are intended for a broadcast RPC procedure call.
The columns are named according to the procedure parameters. RECORD type parameters are represented by columns which are composed by the parameter name and structure column name (parameter^column). These tables also contain "ROW_ID" column. The dot character "." is substituted by the underscore character "_" in the parameter name. The RPC procedure tables contain the columns "$async" and "$instanceId".

The procedure call types:

• synchronous - the column "$async" may not be used in the SELECT part,
• asynchronous - the only column "$async" may be used in the SELECT part,
• broadcast - RPC table without Event handler name may be used.

When the procedure call is addressed directly to some instance, the WHERE part has to contain the column $instanceId".

The general syntax of RPC procedure call:

SELECT $async | param1, param2, param3, ...
            FROM [proces_EVH^]E_event^proc
            [WHERE param3 = x AND param4 = y AND ... [$instanceId = id]]

Examples of RPC procedure call via ESL and their ODBC equivalent:

WHERE part may contain only a value assigned to the parameters.
SELECT output will contain as many rows as RECORD parameter with the most rows or 1 row, if the procedure has only the unstructured types of parameters. If some exception occurs while the procedure is done, SQL query runtime will fail. The result of the asynchronous procedure call is unknown, therefore this query does not return any data.

Limitations

The limits for the command SELECT:

1. A SELECT command can contain just one table.
2. Sorting is enabled only according to the value of one column (ORDER BY).
3. Selecting a historical value must contain a time interval, e.g. 

SELECT TIME,VALUE,STATUS FROM H_NAME WHERE (TIME >"10-04-2000" AND TIME <"12-04-2000 10:00").

It can contain a definition of the required time step in seconds, e.g.

SELECT TIME,VALUE,STATUS FROM H_NAME WHERE (TIME >"10-04-2000" AND TIME <"12-04-2000 10:00") AND STEP=60.

The syntax for entering an absolute time is either "dd-mm-rrrr [hh[:mi[:ss[.mss]]]]" or {ts '2000-04-11 10:00:00'}.

The limits for RPC procedure call:

1. RPC procedures:
   • without any parameters,
   • containing ALIAS parameters,
   • containing IN parameters of RECORD type
   cannot be called.
2. The column $instanceId of RPC tables can be only in WHERE part of the SQL query.
3. The column $async of RPC tables can be only in SELECT part of the SQL query and cannot be combined with other columns.
4. The columns of RPC tables, representing the columns of RECORD parameters, cannot be used in WHERE part of the SQL query.
5. The columns of RPC tables, representing IN parameters, cannot be used in SELECT part of the SQL query.
6. Neither ORDER BY clause nor the aggregate functions (count, avg, max, ...) are supported while RPC procedure is called.

Description of columns with status information