Queries

Structure of queries

Within the query commands, there is a series of functions available for selecting data points or data point attributes and processing the result. Different command elements whose sequence is defined can be used since the expressions are logically linked.

Consider the following:

• The smaller a table, the better the performance (use FROM, WHERE_DPT and _DP filters).
• Restrict the table using the FROM key word.
• Use WHERE _DPT (but only with = Without LIKE and OR) if the DPs of only one type are not already returned by using the FROM clause.
• FROM clause: A* is considerably faster than *A.
• In an SQL query where you query DP names, the FROM section may only contain a config and an attribute, but not additional DP elements.

EXAMPLE

The following query could be improved on:

dpQuery("SELECT '_online.._value' FROM '*.Devices.**' WHERE _DPT=\"Station\"", data);

Possible improvement would be:

Station_*.Devices instead of *.Devices (if all stations begin with Station_*)

EXAMPLE

The following is an unnecessary query and its elements are explained in continuation:

dpQueryConnectSingle("evStatusCB", true, "evStatus", "SELECT '_online.._value' FROM '" + reduManagerDpe + "' WHERE _DPT = \"_ReduManager\"");

SELECT

Selection of data in the database.

 string strQuery = "SELECT '.name:_original.._value,.Datapoint1.Element1:_original.._value' FROM '*'";

 string strQuery = "SELECT '.name:_original.._value,.Datapoint.Element1:_original.._value' FROM '*' WHERE _DPT = \"ExampleDp_Float\"";

[ALL]

Returns all values from the database, current as well as historical data, that fit the FROM statement.

[ALERT]

Alarms (see _alert_hdl) are selected in conjunction with SELECT.

SELECT ALERT registers only alarm attributes, which can be also queried by alertGet*(). Changes of other configs or_alert_hdl attributes, which do not belong to this list and so do not trigger a hot link.

The configs _original and _online are adopted from the process image, if the config had not been changed.

Event configs (_general, other_alert_hdl,_u_range, etc.) are adopted by the event manager.

The rest of the configs ( _offline , non-event configs, _original / _online that do not belong to the alarm) are queried by the data manager per dpGetAsynch() .

 "SELECT ALERT '_alert_hdl.._add_value_5' FROM 'myAlarm.' WHERE '_alert_hdl.._add_value_5' == TRUE"

"SELECT ALERT '_alert_hdl.._add_value_5' FROM 'myAlarm.' WHERE '_alert_hdl.._add_value_5' == 1"
"SELECT ALERT '_alert_hdl.._add_value_5' FROM 'myAlarm.' WHERE  '_alert_hdl.._add_value_5' LIKE \"TRUE\""

[ALERT SINGLE]

The alarm which led to the last modification of alert handling (with dpQueryConnectSingle()) is selected in conjunction with SELECT.

'Columns' FROM 'Lines'

Define the data that is selected by SELECT.

[FROM 'DPGROUP']

Restriction of the query to data point groups.

Plant model (CNS) Paths

Plant model (CNS) paths can be used for the FROM Statement. You can only address attributes that are part of a Config. Plant model (CNS) paths can be used in the FROM part of a query in order to select datapoint elements. Thereby the same rules as for datapoint names apply. Wildcards (see also description of the panel above) and brackets can be used. A plant model (CNS) identifier must be used as follows: "SYS.VIEW:Node:"

• A normal string must match this node name.
• A string with a simple wildcard, '?' or '*' wildcard will only match this node unless you are looking for leaves (pattern ends with '**$') or you have previously encountered an AnyLevelWildcard (see below).
• A string with a double placeholder, '**', an AnyLevelWildcard, corresponds to this level and all levels below it, including all computed item names.
• With curly brackets { strings between the curly brackets are searched. The strings between the curly brackets are separated with comma `,`.
• A string with square brackets, `[` is only valid for element name search, since these characters are invalid for plant model (CNS) lookups!

[REMOTE]

The system name is specified here for distributed systems.

Following behavior of the dpQeryConnect functions can be seen at run-time when using REMOTE <system name>:

1. If the functions are called for a system, the system must be connected at that moment, else the connect will raise an error.
2. If a connection to a system is lost, no hotlink will be raised.
3. If the connection to a system, which was already connected previously,will be reestablished, a hotlink will be raised.

[REMOTE ALL]

Executes a query to all systems. Internal messages are sent to all known and future systems, including the own system if the Dist manager runs. If the Dist manager does not run, an error message is shown and only the result of the local system is returned.

Behavior of the dpQeryConnect functions at run-time when using REMOTE ALL:

1. The functions will be used on every connected system (Dist manager runs).
2. If a connection to a system is lost, no hotlink will be triggered.
3. When the connection to a system is (re)established (even when the system was not connected), the system will trigger a hotlink.

[ WHERE ]

Restriction of the selected data set (see also SQL keywords).

[ TIMERANGE () ]

Time-controlled querying of information (see also SQL keywords).

[ SORT BY y1[, y2, ...]] or [ORDER BY y1[,y2, ...]]

Sort selected data (see also SQL keywords).

[ GROUP BY y1 [, y2, ...]]

Grouping of the selected information (see also SQL keywords).

[ FIRST | LAST x ]

Further restrictions of the selected data (see also SQL keywords).

A query type query makes use of a particular syntax. Keywords such as SELECT are written in uppercase. Usually case-sensitive. Strings are enclosed by double quotation marks ("), data point identifiers are enclosed by single quotation marks ('). Wildcards may be used.

FILTERLANG 'de_AT.utf8' / FILTERLANG <globLangIndex>

The keyword FILTERLANG can be used to specify a project language for a query. When the keyword is being used, a query result is filtered by the specified language. The keyword must be specified as the last keyword of the WHERE clause. Both, the language description such as 'de_AT.utf8' or the integer language index such as 10000 can be used. If the keyword FILTERLANG is not being used, the result of the query is filtered by the default language of the project (the first language in the config file or the language specified by using the config entry "lang"). The following example shows how to use the keyword:

main()
{
  int rc;
  dyn_dyn_anytype ddt;
  dyn_dyn_string dpt;
  dyn_dyn_int dptVal;
  string typeName;
  string dpName;
  langString val;
  typeName = "testLangStringType";
  dpName = "_testFilterLang";
  if (dpExists(dpName)) //If a data point exists
    dpDelete(dpName);   //delete the data point
  if (dpTypeExists(typeName)) //If the data point type exists
    dpTypeDelete(typeName); //delete the type
  dpt[1] = makeDynString(typeName);
  dptVal[1] = makeDynInt(DPEL_LANGSTRING);
  dpTypeCreate(dpt, dptVal);  //Create a data point type
  dpCreate(dpName, typeName); //Create a data point
  setLangString(val, 0, "Deutsch"); //Set the texts for the data point
  setLangString(val, 1, "English");
  setLangString(val, 2, "Russian");
  dpSet(dpName + ".:_original.._value", val);
  // ------------------------------------------------------------------------------------
  //Query the values by using the FILTERLANG keyword and output the result:
  rc = dpQuery("SELECT '_original.._value' FROM '" + dpName + "' WHERE '_original.._value' = \"English\" FILTERLANG 'en_US.utf8'", ddt);  //Result: English
  DebugN("Return value:", rc, "Search result:", ddt);
  rc = dpQuery("SELECT '_original.._value' FROM '" + dpName + "' WHERE '_original.._value' = \"Deutsch\" FILTERLANG " + getGlobalLangId(0), ddt);  //Result: German, by using lang index for the query
  DebugN("getGloablLangId:", getGlobalLangId(0));
  DebugN("Return value:", rc, "Search result. Lang idx was used for the search:", ddt);
  rc = dpQuery("SELECT '_original.._value' FROM '" + dpName + "' WHERE '_original.._value' = \"English\" FILTERLANG 'de_AT.utf8'", ddt);  //No result since English is searched in German language
  DebugN("Return value:", rc, "No result since English is searched in the German language:", ddt);
}

Result of the queries

The result of a query type query is returned as a two-dimensional data field and can therefore be considered a table. The expression after the keyword SELECT specifies a subset contained in the set that is determined by the keyword FROM, see also the following example.

EXAMPLE

dpQuery("SELECT 'KF.DT.AT' FROM 'DP.EL'", tab);

The variable "tab" of the type dyn_dyn_anytype allows return values of any type.

A query generally produces the following result:

The first line has a special role. The first column is empty, from the second column of the first line, in the column header the markedness of the SELECT-part of the query (configs, maybe with elements) returns.

The queried value set starts from line 2. Column 1 contains the name of the queried value set. The values of the query are saved and indexed in the second column.

EXAMPLE

The following examples use a data point mld_bit of the type ExampleDp_Bit and a data point mld_float of the type ExampleDp_Float.

Here a query of the values mld_float.:_online.._value and mld_float.:_online.._stime

main() 
{
  string start='"'+"1998.10.27 00:00:00.000"+'"';
  string stop='"'+"1998.10.27 14:50:35.000"+'"';
  dyn_dyn_anytype tab;
  int length, run;
  dpQuery("SELECT '_online.._value','_online.._stime' FROM 'mld_float' TIMERANGE(\""+start+"\",\""+stop+"\",1,1)",tab);
  length=dynlen(tab);
  DebugN("Length :",length);
  for (run=1; run<=length;run++)
  {
     DebugN(tab[1]);
     DebugN(tab[2]);
     DebugN(tab[3]);
     setValue("Tab2","appendLine",dynlen(tab), "Column 1", tab[run][1], "time", tab[run][2], "value",tab[run][3]);
  }
}  

Refer to the appropriate chapter for information on how to use the TIMERANGE command and the format of the variables start and stop.

Difference between data and messages

In contrast to data (for example, dpQuery(SELECT...)), if messages are queried (for example, dpQuery(SELECT ALERT...)), an additional column is generated since the message time is also returned with a message. For this reason, when data is queried, the actual values are found in column 2 and if messages are queried, the values are in column three. In the latter case, the first value is therefore found in cell [2][3], the next in cell [3][3], ..., [n][3] (with n from N+{ 2 <= n; n = last line}).

EXAMPLE

main()
{
  string start='"'+"1998.10.27 00:00:00.000"+'"';
  string stop='"'+"1998.110.01 11:00:00.000"+'"';
  dyn_dyn_anytype tab;
  int length, run;
  dpQuery("SELECT ALERT '_alert_hdl.._value',
  '_alert_hdl.._text'
  FROM 'mld_float'
  TIMERANGE(\""+start+"\",}\""+stop+"\",1,1)",tab);
  length=dynlen(tab);
  DebugN("Length :",length);
  for (run=1; run<=length;run++)
  {
    DebugN(tab[1]);
    DebugN(tab[2]);
    DebugN(tab[3]);
    setValue("Tab2","appendLine",dynlen(tab),
    "Column 1", tab[run][1],
    "time", tab[run][2],
    "Value",tab[run][3],"Text", tab[run][4] );
  }
}

Refer to the appropriate chapter for information on how to use the TIMERANGE command and the format of the variables start and stop.

With this query, the data point used had the config alert handling.

Data point groups

You can query data point groups. The syntax is:

SELECT ... FROM 'DPGROUP(<gName>)' WHERE ...

or

SELECT ... FROM '{DPGROUP(<g1>),DPGROUP(<g2>)}' WHERE ...

You have to use the name of internal data point for the group, wildcards are not possible with groups. The combination of groups with data points is possible.

EXAMPLE

"SELECT '_original.._value' FROM 'DPGROUP(_DpGroup00006_Public)'"

Distributed systems

To use SQL in distributed systems, an optional entry must be made for the queries:

...REMOTE 'sysname'

The REMOTE entry is placed at the end of the SQL statement. Only one system name can be specified. It must be enclosed by single quotation marks.

...REMOTE ALL

The REMOTE ALL entry is placed at the end of the SQL statement and executes a query to all systems. Internal messages are sent to all known and future systems, including the own system. The work function (for example, with dpQueryConnectAll()) is called from each response.

EXAMPLE

"SELECT '_original.._value,_online.._stime' FROM 'ExampleDP_Arg1.' REMOTE 'System2'"

EXAMPLE

query = "SELECT '_original.._value' FROM 'ExampleDP_Arg*' REMOTE ALL";
dpQueryConnectAll("work",true,"Dist test",query);

Processing

The internal processing of a query is carried out in the sequence of the keywords. First, the query is carried out from the database (SELECT or SELECT ALERT) whereby conditions that affect data point types or only attributes that occur directly in the list of columns can be analyzed immediately. In the next step, all the other conditions are verified. The interim table obtained can then be sorted. After sorting, the number of lines returned in the result is defined. Each returned line is, wherever possible, always completely filled with the current valid values.

In case of a hot link connection to current values, the table is evaluated each time a new value occurs. Sorting is taken into account, if the whole table is required as the result. Restrictions to the number of lines is, however, not supported in this mode.

The GROUP BY function is only evaluated if there is no hot link connection. The sorted result table is processed line-by-line and an entry is inserted in the group table for each group. If a group function is listed in the argument before FROM and no GROUP BY clause is defined, the query returns exactly one line with the summary of the entire results table.

In the FROM part the characters,"{" and "}" can be used to further restrict the data points.

string start="1998.10.28 12.30.00.000"
string stop="1998.10.30 12.45.00.000"
dpQuery("SELECT 'SUM(_online.._value)' FROM '{alt_bit,alert_float}'
WHERE _DPT = \"ExampleDP_Float\"
TIMERANGE(\""+start+"\",\""+stop+"\", 1, 0)
ORDER BY 0 GROUP BY SECS(10)", tab);

In this example, first, all the data points of the type ExampleDP_Float are queried in the time range start to stop and are saved in an internal results table. In a second step, the sums of the determined values are formed, they are then sorted according to their size and are stored in groups with Dt = 10 seconds.

See the relevant chapter of this section for explanations on the TIMERANGE or group functions.

See also the functions isAnswer() and isRefresh().