dpQuery()

Retrieves attribute values with the help of SQL statements.

Synopsis

int dpQuery (string query, dyn_dyn_anytype &tab);

Parameter


Parameter	Meaning
query	SQL statement. See the example at the end of this page as well as chapter Queries and SQL keywords. VORSICHT: 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 execution is successful, -1 in the event of an error.

Anmerkung: If more values are returned for a dpQuery query than the specified limit, an error message is shown. The error can be retrieved with a subsequent call of the function getLastError(). See also Error Handling.

Error

Errors can be retrieved with getLastError(). This includes incorrect or missing arguments. Erroneous queries result in an error message.

Description

The function dpQuery() queries attribute values with the help of the SQL statements formulated in the string query. Please refer to the section of the manual on SQL for information about the SQL dialect used in WinCC OA. The result is stored in tab as a two-dimensional table (see below). When querying historical values from a value archive (HDB), consider the notes in the chapter Swap out/backup archives.

All attributes of configs that are held by the Event manager, such as _original, _online, _alert_hdl etc. can be queried, for example:

 "SELECT '_online.._value' FROM '*'"

But 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, for the keyword TIMERANGE only times until the year 2038 may be used. This restriction does not apply in projects using SQLite.

Anmerkung: Queries with 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.

Anmerkung: _status64 can be used as part of 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 timewindow. 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).

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]);                  
}

Assignment

Data point function, waiting Control function

Availability

CTRL