ClearSCADA

SQL queries are used for embedded lists that can be placed onto mimics in ClearSCADA. If the content of the data needs to change based on some variables, then the SQL query should be contained within an animation.

When trying to enter SQL into animations there are several things to be aware of:

  • the entire expression must be enclosed within single quotes,
  • any carriage returns must be removed and
  • any single quotes within the expression must be replaced with two single quotes.

For example the query:

SELECT "FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "TypeDesc"
FROM CDBOBJECT
WHERE ( "ConfigTime" BETWEEN
{ OPC 'H' }
AND
{ OPC 'H + 1H' }
)
ORDER BY "~FullName" ASC

which selects all points which have been configured in the last hour would become:

'SELECT "FullName" AS "~FullName", "Id", "Foreground", "Blink", "Background", "TypeDesc" 
FROM CDBOBJECT 
WHERE ( "ConfigTime" BETWEEN { OPC ''H'' } AND { OPC ''H + 1H'' } ) ORDER BY "~FullName" ASC'
Additional carriage returns have ben added to the animated query to aid readability. The animation should not contain any carriage returns.
Note that the SQL is now enclosed within a single quote (') and that the single quotes around the time fields have been replaces with two single quotes.

The simplest way to convert an existing SQL query to a string for an Sql animation is to enter the SQL into an embedded list. On the query tab, right click and select the Generate Animation option. This will auto generate the SQL query into the appropriate form for an animation and add the query to the Sql animation of the embedded list.