How to configure a Sql grid control

The Sql grid is a control designed to display the result of Sql queries in a grid control. Based on pre-defined Sql queries, it is able to request data upon mimic opening and other triggers, and display the returned data under the form of a data table.

Prerequisites

• You have configured a Sql read query with consumer type User interface. Refer to How to configure Sql queries topic to learn more.

• You have created the mimic in which the Sql grid control is to be inserted.

Inserting a Sql grid control and configuring the Aspect and General properties

1. Open the mimic in design mode and click the Insert menu.

2. Click Sql grid, the Properties dialog of the control opens. The first tab is Aspect tab, here you can configure the following properties: Show picture

   • Border and Appearance properties - Define the style of the control's border.

   • Position and Size properties - Define the control's position and size in the mimic.

   • Locked - If selected, the control cannot be moved or changed while in Design mode.

   • Save properties as text (ASCII mimic only) - If selected, the control's properties are saved in ASCII format. The mimic must also be saved in ASCII.

     

     You can specify more properties to control the graphical element visibility. From the Aspect tab, behind the down arrow at the bottom of the Properties dialog box:

     • Specify zoom visibility bounds so that the graphic control is only visible for predefined zoom levels.

     • Specify layers so that the graphic control is only visible if one of the selected layers is displayed.

     These are even more useful when combined with user permissions and decluttering templates.

3. Select the General tab, here you can configure the size of the grid, the number of rows and columns, and more via the following properties:

   • Rows / Columns - Specify the total number of rows and columns respectively, including any header rows and columns. For example, if you want a grid of 10 x 3 cells and both row and column headers, the number of rows/columns to configure is 11 x 4. When referenced programmatically the top left cell, excluding any headers, has an index of 0,0.

     There is no enforced limit for the maximum number of cells but it is recommended that the total number of cells is kept below 100,000.

   • Fixed Row/ Col - Specify the number of header rows and columns. When referenced programmatically, the cells of the header rows and columns are indexed negatively. A value of zero means that no row or column header is displayed. The cells that make up the headers behave differently compared to the other cells. Certain actions cannot be perform on them such as changing their color from the configuration dialog, select them or type text into them (although you can change their label using a script).

     You must have a header row and column if you want to allow the user to resize the columns and rows at run time.

   • Scrollbars - If selected, it enables the display of scroll bars in the grid. It can be used to navigate through the cells at run-time if the number of cells is greater than can be displayed in the grid control's visible area.

   • Background - Defines the background color of the cells.

   • Gridline color - Defines the color of the grid displayed between the cells.

   • Text color - Defines the color of any text displayed in the grid.

   • Font -Defines the font type and style for all the cells including the headers.

   • Selectable - If selected, enables the selection of cells by pointing and clicking at runtime.

   • Multiselection - If selected, enables the selection of multiple cells at once.

   • Autosize - If selected, it enables the automatic adjustment of the width and height of each cell to fit the available space. All rows will have the same height and all columns will have the same width. The effect of any previous resizing is lost.

   • Frame selection - If selected, enables highlight of selected cells with a black frame.

   • Track selection - If selected, causes the headers to indicate the cell that is currently selected by adopting a relief appearance.

   • Enable Drag & Drop - If selected, enables dragging and dropping the contents of one cell to another.

   • Editable - If selected, enables entering of text into cells.

   • Sort on Header Click - If selected, it enables the sorting of the contents of the entire grid at run-time, using to the content of a single column.

Configuring the Queries tab properties

1. Select the Queries tab.

2. Select a Sql connection from the Sql connection name drop-down.

   Several Sql queries can be configured in the grid but only the one flagged as selected is used to fill the control at runtime. Associated variable or SCADA Basic give you a way to switch from one Sql query to another at runtime.

3. Click in a Sql query cell to select the query. The query must be configured to be consumed by a user interface. If the query comes with parameters, parameter columns are added to the table to configure the parameters substitution. It is possible to select the same query several times in case you need to use the same query with different parameter values. Show picture

   

You can further configure the queries:

• To substitute a parameter with a variable, select the icon in the value column and select a variable from the Variable selector dialog. You can also enter a static value directly or enter an expression. For example, selecting the variable "@MyVariable" in the value field for ?Param01 will substitute the value of the parameter called Param 01 with that of the variable called @MyVariable.

  Query parameter substitution is saved as part of the mimic.

• To define the default Sql query, tick the Sel checkbox for the desired query. It is possible to validate the configuration without any default query selected, an associated index variable must then be used to specify the default query. SCADA Basic scripting can also be used to select the query to execute.

You can copy, paste, cut, create, delete, move up, or move down any query using the context menu available on right-click.

Configuring the Operations tab properties

1. Select the Operations tab. Here you can configure the following properties: Show picture

   • Selection - Defines how the default query to execute is selected. There are two options: If set to Default item selection, the selected query in the Queries tab is used. If set to Index variable value, the query is selected according to the value of the Index variable.

   • Index - Defines the register variable used as index to select a query.

   • Execution status - Defines the register variable that holds the operation status of the query.

   • Execution error -Defines the text variable that holds extra information when the variable status indicates a failure.

   • Number of rows returned - Defines the register variable that holds the number of rows returned by the last query execution.

   

Changes in variable values are in both directions:

• If you change the control's query selection, the value of the index variable changes.

• If you change the value of the index variable, the query selection in the control changes to follow it (assuming that the value is within the valid range).

Configuring the Execution tab properties

1. Select Execution tab. Here you can set the execution properties of a Sql read query. It’s possible to trigger it cyclically, on a trigger, by script or with an accelerator key. It’s also possible to inhibit the query execution. The following properties can be configured: Show picture

   • Cyclic - If selected, activates the execution of the selected query cyclically. The execution period can be customized.

   • Triggering bit - If selected, enables the execution of the selected query on value change of a bit variable. If the variable is invalid, query execution is inhibited.

   • On mimic open - If selected, enables the execution of the selected query when the mimic that contains the grid is opened.

   • Allow trigger by script - If selected, enables the use of the SCADA Basic instruction SELECTOR to trigger the query.

   • Enable - If selected, enables the use of a keyboard accelerator key to trigger the query. You can specify the shortcut in the Accelerator field.

   • Inhibiting bit - If selected, enables the use of a bit variable to inhibit the execution of the query if the variable changes to 0 or 1. If the variable is NS, the query is also inhibited.

   

   If a query is in running state, it’s not possible to trigger it again.

Activating Traces messages

The Traces tab is used to enable trace messages from the control. It is useful when diagnosing errors or if you want to track the activity at run-time. Trace messages appear in the Trace window and are logged in the trace file. The following messages can be generated.

• System errors - If selected, logs a trace when an unexpected error such as a stream processing failure occurs.

• Verbose - If selected, logs traces of all activity associated with the control.

• Runtime (Queries) - If selected, logs traces related to query handling.

• Runtime (Triggers) - If selected, logs traces related to query triggering.

• Runtime (Performances) - If selected, logs traces related to query performances.

Using the Sql grid at runtime

This section describes the basic capabilities of the Sql grid at run-time as configured with the properties in the Aspect and General tab.

Resizing a column or row

Rows and columns can be re-sized by clicking and dragging the border between adjacent columns or rows on the header. The new widths and heights are saved when the mimic is saved as long as the property Autosize in the General tab is not selected.

If you want to resize the columns or rows, and then prevent further resizing by the run-time user, the headers must be turned off (number of headers set to 0) when the mimic is saved.

Selecting cells

Cell selection is only possible if Selectable property is selected in the General tab of the properties dialog. The background of the cells changes color to blue when they are selected.

• To select a single cell, click on it. Clicking again will de-select it.

• To select several cells, hold down the ctrl key while clicking on cells in succession.

• To select a block of cells, click and drag the pointer across adjacent cells or select a cell for one end or corner of the block, then hold down the shift key and click on the cell in the opposite end or corner.

• To select all cells in a column, click on the corresponding cell in the column header.

• To select all cells in a row, click on the corresponding cell in the row header.

• To select all cells, use the keyboard shortcut ctrl+A.

Navigating

The Sql grid supports similar control of navigation to that of Microsoft Excel. The most commonly used keys are as follows:

• Tab - Selects the next cell in the same row (or if at the end of the row: the first cell in the next row).

• Shift+Tab - Selects the previous cell in the same row (or if at the start of the row: the last cell in the previous row).

• Arrow keys - Moves the selection according to the key pressed (up, down left and right).

• Home - Selects the first cell in a row.

• End - Selects the last cell in a row.

• Ctrl+Home - Selects the first cell in the grid.

• Ctrl+End - Selects the last cell in the grid.

• Page up - Scrolls the display vertically so that the previous page is displayed. A page is the visible area in Grid; the grid itself is continuous.

• Page down - Scrolls the display vertically so that the next page is displayed.

Sorting cells

To sort the whole grid alphabetically, click on a cell in the top header (a second click reverses the sort order). The sort order is according to the corresponding column's contents.