Knowledge Base

We have a query execution defined to be repeated every 10 seconds and with a maximum received row count equal to 100:

Then in the query itself, we add conditions according our execution plan. We want to retrieve the last 100 values since the last retrieved value:

About the query:

SELECT TOP (100) * FROM [TrendTable1]

WHERE   
([Chrono]> ?Param01)
OR
([Chrono]=?Param01 AND [Name]>’?Param02‘)
OR
([Chrono]=?Param01 AND [Name]=’?Param02‘ AND [Value]>?Param03)
OR
([Chrono]=?Param01 AND [Name]=’?Param02‘ AND [Value]=?Param03 AND [Quality]>?Param04)

ORDER BY [Chrono] ASC, [Name] ASC, [Value] ASC, [Quality] ASC

TrendTable1: Name of the table
Chrono: Timestamp of the variable in the table
Name: Name of the variable in the database
Value: Value of the variable in the database
Quality: Quality of the variable in the database
Order: You need to sort (ASC) the result in order to retrieve the data chronologically

In the WHERE condition, we use dynamic parameters and we store them in PcVue Sql variables:

 Creation of these variables:

1- Param01 corresponds to the chrono, so we need to create a text variable

In Row selection: Select the last Row:

 In Mapping: Select FieldValue(“Chrono”) for Value

In parameters: Select =Value for ?Param01

In advanced tab of the variable, Check Save

2- Param02 corresponds to the Name, we also need to create a text variable similar to the previous one.
-Map the variable:
– In Row selection: Select the last Row
– In Mapping: Select FieldValue(“Name”) for Value In parameters: Select =Value for ?Param02
– Advanced Tab : Check Save

3- Param03 corresponds to the Value, we need to create a register variable.
– Map the variable:
– In Row selection: Select the last Row
– In Mapping: Select FieldValue(“Value”) for Value In parameters: Select =Value for ?Param03
– Advanced Tab : Check Save

4- Param04 corresponds to the Quality, we also need to create a register variable.
– Map the variable:
– In Row selection: Select the last Row.
– In Mapping: Select FieldValue(“Quality”) for Value In parameters: Select =Value for Param04
– Advanced Tab : Check Save

The attached sample project shows an example of using SQL queries attached to SQL connections defined in PcVue. In particular, it shows a possibility to repeat the execution of these queries automatically under certain conditions.

First of all, you need to enter the correct login and password for the SQL connection declared in the general part of the Application Explorer:

Check that the SV DbConnect service has been started:

The query will be launched every 10 seconds. It will retrieve all the information from the TRENDTABLE1 table by packets of 100 rows, starting from the last information retrieved previously.

After the development of such a query, it is mandatory to run it at least once by manually entering the parameters. For instance if we want to get all data:

Warning: if the database size is important you should not use 0 for the field ?Param01 (Chrono) but an acceptable value instead.

Then, since we save the value of the internal variables that are used both to define the parameters of the query and to store their new value after each execution, the system works continuously.

If we look at the trend curve, we can see that the variable SQL.Query01.F1R1_TEMP only changes value every 10 seconds, at the rate of the SQL query. But in fact it changes value several times and all these changes are recorded with the timestamp coming from the TRENDTABLE1 table. So when we press the refresh button, we see a nice smooth ramp instead of stairs in real time.

In order to record all the changes of values that can exist since the last execution of the query, we should not use a filtering period while we configure the trend for SQL.Query01.F1R1_TEMP variable, as explained in the Sql Variables and Data Archiving article.

Sample project:

RepeatSqlQuery.zip