How to data link
This section explains how to link data with CONPROSYS device registered in "Device Management" of Dashboard, execute query executed with "Query", and obtain result.
When "Device tree" is displayed from the "View" menu of HMI Editor, a list of devices and queries registered in the database is displayed.
The contents displayed in the device tree are as follows.
| category | group | Description |
|---|---|---|
| dt |
Below the dt category, the registered CONPROSYS device and share group are displayed. When you select these groups, the list of available items appears in the right panel. |
|
| share |
The share group can freely register items that can be used in common among pages in the same project. Link dt: share: <arbitrary item name> and output the value, the new item is registered. |
|
| DeviceID.CloudKey |
The registered CONPROSYS device is displayed in the form of device ID + "." + Cloud key. The device ID that was set at device registration is displayed. The cloud key registered on the CONPROSYS device is displayed. Clicking on each item in the list will display the bit value of the current value in 16-bit or 32-bit format at the bottom. To register a new device, use "Device management" in the dashboard menu. |
|
| DeviceID |
Here, the unique ID of each device and the information of the user privilege that can use the device data are displayed. The device ID set at the time of device registration is displayed. For a list of predefined variables, please refer to Device Alarm Reserved Variables . Only users who are enabled in the operation group or operator can use the data of this device. However, when retrieving data via "Query" or shared data, the privilege does not apply. To register a new device or change the permissions, select "Device" from the dashboard menu. |
|
| db |
Below the db category, the registered queries are displayed. When you select these, the settings on how to execute the query and the list of available items are displayed on the right panel. |
|
| Query ID | The query ID of the registered query is displayed. |
How to use queries
When you select a query in the device tree, the following screen will be displayed.
At the top you will see the type of query you selected and the settings for automatic execution.
□ Automatically execute queries when loading pages
When enabled, the query is executed once when reading the created page.
□ Run queries on a regular basis
When enabled, executes the query at the specified execution interval.
Note: If the page is not opened in the browser, the query is not executed.
About query items
Items displayed and usage differ depending on the type of query. Describe the items displayed by each query type and their contents.
■ Common items
| item name | access method | Description |
|---|---|---|
| Run | Read/Write |
Writing a value other than 0 executes the query. When execution of the query is completed, the value of Run will be 0. Example) db:query1:Run, If linked to the event output of the "switch" or "button" control, the query is executed when the control is clicked. |
| Status | Read only |
You can get the execution status of the query. "OK" indicates that the execution is completed normally, and "Running" indicates that the execution is in progress. In other cases, it will result in an error in executing the query. Example) db:query1:Status |
| Time | Read only |
You can get the time when the query was executed. The value is the elapsed time (ms) from 1970/1/1. Details property of the label controlusing, can be displayed on the screen in the required time format. Example) db:query1:Time |
■ Items for "get" queries
| item name | access method | Description | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Offset | Write Only |
When query is executed, data after the line specified by this item is acquired later. The default value is 0, which must be a number greater than or equal to 0. Example) db:query1:Offset |
|||||||||
| Limit | Write Only |
When executing the query, if the result has more than the number of lines specified in this item, limit the number of lines of data to be acquired. The default value is 2000. Example) db:query1:Limit |
|||||||||
| NumTotal | Read Only |
Item to get the number of rows of the query execution result. Example) db:query1:NumTotal |
|||||||||
| Row### | Read Only |
Item to retrieve the data for the specified line. ### can be a number greater or equal 0, less than the number of lines retrieved. If there are multiple fields on the specified line, the value is stored in the array. If you link to a property of a control that does not correspond to an array, the last value will be used. Example) db:query1:Row0 |
|||||||||
| Row###.FieldName | Read Only |
The item to retrieve the data for the specified field on the specified line. ### can be a number greater or equal 0, less than the number of lines retrieved. For the field name, you can specify the field added by "output data" of the query setting. Example) db:query1:Row0.field_1 |
|||||||||
| FieldName | Read Only |
Item to retrieve the data for the specified field. For the field name, you can specify the field added by "output data" of the query setting. If there is a value in the specified field, the value is stored in the array. If you link to a property of a control that does not correspond to an array, the last value will be used. Example) db:query1:field_1 |
|||||||||
| Row[value of key field] | Read Only |
It is an item that acquires data using the value of the specified key field. You can retrieve the row whose key field value matches the specified value. If there are multiple fields on the specified line, the value is stored in the array. If you link to a property of a control that does not correspond to an array, the last value will be used. Example) When the key field is set to "Name" in the following table and you specify db:query1:Row[John], you can get ["John", 38, "man"].
|
|||||||||
| Row[value of key field].FieldName | Read Only |
It is an item that acquires data using the value of the specified key field and the field name. You can get the value of the specified field name of the line whose key field value matches the specified value. Example) If you specify db: query1:Row[John].Age in the above table, 38 can be obtained. |
■ Items for "update" "add" queries
| item name | access method | Description |
|---|---|---|
| Row | Write Only |
It is an item for writing data of one line. If there are multiple fields added in the "write data" of the query setting, it is necessary to write the data with a comma-delimited character string. When the query type is "update", the data of the line corresponding to the condition specified in the query setting is updated. If the query type is "add", one row of data is added. Example) db:query1:Row If three fields "field_1", "field_2", and "field_3" are specified in the write data, it is necessary to write a character string such as "1, 2, 3". If the number of fields do not match, data will not be written. |
| FieldName | Write Only |
It is an item to write data in the specified field. For the field name, you can specify the field added by "write data" of the query setting. If the query type is "update", the data is updated according to the contents of the query setting except for the specified field. When the query type is "add", one row of data is added according to the contents of the query setting except for the specified field. Example) db:query1:field_1 |