PLEASE NOTE: If using Azure SQL Server, you will need to have admin access to Azure SQL Server. Please see the section titled "Configuration for Azure SQL" for instructions on how to setup Crimson database.
Introduction
This document provides a description of configuring and using the SQL Queries section of Crimson 3
APPLICABLE PRODUCTS
CR3000 HMI / Graphite® HMI / DA30D Data Station / DA50 / DA70
PRE-REQUISITES
Crimson 3.x Group 3 License
Crimson® 3.0 (Build 702.002 or later) all builds of Crimson 3.1 & 3.2
IN THIS ARTICLE
Configuring a Basic SQL Query
1. Create array tags for each required column of data in the SQL Table. In this recipe example, there will be one string tag (RecipeNames) and 5 numeric (integer) values as shown in Figure 1.
Figure 1.
2. Navigate to the SQL Queries section of Crimson 3, as shown in Figure 2.
Figure 2.
3. Set Enable SQL Queries to Yes. Configure the SQL Server Connection as required.
4. Click New-SQL Query from the top of the Navigation Pane, as shown in Figure 3.
Figure 3.
5. Click on the newly created Query.
6. Configure as needed for the application.
a. Query Mode
- Periodic: the query will run at the specified Update Rate.
- On-Demand: the query will run when the RunQuery user function is executed.
b. Table Name: the name of the table to query.
c. Schema Name: type of schema used by the table, the default dbo is acceptable for most applications.
d. Number of Rows: number of rows to be requested by the query.
e. Sort Results: choose how to sort the results.
f. Sort by Column: which column to sort on.
g. Filtering will be covered in a later portion of this Tech Note.
Figure 4.
7. Click on Column1, as shown in Figure 5.
Figure 5.
8. Set the SQL Column Name to the column of the table that is being queried.
9. Set the SQL Type to the data type of the data in the column. Figure 6 shows a column named Recipe with a type of VARCHAR.
Figure 6.
10. Click on Row_1 under the Recipe column.
11. Click on the tag to associate with this column in the Resource Pane. Assuming the tag is an array, click the Toggle Array button at the top of the Resource Pane to show all elements of the array, as shown in Figure 7.
Figure 7.
12. Drag each element of the array in the Resource Pane to the associated row in the Navigation Pane.
Figure 8.
13. To query additional columns, click on the Query (Query1 as shown in Figure 9) in the Navigation Pane and then click New-Column.
Figure 9.
14. Repeat steps 7 through 13 as required. Figure 10 shows a completed query for a name and five values.
Figure 10.
NOTE: Not all columns in the table need to be queried, but all of the columns configured, must exist in the table, or the query will fail.
Configuration for Azure SQL
1. When configuring your Crimson 3.x database to access an Azure managed SQL server, you will need to make a tag for the FQDN.
2. Go to Data Tags and create a new string tag
3. Set source as "General", then enter your FQDN of your server into the text box as seen below
5. Navigate to SQL Queries tab and set your SQL Server Connection to Server: Dynamic Name, then add your tag in the text box as seen below
6. You will need to enter your login name as the admin user followed by the machine name. Example below:
Executing On-Demand Queries
1. Add a button to a display page.
2. Open the button's properties.
3. Referring to Figure 11, click on the Action tab.
4. Type RunQuery("Query1") in the On Pressed field. Replace "Query1" with the desired Query name to run.
Figure 11
5. Click OK.
Figure 12 shows the SQL Table from the server.
Figure 12.
Figure 13 shows an example of the result.
Figure 13.
Adding Filters to Queries
In Crimson 3.0 and builds of Crimson 3.1 prior to build 3113.000, the filters rely on fixed values. The filter shown in Figures 14 and 15 will only pull the rows where the value in the Val1 column are less than or equal to 1000.
Figure 14.
Adding Filters in Crimson 3.x
1. Click on the Query to filter.
2. Click on the Configure Filters link as shown in Figure 16.
Figure 16.
3. At the top of the Filters Window, configure the filter to add.
4. Referring to Figure 17, click the first drop-down and select the column to be used in the filter.
Figure 17.
5. Select VAL1.
6. In the next drop-down, select the comparison tool to be used in the filter.
7. Enter the value to be used in the comparison in the text edit field, 1000 as shown in Figure 18.
Figure 18.
8. Click the Add button to add it to the filter list.
Figure 19.
9. Additional filters can be added, including a new drop-down for ANDing or ORing it with the previous filter.
10. Click OK when all filters have been added.
Figure 20 shows the results of the original query with the filter applied, refer to Figure 13 for the unfiltered results.
Figure 20.
Adding Static Filters in Crimson 3.x
1. Click on the Query to filter.
2. Click on the Edit Filters link as shown in Figure 21.
Figure 21.
3. Click the Add Condition button at the bottom of the Filters Window.
4. Select the Column, Operator, and Filter as needed.
Figure 22.
5. Additional filters can be added, including a new drop-down for binding a condition with the previous.
6. Click OK.
Refer to Figure 20 above to view the results of this filter.
Adding Dynamic Filters in Crimson 3.x
Dynamic filters can be used to limit the amount of data transferred from the server to the unit and will alleviate the need for multiple array tags.
This example will begin with a single column query based on stopping at step 12 of the Configuring a Basic SQL Query section above, adding an Ascending sort on the Recipe (only) column.
1. Create a new query.
2. Referring to Figure 23, configure the Query to be On-Demand, Table1, and one row.
Figure 23.
3. Add five more columns to Query2.
4. Adjust the SQL Column Name and SQL Type of each to match the table's column names (Recipe:VARCHAR and Val1-Val5: all INTs, in the example shown in Figure 24).
Figure 24.
5. Navigate to the Data Tags section.
6. Create 6 new Numeric tags.
7. Rename them Val_1-Val_5 and Index, as shown in Figure 25.
Figure 25.
8. Navigate to the SQL Queries section.
9. Click Row_1 under each column, excluding the Recipe column) and drag the appropriate tag from the Resource Pane and drop it on the Row_1, refer to Figure 26.
Figure 26.
10. Click on Query2.
11. Click the Edit Filters link.
12. Click the Add Condition button.
13. Set the Column to Recipe, Operator to =, and the Filter to RecipeNames[Index], as shown in Figure 27.
Figure 27.
14. Click OK twice.
15. Navigate to the Display Pages.
16. Add a new Data Box to the page.
17. Open the new Data Box's properties.
18. Set the Value to RecipeNames[Index].
19. Add a method to change the value of Index, either a Data Box set for Data Entry, or a pair of buttons set to increment and decrement index.
20. Add the five Val_X tags to the page.
21. Copy and paste the button created in Executing On-Demand Queries section above.
22. Open the new button and modify its Action tab to run Query2, as shown in Figure 28.
Figure 28.
23. Click OK.
24. Download the database to the unit.
25. Press the Query1 button to populate the RecipeNames Array, as shown in Figure 29.
Figure 29.
26. Modify the value of Index until the appropriate recipe is selected.
27. Press the Query2 button to populate the Val_X values, as shown in Figure 30.
Figure 30.
Additional Information
Disclaimer
It is the customer's responsibility to review the advice provided herein and its applicability to the system. Red Lion makes no representation about specific knowledge of the customer's system or the specific performance of the system. Red Lion is not responsible for any damage to equipment or connected systems. The use of this document is at your own risk. Red Lion standard product warranty applies.
HMS Technical Support
If you have any questions or trouble, contact HMS Technical Support by clicking HMS Support Portal