AVEVA™ SQLDataGrid Symbol User Guide

[Link]
 © 2022 AVEVA Group plc and its subsidiaries. All rights reserved.
No part of this documentation shall be reproduced, stored in a retrieval system, or transmitted by any means,
electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of AVEVA.
No liability is assumed with respect to the use of the information contained herein.
Although precaution has been taken in the preparation of this documentation, AVEVA assumes no responsibility
for errors or omissions. The information in this documentation is subject to change without notice and does not
represent a commitment on the part of AVEVA. The software described in this documentation is furnished under
a license agreement. This software may be used or copied only in accordance with the terms of such license
agreement.
ArchestrA, Avantis, Citect, DYNSIM, eDNA, EYESIM, InBatch, InduSoft, InStep, IntelaTrac, InTouch, OASyS,
PIPEPHASE, PRiSM, PRO/II, PROVISION, ROMeo, SIM4ME, SimCentral, SimSci, Skelta, SmartGlance, Spiral
Software, WindowMaker, WindowViewer, and Wonderware are trademarks of AVEVA and/or its subsidiaries. An
extensive listing of AVEVA trademarks can be found at: [Link] All other brands may be
trademarks of their respective owners.
Publication date: Wednesday, July 13, 2022
Publication ID: 953413
Contact Information
AVEVA Group plc
High Cross
Madingley Road
Cambridge
CB3 0HB. UK
[Link]
For information on how to contact sales and customer training, see [Link]
For information on how to contact technical support, see [Link]
To access the AVEVA Knowledge and Support center, visit [Link]

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 2
Contents

Chapter 1 Configuring the SQLDataGrid Symbol. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

About Configuring the SQLDataGrid Symbol. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Securely Accessing the SQL Data Grid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Using the SQLDataGrid Symbol. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Configuring Authentication Modes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Windows Authentication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Configuring the Database Connection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Configuring SQLDataGrid Symbol Custom Properties. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Using Scripts with the SQLDataGrid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Associating Grid Control Actions to InTouch Scripts and Animations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Accessing Data from a Selected Row in the SQLDataGrid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Chapter 2 Working with the SQLDataGrid in Run Time. . . . . . . . . . . . . . . . . . . . . . . . 11

About Working with the SQLDataGrid in Run Time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Viewing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Populating the Table, View or Query List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Solving Hide and Show Window Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Moving Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Grouping Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Viewing Aggregate Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Using Progressive Column Filtering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Discarding Changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Viewing Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Writing Changes to the Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Views and Updates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Database Connection Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Connection Sharing and Pooling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Appendix A Custom Properties for the SQLDataGrid Symbol. . . . . . . . . . . . . . . . . . . 22

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 3
Chapter 1

Configuring the SQLDataGrid Symbol

About Configuring the SQLDataGrid Symbol

The SQLDataGrid is a symbol that enables you to interact with data stored in SQL databases. You can use the
SQLDataGrid symbol in an InTouch application that you create in a published or managed mode.
The following graphic shows the main components associated with the SQLDataGrid.

You can perform the following activities with the SQLDataGrid symbol:
• Insert or modify data in selected database tables and views.
• Delete rows from a database table (select the row and press the Delete key).
• Select data from existing tables and views.
• Run a custom SQL query.
This section provides the following information:
• The minimum configuration required by the SQLDataGrid symbol to retrieve data from a SQL database.
• The set of custom properties that must be configured to authenticate users and connect to the database.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 4
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 1 – Configuring the SQLDataGrid Symbol

• A list of the custom properties that define the parameters associated with a specific database and query to
be retrieved.

Securely Accessing the SQL Data Grid

The SQL Data Grid is a powerful tool which allows InTouch WindowViewer to connect to various SQL databases.
This tool allows for reading as well as modifying SQL databases, including their data, tables, table schemas, and
stored procedures. Given this, it is important that the SQL Data Grid and any SQL database(s) it will access are
configured securely.
Security Best Practices
1. Grant each user the least privileges required within each SQL database
Grant users in the SQL database the least privileges required to accomplish their job. This can include limiting
their access to specific databases as well as preventing modification to the database. The user should only
have the privilege needed to perform their required actions. In the SQL Data Grid, the user will only be able
to perform actions that their role allows them to. For SQL authentication and Windows impersonation, this is
the user whose credentials have been provided to the SQL Data Grid. For Windows integrated
authentication, this is the currently logged-on user.
2. Consider granting users read-only access within each SQL database
Grant users read-only access in the SQL database when possible. This will prevent unwanted modification of
the database. The SQL Data Grid can operate with a user who has read-only privilege and will only allow the
user to retrieve and view the data.
3. Configure SQL Data Grid to use Windows Integrated Authentication
Configuring the SQL Data Grid to use Windows integrated authentication helps to secure your application.
Using SQL authentication and Windows impersonation require the storage of user credentials and increases
the attack surface of the application. With Windows Integrated Security, the SQL Data Grid will connect to
the SQL database using the context of the currently logged on Windows OS user.
4. Be aware of what SQL Data Grid can do
As an application developer/maintainer, it’s your responsibility to be fully aware that the SQL Data Grid is not
a read-only control, but instead it allows users to execute full SQL statements on the database, including
UPDATE, CREATE, DROP, etc. There is a potential risk involved with using the SQL Data Grid if it, or the SQL
database it is accessing are not configured securely.

Using the SQLDataGrid Symbol

The SQLDataGrid symbol appears in the Graphic Toolbox of the System Platform IDE. Embed the symbol in an
existing window or a new window. When you double-click the symbol, the Edit Custom Properties dialog box
appears.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 5
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 1 – Configuring the SQLDataGrid Symbol

For detailed information on working with symbols, see the ArchestrA and InTouch Integration Guide.

Configuring Authentication Modes

This section describes how to use the custom properties of the SQLDataGrid symbol to authenticate a user to the
database. You can assign static values to these custom properties or link them to attributes or tag names in your
application.
Note: The value fields are not case sensitive.
To configure an authentication mode for the SQLDataGrid symbol
1. In the Custom Properties pane, select Authentication.
2. In the Default Value box, type the name of a valid security type. Acceptable values are:
• Windows
• SQL Server
A more detailed description of these authentication modes follows this procedure.
3. In the Custom Properties pane, select ConfiguredUser.
4. In the Default Value box, type a user name.
5. In the Custom Properties pane, select ConfiguredPassword.
6. In the Default Value box, type the password associated with the user name.

Windows Authentication
If you use Windows as a value for the Authentication custom property and do not designate custom properties
for a domain, user, or password, the SQLDataGrid symbol uses the credentials of the user who is currently logged
on to the node running InTouch.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 6
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 1 – Configuring the SQLDataGrid Symbol

If you select Windows as a value for the Authentication custom property and designate custom properties for a
domain, user, and password, the SQLDataGrid symbol uses the specified Windows domain name, user name, and
password to connect to the SQL Server database.

SQL Server
This authentication mode uses the specified SQL Server user name and password to connect to the SQL Server
database.

Configuring the Database Connection

This section describes the procedure to configure the custom properties required to establish a connection to a
specific database and its associated data. You can assign static values to these custom properties or link them to
attributes or tag names in your application.
Note: The value fields are not case sensitive.
If you are using a non-default port for the SQL Server database, see the SQLData Object help file for
configuration information.
To configure the database connection
1. In the Custom Properties pane, select ConfiguredServer.
2. In the Default Value box, type the network name of the computer hosting the database.
3. In the Custom Properties pane, select ConfiguredDatabase.
4. In the Default Value box, type the name of the target database.
5. In the Custom Properties pane, select SQLQuery.
6. In the Default Value box, type the name of the target table, view or query.
To populate the SQLDataGrid, click Runtime, and then click Retrieve.

Configuring SQLDataGrid Symbol Custom Properties

This section lists custom properties in functional groups. For a detailed description of each custom property, see
Appendix A, Custom Properties for the SQLDataGrid Symbol.
The default run-time window shows the complete symbol. However, you can hide the interface controls and
display only the grid.
You can use custom properties to display additional information about the status of the query, the dataset, or to
provide interactive buttons that initiate specific actions
In addition to the table, view, and query input, you can specify additional parameters by using custom properties
and control them with scripting.
The following table lists the various custom properties you can insert in your scripts by functional groups.
Authentication Authenticate users to databases
Authentication
ConfiguredDomain
ConfiguredPassword

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 7
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 1 – Configuring the SQLDataGrid Symbol

ConfiguredUser
Database Connectivity Designate database connectivity functions
AutoRefresh
AutoRefreshDuration
CmdCancel
CmdRefresh
CmdRetrieve
CmdTestConnection
CmdWrite
ComboBoxListString
ConfiguredDatabase
ConfiguredReadOnly
ConfiguredServer
RowCount
RowLimit
SelectedRowNumber
SQLQuery
SQLQueryCurrent
SQLQueryPassThrough
SQLQueryPending
Diagnostic Perform diagnostic functions
CmdLogDiagnostics
CmdResetDiagnostics
ErrorPanelDisable
StatusDesc
StatusError
StatusIsError
Appearance Modify the appearance of the run-time SQLDataGrid that contains
data from the database
ButtonPanelHide
CmdPageFirst

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 8
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 1 – Configuring the SQLDataGrid Symbol

CmdPageLast
CmdPageNext
CmdPagePrev
ColumnAggregateEnable
ColumnFiltering
ColumnGroupingEnable
ComboBoxPanelHide
FontBold
FontName
FontSize
SupportThemes
SuppressAuthenticationWarning
WriteButtonHide

Using Scripts with the SQLDataGrid

When you use the SQLDataGridUserCtl to create a new symbol, you can script all the properties and events of
the control.

Associating Grid Control Actions to InTouch Scripts and Animations

You can hide the buttons of the SQLDataGrid symbol and trigger data retrieval from outside the grid interface.
For example, you can configure a button to generate a dataset from an InTouch application.
To do this, use the CmdRetrieve property and associate it to an InTouch tag. The tag is linked to the InTouch
button.
To configure dataset retrieval from InTouch
1. In WindowMaker, open the InTouch window.
2. Double-click the SQLDataGrid symbol. The Edit Custom Property dialog box appears.
3. In the Custom Properties panel, select the ButtonPanelHide property.
4. In the Default Value field, type True.
5. Select the CmdRetrieve property.
6. In the Default Value field, select an InTouch tag by one of the following actions:
a. Type a tag name. If the tag does not exist, you are prompted to define a new tag.
b. Click the ellipsis to open the Tagname Dictionary and select a tag.
For this example, type CmdRetrieve in the field.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 9
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 1 – Configuring the SQLDataGrid Symbol

7. Click OK and define the tag as a Memory Discrete tag.

8. When you finish defining the tag, close the Tagname Dictionary and click OK on the Custom Properties
panel.
9. Create a button on your InTouch window. For this example, its caption is Retrieve.
10. Link the CmdRetrieve tag to the button.
11. Switch to run-time mode and click Retrieve.

Accessing Data from a Selected Row in the SQLDataGrid

You can use the SQLDataGrid symbol to read the content of a selected row and associate it to attributes outside
the symbol. You can configure scripts to read the data from a selected row and programmatically associate that
data to tags in an InTouch application or to attributes in the Galaxy.
The following script example shows how to retrieve data from a selected row. Notice that the SelectedRowData
property is available in the SQLDataGrid client control and not in the SQLDataGrid symbol.
dim rd1 as [Link];
rd1 = [Link];
if(rd1 <> null) then
InTouch:Text9 = [Link][4].ToString();
'This assigns the 4th column value for the selected row, to the InTouch "Text9" tag.
endif;

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 10
Chapter 2

Working with the SQLDataGrid in Run

Time

About Working with the SQLDataGrid in Run Time

During run time, you can select the database table, view, or query that will populate the SQLDataGrid symbol
and change the appearance of the data.
You can use the run-time application to perform the following tasks:
• Move columns
• Group columns
• Aggregate column values
• Filter columns
• Sort columns
If you have the appropriate permissions, you can also make the following changes to the database:
• Change the order of columns in an existing row.
• Insert a row into a table.
• Update a row in a table.
• Delete a row from a table.
Note: You cannot update or delete a row in a table unless that table has a primary key defined. You can add
new rows to a table without a primary key as long as that is the only action you are performing on the table.

Viewing Data
You can use the run-time version of the SQLDataGrid symbol to view data in the following formats:
• Data from a specific table
• Data from a specific view
• Data that is retrieved by a SQL query
You can resize the column width of any table, view, or query result.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 11
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

To view specific data from a SQL query

1. In the Table, View or Query box, type a SQL query.
2. Click Retrieve. The appropriate dataset appears.

Populating the Table, View or Query List

You can also populate the Table, View or Query box with items that you frequently use, so that you do not
repeatedly have to type entries.
To populate the Table, View or Query box
1. In the Edit Custom Properties dialog box, click ComboBoxListString.
2. In the Default Value box, type any of the following items, separated by semicolons (;):
• Multiple table names
• Multiple SQL statements
• A combination of table names and SQL statements
3. Click OK.
4. When you go to run time, click the arrow to the right of the Table, View or Query box. Each item that you
entered is displayed on a separate line.

Solving Hide and Show Window Issues

If you hide the InTouch window that contains the SQLDataGrid and then show it again, the last data values
contained in the window might not appear. Use the following procedure to ensure that the data values always
appear.
To Ensure that Grid Values Appear When You Hide and Show the InTouch WIndow
1. In WindowMaker, click Special > Configure > WindowViewer.
2. Under WindowViewer Memory, clear the check box Always load windows from disk.

Moving Columns
You can rearrange the order of the columns to meet your viewing needs.
To move columns
1. Click the heading of the column that you want to move.
2. Holding your mouse key down, drag the column heading to another position within the table.

Grouping Columns
The group-by function lets you sort data at more specific levels. This example shows a view that groups customer
names and their business addresses.
Note: The ColumnGroupingEnable custom property must be True before you can use this function.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 12
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

To use the Group-by function

1. In the Table, View or Query box, type the name of a table or view or click the arrow to display a list of pre-
determined items. For details about pre-determined items, see Viewing Data.
2. Click Group-by. The Drag a column header area appears.

3. Drag a column heading to the upper area.

4. To view the data groups, press Enter to expand any of the StateProvince items.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 13
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

5. To see a more specific sorting of the data, drag another column heading to the upper sorting area.

6. If you want to sort the data at an even finer level, you can repeat steps 3 and 4 and drag another column
heading to the sort area.
7. If you want to remove one of the column headings from the sorting area, drag it down to the data area.

8. To return the original dataset, click Group-by.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 14
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

Viewing Aggregate Values

You can perform a numeric operation on data within a single column of the table by clicking the Aggregate
button.
Note: Before you can use this function, the ColumnAggregateEnable custom property must be True.
You can perform the following functions on column data. Not all operations may be available for a column
because of the data type.
• Average: Calculates the average number of items in the column.
• Count: Lists the number of rows in the column.
• Maximum: Lists the highest number in the column.
• Minimum: Lists the lowest number in the column.
• Sum: Adds the numbers in each row and provides a total.
To view aggregate values
1. Click Aggregate. The summation sign (S) appears at the head of each column.
2. Click the summation sign at the top of the OrderQty column. The Select Summaries dialog box appears.

3. Select Average and click OK. The value appears just below the Grand Summaries row. You can re-size the
column width to view the entire number This following figure shows the average value for OrderQty.

4. To hide the Grand Summaries row follow these steps:

a. Click the summation sign.
b. Clear the Average check box in the Select Summaries dialog box
c. Click OK.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 15
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

5. To hide the summation signs, click Aggregate.

Using Progressive Column Filtering

You can configure the SQLDataGrid to support progressive column filtering. Type the first letters of your search,
and the matching values appear in the grid.
To configure and use progressive filtering
1. Switch your SQLDataGrid window to WindowMaker mode.
2. Double-click the symbol and select the custom property ColumnFiltering.
3. Set the ColumnFiltering default value to True.

4. Click OK and run the InTouch application.

5. To return a dataset, click Retrieve. In this example, the dataset contains a table view of customer names and
addresses.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 16
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

6. Move your cursor over the field below the LastName column heading. A list arrow appears.
7. You can filter the values by any of the following actions:
• Click the list arrow and type the first letter of the value If you type additional letters, your sort is more
precise.

• Click the list arrow and select an item from the list.

• If you select the (Custom) item from the list, you can further refine your search in the Custom dialog box.
This example shows a search for two different area codes.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 17
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

8. To perform a different search, clear your search criteria by clicking the cancel icon at the left of the filtering
row.

Discarding Changes
If you need to discard the changes made to grid values, click Retrieve. This action reloads the table values from
the database and discards any changes. You can also initiate the action by using a script that sets the
CmdRetrieve property to True.

Viewing Errors
You can manually display or close the Status panel in the lower part of the grid display area. The status panel can
also automatically appear when an error occurs. However, after the panel appears, it remains on view.
If a command object is successful, the message "Success" appears in the Status panel. If the panel is closed,
"Success" messages do not appear for successful command objects.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 18
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

To close the panel, click the double-arrow icon at the right of the panel. To open the panel on demand, right-click
and select Show Status.

Note: The error panel display does not change the overall area occupied by the grid symbol.

Writing Changes to the Database

You can write changes to the database if the following conditions are met:
• The database query (or table) can be updated.
• The ConfiguredReadOnly custom property value is False.
• SQL Server security allows the security mode configured at the grid to perform an update.
• Changes made in the grid are consistent with the database schema.
Changes made in the grid are written to the database using the Write command. Changes are handled as a single
database transaction. If any change fails, the entire set of changes is rolled back.
If you have sufficient privileges, you can change a value and click Write. You can also use a script to set the
CmdWrite property. This action causes the grid contents to be evaluated for changes and for the changes to be
written to the database.
Tip: After you modify cells in the grid and write them back to the database, you should also verify that the data is
correctly written. In certain cases, such as a database table with an automatically incrementing primary key or
interdependent cells, the grid does not show the true contents of the database table after you write back to it.
Also, no error message is shown. It is recommended that you execute a retrieve or auto refresh command after
writing to the database to make sure it is showing the current data.

Views and Updates

Some views cannot be updated for the following reasons:
• The views contain table joins on keys other than primary keys.
• The views include security settings that prohibit updates.
If the write process fails, you can view the status in the Error panel. You can also monitor the StatusDesc, and
StatusError properties by using text fields on the InTouch window for run-time display.
To make a change to data in the data grid
1. Ensure that the ConfiguredReadOnly custom property value is False.
2. Click Runtime.
3. Enter a SQL statement or the name of a database table or view.
4. Click Retrieve. This example shows data from the customer table in AdventureWorks. You will modify a
phone number.
5. Select the phone number that you must modify. The area becomes editable.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 19
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

6. Change the last four digits of the phone number to 0122.

7. Click an area outside the edit area. The Write button becomes enabled.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 20
 AVEVA™ SQLDataGrid Symbol User Guide
Chapter 2 – Working with the SQLDataGrid in Run Time

8. Click Write.
9. To verify the change, click Retrieve.

Database Connection Management

An important feature of the SQLDataGrid is the ability to optimize the management of multiple simultaneous
connections to the database. This section describes how the infrastructure handles the database connection
pool and sharing for the SQLDataGrid.

Connection Sharing and Pooling

Multiple instances of the SQLDataGrid configured with identical connection strings use a shared connection pool
and SQL command queue when they are active in the same instances of the InTouch WindowViewer. To limit the
size of the shared pool, you can use the connection pool property MaxConnections. For configuration options,
see the MaxConnections.
Physical connections to the SQL database are opened based on command requests and are closed when no
requests are pending. All SQL commands are queued and processed based on availability of connections and
processing time.
The SQLDataGrid asynchronously processes all SQL commands. Asynchronous command processing ensures
availability of interaction with the InTouch WindowViewer during time-consuming command processing.
Any database connection that is established in a script within a symbol using the SQLData Script Library also
takes advantage of connection pooling. Similar connection strings from either the SQLDataGrid or the script in
the same InTouchView application share a common pool.
Note: Connection sharing and connection pooling require that the same security mode and user credentials be
used for all SQLDataGrid symbols within the InTouchView application that shares the connection or the pool.

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 21
Appendix A

Custom Properties for the SQLDataGrid

Symbol

The following table lists all available Custom Properties. Data types are shown in the Custom Properties panel
and are not included in this table.
Property Name Description

Authentication Designates whether database user authentication is

SQL Server or Windows.
Writable at Run Time: Yes
Default: Windows
AutoRefresh Automatically refreshes data appearing in the
SQLDataGrid. When True, the query that repopulates
the SQLDataGrid runs after the AutoRefreshDuration
time elapses.
Writable at Run Time: Yes
Default: False
AutoRefreshDuration Designates the requested time interval in seconds for
AutoRefresh.
The actual refresh rate can be slower than the
specified rate.
Writable at Run Time: Yes
Default: 60
ButtonPanelHide Hides grid buttons when the value is True.
Writable at Run Time: Yes
Default: False
CmdCancel Cancels the last database query.
Writable at Run Time: Yes
Default: False

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 22
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

CmdClear Clears the contents of the SQLDataGrid.

Writable at Run Time: Yes
Default: False
CmdCopy When True, copies the current selection to the
clipboard.
Writable at Run Time: Yes
Default: False
CmdLogDiagnostics Writes the current DCM diagnostic values to the logger.
Writable at Run Time: Yes
Default: False

CmdPageFirst Moves the SQLDataGrid display to the first row.

Writable at Run Time: Yes
Default: False
CmdPageLast Moves the SQLDataGrid display to the last row.
Writable at Run Time: Yes
Default: False
CmdPageNext Moves the SQLDataGrid display down one page.
Writable at Run Time: Yes
Default: False
CmdPagePrev Moves the SQLDataGrid display up one page.
Writable at Run Time: Yes
Default: False
CmdPaste When True, pastes the current text contained on the
clipboard to the grid.
Writable at Run Time: Yes
Default: False
CmdRefresh Refreshes the current SQL query data. Any pending
changes are discarded.
Writable at Run Time: Yes
Default: False

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 23
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

CmdResetDiagnostics Resets the current DCM diagnostic values and clears

the message and event counters.
Writable at Run Time: Yes
Default: False
CmdRetrieve Loads the selected table, view, or query to the
SQLDataGrid. Any changes to a previous dataset are
discarded.
Writable at Run Time: Yes
Default: False
CmdTestConnection Tests the database connection for the specified server,
database, security type; and if applicable, user name
and password.
The result is placed in the Error panel.
Writable at Run Time: Yes
Default: False
CmdWrite Submits changes made in the SQLDataGrid to the
database.
Writable at Run Time: Yes
Default: False
ColumnAggregateEnable Specifies whether the column aggregate feature is
exposed by default.
Writable at Run Time: Yes
Default: True.
ColumnFiltering Enables column filtering and provides a row with the
progressive filtering entry field.
Writable at Run Time: Yes
Default: False
ColumnGroupingEnable Specifies whether the column grouping feature of the
SQLDataGrid is exposed by default.
Writable at Run Time: Yes
Default: False

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 24
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

ComboBoxListReadOnly If False, you can make changes to the edit box above
the combo list box.
Writable at Run Time: Yes
Default: False
ComboBoxListString Enables a list of table or view names, separated by
semicolons to be viewable in the Runtime combo box.
Writable at Run Time: Yes
Default: Blank

ComboBoxPanelHide Hides the combo box and panel.

Writable at Run Time: Yes
Default: False
CommandTimeout The number of seconds that the SQLDataGrid waits for
a pending command to run before timing out.
Writable at Run Time: Yes
Default: 30
ConfiguredDatabase The name of the database that connects to the
SQLDataGrid.
Writable at Run Time: Yes
Default: Blank
ConfiguredDomain The domain to use with impersonation if the
authentication mode is Windows.
Writable at Run Time: Yes
Default: Blank
ConfiguredPassword SQL Server domain if the security type is Windows.
Writable at Run Time: Yes
Default: ********
ConfiguredReadOnly Prevents the SQLDataGrid from making changes to the
database.
Writable at Run Time: Yes
Default: True

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 25
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

ConfiguredServer The name of the computer hosting the database.

Writable at Run Time: Yes
Default: Blank
ConfiguredUser The SQL Server user name if security type is SQL. This
property is optional when security type is Windows.
Writable at Run Time: Yes
Default: Blank

ConnectionTimeout The number of seconds that the SQLDataGrid tries to

connect to a database before timing out.
Writable at Run Time: Yes
Default: 15
ContextMenuEnable When True and you right-click the grid, the context
menu appears.
When False and you right-click the grid, the context
menu does not appear.
Writable at Run Time: Yes
Default: True
ErrorPanelDisable Disables the error message display.
Writable at Run Time: Yes
Default: False
ErrorPanelShow When True, the error panel is visible.
When False, the error panel is hidden.
Writable at Run Time: Yes
Default: False
FeatureButtonsHide Hides the Aggregate and Group-by buttons.
Writable at Run Time: Yes
Default: False
FontBold Specifies whether to display the SQLDataGrid font in
bold text.
Writable at Run Time: Yes
Default: False

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 26
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

FontName Specifies the name of the font.

Writable at Run Time: Yes
Default: Arial
FontSize Specifies the point size of the font.
Writable at Run Time: Yes
Default: 9.0
InstanceName A name that you choose to identify a specific
SQLDataGrid instance. The name appears in the
ConnectionName diagnostic.
Writable at Run Time: Yes
Default: Blank
MaxConnections The number of active connections allowed in the
connection pool for a given connection string.
Writable at Run Time: Yes
Default: 100
RowCount The row count returned in the last successful data
request.
Writable at Run Time: No
Default: 0
RowLimit Modifies the query to return a specified number of
records.
To prevent the database query from being modified,
set the SQLQueryPassThrough custom property to
True.
Writable at Run Time: Yes
Default: 20,000
RowsChanged If True, one or more rows have been changed in the
grid (added, odified, or deleted).
If False, no rows were changed in the grid.
Writable at Run Time: No
Default: False
SelectedRowNumber The number of the selected grid row.
Writable at Run Time: Yes

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 27
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

Default: 0

SQLQuery Specifies the SQL query, table, or view data to be

retrieved to the SQLDataGrid.
If SQLQueryPassThrough is True, you must add the
SELECT statement along with the table or view name.
For example, SELECT * FROM [Link], rather
than [Link].
Writable at Run Time: Yes
Default: Blank
SQLQueryCurrent Specifies the SQL command that was run to show the
current data in the SQLDataGrid.
Writable at Run Time: No
Default: Blank
SQLQueryPassThrough If True, enables the SQL statement specified in the
SQLQuery custom property to be passed directly to the
database without modification.
When this property is True and the SQLQuery custom
property is set only to a table name or view, the grid
does not insert "SELECT * FROM" in front of the table
or view name.
Additionally, if this property is True, the top "n" is not
inserted into a SELECT statement (where "n" is the
value of the RowLimit custom property).
Writable at Run Time: Yes
Default: False
SQLQueryPending Indicates a pending query.
Writable at Run Time: No
Default: False
StatusDesc Specifies the error description from the last query. If
no error exists, the string is empty.
Writable at Run Time: No
Default: Blank

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 28
 AVEVA™ SQLDataGrid Symbol User Guide
Appendix A – Custom Properties for the SQLDataGrid Symbol

Property Name Description

StatusError Specifies the error number from the last request.

Writable at Run Time: No
Default: 0
StatusIsError Indicates whether an error occurred in the last query.
True = Error
False = no error
Writable at Run Time: No
Default: False

SupportThemes Specifies the SQLDataGrid window is formatted with

Windows XP or Vista visual themes. You should always
set the value of this custom property to True. This
custom property is not yet supported.
Writable at Run Time: No
Default: True
SuppressAuthenticationWarning If user specifies any values in the ConfiguredUser and
ConfiguredPassword properties, then a warning
textbox is displayed in runtime. The warning informs
the user of the possible security issue of storing the
database user credentials locally. If True, hides the
textbox regardless of configuration.
Writable at Run Time: No
Default: True
WriteButtonHide Hides the Write button used to send changes to the
database.
Writable at Run Time: Yes
Default: False

© 2022 AVEVA Group plc and its subsidiaries. All rights reserved. Page 29