Examples

EXAMPLE

dpQuery("SELECT ALERT '_alert_hdl.._value' FROM '*' WHERE _DPT =\ "ExampleDP_Float\" ",
            tab);

Note:

Note that a SQL query has to be defined on one line. In the example above, it is split into two lines for lack of space.

dpQuery("SELECT '_online.._value' FROM '*' WHERE (_ELC==DPEL_FLOAT) & '_online.._value > 1");

Note:

In this example, for performance reasons, the data point expression should be specified directly after the FROM as then not all data point values will have to be read out.

EXAMPLE

This query reads out the online value and the time when this online value has been set. In this case,

all the DPs whose leaves are of the type "bool" are queried. The constants that can be used are listed under Data types of data point elements.

select = "SELECT '_online.._stime','_online.._value' FROM '*' WHERE _ELC ==DPEL_BOOL";

Here, only the ends of the leaves are selected AND the user bit 7 must be set.

select = "SELECT '_online.._stime','_online.._value' FROM '*' WHERE _LEAF AND '_online.._userbit7' == 1";

If the DPtype is called "query", all the values are output as displayed in the first part. (An example of this

is that also entire DP parts can also be specified in front with, for example, EL.)

select = "SELECT 'Text1:_online.._value','Text2:_online.._value', 'Text3:_online.._value' FROM '*' WHERE _DPT = \"Query\" ";

Note:

Only configs managed by the event manager can be queried. These are user range of values, WinCC OA range of values (_u_range, _pv_range), authorization (_auth), DP functions (_dp_fct), substitute value (_default), alert handling (_alert_hdl), alert class (_alert_class), online, original value (_online, _original) and lock (_lock).

Different data points are used in the examples below. In addition to mld_float of the type ExampleDp_Float and mld_bit (ExampleDp_Bit) the following additional data points exist:

The data point Gas_pipe_1 is of the self-created type Gas_pipe.

The data points Engine1 and Engine2 are of the self-created type Engine.

Figure 2. Data points Engine1 and Engine2

Querying current data

With the command:

SELECT '_online.._value' FROM 'mld_float.**'

the current data is queried, in the same way, for example, as with dpGet. In this specific case, all current data with the structure "mld_float.:_online.._value" (and which are found in the current system) would be found.

This command should be used from the Control script as follows:

dpQuery ("SELECT '_online.._value' FROM 'mld_float.** '", tab);

The query command returns a two-dimensional matrix as the result. They are transferred to the variables tab here. As it is not known which format the data of the matrix will have, the variable tab is declared as the type dyn_dyn_anytype .

When reading out data, note that the requested data is stored in the second column. Due to an automatically added header line, the first value is found in cell [2][2], the next one is in cell [3][2], ..., [n][2] (with n e N+{ 2 £ n; n = last line}).

Querying archived messages

As described earlier on, the managed values are divided into different database areas. In CONTROL, SELECT is used to switch to the area with the "normal" values. If SELECT ALERT is specified, the saved messages are read out.

dpQuery ("SELECT ALERT '_alert_hdl..value' FROM 'mld_bit'", tab);

With the entry of the DP level (here: "mld_bit") the depth (".**") does not need to be specified: they are supplemented by Control, if applicable (so ".**" may be omitted. On the other hand, the data point section must always be specified from the config level (KF.DT.AT) .

Only react to changes

With query functions, as with standard control programming, it is possible to only read out changes of the values. With a dpQueryConnect function, this can be realized as follows:

main(mapping event)
{
  dpQueryConnectSingle("workfunc", TRUE, "EV_connect_single", "SELECT '_online.._value'FROM 'ExampleDP_Arg*'");
}
workfunc(string ident, dyn_dyn_anytype result)
{
  DebugN(ident, result);
}

The line above is used to set up a connection to all online values of the data points that begin with ExampleDP_Arg_.

In the event of a change, the function workfunc is called here and two parameters are transferred. In the latter case, the results of the query are written. When information is read out, there is a difference between data and messages, as can be seen in the chapter "queries".

If own values have to be transferred to the workfunc subroutine, this is not possible via the function call, since here only the two transfer parameters "ident" and "result" are expected. However, own values may reach the subfunction if global variables are used.

The second entry in the main function (here TRUE) indicates whether at the moment of connection, a complete image of the current status of the database is to be transmitted.

Functional flow of dpQueryConnectSingle with ALERT SINGLE

A value is only written if one of the registered values leaves the valid range of values or returns to these limits. No value is written, however, on startup with a valid value.

EXAMPLE

The data point mld_float is used again for the following examples. The breaking down of wildcard addresses down to element levels can be seen in the table there.

main(mapping event)
{
  dpQueryConnectSingle("workfunc", TRUE, "EV_connect_single", "SELECT '_online.._value'FROM 'ExampleDP_Arg*'");
}
workfunc(string ident, dyn_dyn_anytype result)
{
  DebugN(result);
}

EXAMPLE

Here, the alarms of all DPs are queried and displayed in a line

/* SimpleCtrlScriptStart {invalid} SimpleCtrlScript {SetValue} object {Text1} attribute
{backCol} value {Red} SimpleCtrlScriptEnd */
main()
{
  int rc;
  setValue("Text1","backCol","Red");
  rc = dpQueryConnectSingle( "work", 0, "ident", "SELECT ALERT SINGLE '_alert_hdl.._value'
  FROM '*'" );   DebugN ("nach QueryConnectSingle: ", rc );
}
work( string ident, dyn_dyn_anytype val )
{
  int i;
  DebugN( "Work: ", dynlen( val ) );
  for (i = 2; i <dynlen(val ); i++ )
  DebugN( "value: ", i, val[i][1], val[i][2] );
}

All the leaf nodes of data points, and all their original values

The query is as follows:

dpQuery("SELECT '_original.._value' FROM 'Engine1' WHERE _LEAF", tab);

Engine1 is extended internally to Engine1.** to return all elements of the data point.

Then the attribute _original.._value is selected from all the elements.

In the third step, this result is checked against the query conditions. Here, all the current values of the individual elements (Engine1.torque:_original.._value and Engine1.Fault:_original.._value) of the data point Engine1 are output.

If, for example, the last 100 lines of all data points are searched for from the database, the query must be formulated differently:

dpQuery("SELECT '_original.._value', '_original.._stime' FROM '*' SORT  BY 2 LAST 100", tab);

With this query it is important to sort the interim table to actually obtain the last 100 values. Generally speaking, the order of the query results is therefore undefined. If there are many data points in the database, this results in the creation of a large interim table. In this case it is better to call a TIMERANGE function with the appropriate bonus parameter. So to obtain the last 100 values, you have to transfer

 dpQuery("SELECT '_original.._value' FROM 'ExampleDP_Arg1' TIMERANGE(\"NOW\", \"2020.06.10 17:11:00.000\", 1,100)SORT BY 1", tab);

Here the current time is specified as the starting time, the stop time is not named. The last 100 values are updated and sorted by the time.

The current torque of all engines of a system

We require that all engine data points are derived from the same data type "engine", which contains an element torque. The query is as follows:

dpQuery("SELECT 'value' FROM '*.torque:_original..' WHERE _DPT =

\"engine\"", tab);

Another possible query:

dpQuery("SELECT '_original.._value', '_original.._stime' FROM 'engine*.torque'", tab);

In addition to the requirement that all engines have to be of the same type, you must ensure that data points to be created also comply with naming conventions for engines. The source time is also output here.

Note:

Generally speaking, with a query of the type mentioned above, the second version is preferable, as with the first variant, all the data points with an element "torque" are read out first and then in a further step the query is restricted to the data point ENGINE.

List of user value ranges of particular elements

The following query can be used to determine different attributes (here: "min" and "max") of certain data points:

dpQuery("SELECT 'min', 'max' FROM 'gaspipe1.**:_pv_range'", tab);

The result in this case is the appropriate limits of the config "WinCC OA range of values". The detail is added internally as 0 ('gaspipe1.**:_pv_range.0.min').