Editable Query Results

Results of execution of some queries are editable. This is a unique feature of Iotellect Query Language, that helps collect properties of different objects or settings of hardware devices in a single table (including only the necessary fields, sorted, grouped etc) and edit these properties using a convenient form.

A field in a query result is editable if several conditions are met:

  • The field must belong to some context variable, not a function output. This is obvious, since you can write a new value for a variable back to the context from which it came, but there is no way to "write back" a function output.

  • This field must not be defined as read-only in the format of the variable from which it was taken.

  • The value of this field must not be calculated (using simple or aggregation functions or some query language expressions).

  • The field must not come from a table that was built using nested SELECT statement.

  • All fields of a source table should be selected in the SELECT clause using * or tableAlias.* (this can be avoided by using write-back fields).

Editable query results are only available when editing value of Query Results (data) variable of a Query context.

Example 1:

SELECT * FROM users.*:childInfo

Most fields in the result of this query will be editable, because:

  • They came directly from the value of the childInfo variable,

  • They are writable in the format of this variable,

  • They are not converted using any function or expression in the query result,

  • They are selected using *, without using Field References

Some fields (i.e. "Username" field) will not be editable, because they are not defined as editable in the format of the childInfo variable.

Example 2:

SELECT childInfo$firstname, childInfo$lastname FROM users.*:childInfo

The result of this query will have no editable fields, because fields to be selected are listed explicitly in the SELECT clause and write-back fields are not defined in the query text.

Write-Back Fields

There is a special way to select a pre-defined number of fields by explicitly listing them in the SELECT clause and still make these fields editable. To do this, you have to add three so-called "write-back" fields to the list of Field References listed in the SELECT clause. These fields help the query processing engine figure out where the information contained in the editable query results should be stored when the modified result is saved.

The format of these write-back field references is as follows:

tableAlias.CONTEXT_ID, tableAlias.PARENT_ID, tableAlias.RECORD_INDEX

where tableAlias is name of alias given to the Context Reference in the FROM clause, CONTEXT_ID, PARENT_ID and RECORD_INDEX are predefined constants. Just put them in the query text as-is, without modifying anything.

These write-back fields must be always used all together.

Example:

Assuming we have a query that shows statistics of traffic generated by all Device Servers accessible by the user executing it:

SELECT 
 info.deviceServerInfo$owner, 
 info.deviceServerInfo$name, 
 info.deviceServerInfo$description, 
 info.deviceServerInfo$blocked, 
 info.status$servertods, 
 info.status$dstoserver,
FROM 
 users.*.deviceservers.*:status:deviceServerInfo as info

Result of this query is not editable, because selected fields are explicitly listed in the SELECT clause.

To make it editable, we should add write-back fields to SELECT clause:

SELECT 
 info.deviceServerInfo$owner, 
 info.deviceServerInfo$name, 
 info.deviceServerInfo$description, 
 info.deviceServerInfo$blocked, 
 info.status$servertods, 
 info.status$dstoserver,
 info.CONTEXT_ID,
 info.PARENT_ID,
 info.RECORD_INDEX
FROM 
 users.*.deviceservers.*:status:deviceServerInfo as info

This correction will make Device Server description and its "blocked" status editable in the query result. Other fields will remain read-only, since they are defined as read-only in the format of the deviceServerInfo ("Device Server Info") and status ("Device Server Status") variables.

If some columns are not editable due to the absence of write-back fields, this is indicated in the query debug report.

Was this page helpful?