dpQuery()
Retrieves attribute values with the help of SQL statements.
Synopsis
int dpQuery (string query, dyn_dyn_anytype &tab);
Parameters
| Parameter | Meaning |
|---|---|
| query |
SQL statement. See the example at the end of this page as well as chapter Queries and SQL keywords. CAUTION:
Connect functions can
only be used for configs which are managed by the event manager.
Therefore it is not possible to make a connect on the following
configs: _archive, _address, _cmd_conv, _msg_conv,
_smooth
|
| tab | Two-dimensional results table. See the example at the end of this page as well as chapter Queries and SQL keywords. |
Return Value
0 if the execution is successful and -1 in the event of an error.
Errors
Errors can be retrieved with getLastError(). This includes incorrect or missing arguments. Erroneous queries result in an error message.
Details
The function dpQuery() queries attribute values using the SQL statements formulated in the string query. For information on the SQL syntax used in WinCC OA dpQuery(), refer to the section of the manual on SQL. The result is saved in tab as a two-dimensional table (see below). When querying historical values from a value archive (HDB), refer to the notes in the chapter Swap out/backup archives.
All attributes of configs that are saved in the Event Manager, such as
_original, _online, _alert_hdl etc., can be queried:
"SELECT '_online.._value' FROM '*'"However, configs from other managers cannot be queried in this way, e.g:
"SELECT '_alert_hdl.._active' FROM '*' WHERE ('_alert_hdl.._active' == 0)").In projects with RAIMA archiving, only times up to the year 2038 may be used for the
keyword TIMERANGE. This restriction does not apply to projects that
use SQLite.
SELECT ALL
are now correctly returning an error as they are not supported in SQLite-NGA
projects.It is not allowed to refer to an element in the
SELECT part without the WHERE part containing
_DPT = \"XXX\" . The columns of the result table are not clear
anymore without a clear DP type!
Table: Example of the result in the variable tab
| tab[1][1] (empty) | tab[1][2] column header |
| tab[2][1] line name 2 | tab[2][2] contents of line 2 |
| tab[3][1] line name 3 | tab[3][2] contents of line 3 |
| ... | ... |
The first entry tab[1][1] is not used, tab[1][2] contains the overview of the columns and tab[n][1] (n>1) contains the name of the column in question. The name found in the query is finally located in tab[n][2], n>1.
SELECT in a dpQuery with aggregates for NGA. An aggregate
function must not be specified. The _status64 is taken from the last value to be
still included in a time window. Meaning from the value with the highest timestamp
in the aggregation window. e.g.:
SELECT '_original.._stime', 'AVG(_original.._value)', '_original.._status64' FROM 'MyDpe.' TIMERANGE ... SORT BY 0 GROUP BY SECS(500).Example
The original value of data points beginning with ExampleDP_Arg and
of type ExampleDP_Float is returned.
main()
{
dyn_dyn_anytype tab;
int z;
dpQuery("SELECT '_online.._value' FROM 'ExampleDP_Arg*' WHERE _DPT= \"ExampleDP_Float\" ", tab); // _DPT returns the type of a data point.
shape list1=getShape("SELECTION_LIST1");
for(z=2;z<=dynlen(tab);z++) // dynlen(tab) returns the length of tab
setValue(list1,"appendItem",tab[z][2]);
}