ODBC Client Query Tag

Introduction

The ODBC Client Query Tag represents an SQL command on an external database. The SQL command can be executed:

At a fixed interval.
On demand when requested by a Refresh Tag Command.
Both at a fixed interval and on demand.

Configuration Properties

The ODBC Client Query Tag supports the configuration properties of the Alarm Tag plus these properties:

Property	Type	Version	Description
AlarmSeverity	UINT	1.5	The severity of the alarm raised when an error is reported by the ODBC Driver.
Interval	TIME	1.2	The interval between each query. An interval value of zero (T#0s) disables periodic query execution. See Interval and Offset for information on how timed activities are scheduled.
Offset	TIME	1.2	The offset added to the Interval to get the actual query time. See Interval and Offset for information on how timed activities are scheduled.
Connection	WSTRING	1.2	The connection string used to define the ODBC data source.
Parameter1	WSTRING	3.30	An optional reference to a Data Point Tag. If used, the value of this tag is passed as a dynamic parameter (?) to the SQL statement.
Parameter2	WSTRING	3.30	An optional reference to a Data Point Tag. If used, the value of this tag is passed as a dynamic parameter (?) to the SQL statement.
SQL	WSTRING	1.2	The SQL statement to execute on the ODBC data source. The SQL statement can contain up to two dynamic parameters (?). The values of the dynamic parameters are provided by the Parameter1 and Parameter2 properties.
Password	WSTRING	3.33	An optional password value to add to the connection string. If used, a PWD parameter is appended to the connection string that is sent to the ODBC Driver Manager. Note: The password value is encrypted and not stored as plain text.
UserName	WSTRING	3.33	An optional username to add to the connection string. If used, a UID parameter is appended to the connection string that is sent to the ODBC Driver Manager.

Connection Property

The Connection property tells the ODBC Driver Manager how to connect to the ODBC data source. The Connection Property consists of a set of property names and values separated by semicolons.

To connect to the default instance of Microsoft SQL Server 2008 running on a server called DataStore, you might use this connection string:

Driver={SQL Server Native Client 10.0};Server=DataStore;UID=OdbcUser;PWD=OdbcPassword

Putting the UID and PWD parameters directly in the connection string is a security risk. Anyone who has access to the Connection property can read the username and password. To mitigate this risk, use the UserName and Password properties to store the username and password. For example:

Property	Value
Connection	Driver={SQL Server Native Client 10.0};Server=DataStore
UserName	OdbcUser
Password	OdbcPassword

Note: The value stored in the Password field is encrypted and not stored as plain text.

To work out what to put in a connection string for a specific database:

Consult the documentation for the ODBC driver for your target database.
Try the website https://www.connectionstrings.com/. This is an independent resource dedicated to providing connection string information.
Search existing questions on Stack Overflow. If you cannot find the answer you can always ask your own question.

Parameter1 and Parameter2 Properties

You can use the Parameter1 and Parameter2 properties to pass dynamic parameters (?) to the SQL statement. Parameter1 and Parameter2 are references to Data Point Tag. If used, the value of these tags are used as dynamic parameters to the SQL statement.

If you have an ODBC Client Query Tag with the following properties configured:

Property	Value
Parameter1	.Batch Number
SQL	SELECT Weight,Duration FROM BatchRecords WHERE BatchNo = ?

If the Data Point Tag ".Batch Number" has the value 476, this SQL statement will be executed:

SELECT Weight,Duration
FROM BatchRecords 
WHERE BatchNo = 476

External Database Security

Fernhill SCADA may need to authenticate itself with the external database. There are three methods Fernhill SCADA can use to authenticate itself with an external database. Which method you choose depends on:

The type of database you want to connect to, and
The level of security you want to achieve.

Authentication Method	Comments
Integrated security	You change the Fernhill SCADA service to log on as a user. This method offers good security. However not all databases support this method of authentication. See Fernhill SCADA Server for information about changing the user account used by Fernhill SCADA.
Using a DSN	You configure a username and password as part of a System DSN. This method offers good security. However, not all databases support this method of authentication.
Connection String	You include the UID and PWD keywords in the Connection string. This method is not secure because the connection string is visible to any Fernhill SCADA user. Make sure the database account is restricted to querying only the data you want to read into Fernhill SCADA.

Data Properties

The ODBC Client Query Tag supports the data properties of the Alarm Tag plus these properties:

Property	Type	Description
QueryState	UINT	A code indicating the state of the query. The value 15 shows successful operation. Other values indicate an error. The ErrorMessage property may give additional information.
ErrorMessage	WSTRING	The error message returned from the ODBC data source.
Parameter1Value	STRING	The value of the first dynamic parameter (?) passed to the SQL Command.
Parameter2Value	STRING	The value of the second dynamic parameter (?) passed to the SQL Command.
QueryDuration	TIME	The time taken for the latest query to execute.
QueryRows	UDINT	The number of rows returned in the result set.
QueryColumns	UDINT	The number of columns returned in the result set.
QueryTimer	TimerStatus	The status of the query timer.

The QueryState property can be used to diagnose problems with the ODBC data source:

QueryState	Description
0	Initial state before the driver has started
1	There is an error in the ODBC Client Query Tag configuration
2	There was an error allocating an ODBC connection handle
3	The connection to the ODBC data source failed
4	There was an error allocating an ODBC statement handle
5	There was an error executing the SQL statement
6	There was an error querying the result set column metadata
7	There was an error fetching the result set
8	There was an error in the parameters to the SQL statement
15	The latest query executed successfully

Tag Commands

The ODBC Client Query Tag supports the tag commands of the Alarm Tag, plus these tag commands:

Tag Command	Version	Description
Refresh	1.2	Requests an immediate execution of the SQL statement.

Note: Event triggered data reads (from the Refresh Tag Command) and periodic data reads (defined by the ScanInterval property) are independent. You can use the Refresh Tag Command, or the ScanInterval property, or both to determine when the query is executed.

Relationships to Other Tag Types

The ODBC Client Query Tag builds on the features of these tag types:

Further Information

Interval and Offset

To learn how activities are scheduled using Interval and Offset.

I/O Analog Data Point Tag

To learn about the I/O Analog Data Point Tag that can be associated with the ODBC Query Tag.

I/O Digital Data Point Tag

To learn about the I/O Digital Data Point Tag that can be associated with the ODBC Query Tag.

I/O String Data Point Tag

To learn about the I/O String Data Point Tag that can be associated with the ODBC Query Tag.

I/O Word Data Point Tag

To learn about the I/O Word Data Point Tag that can be associated with the ODBC Query Tag.

I/O Double Word Data Point Tag

To learn about the I/O Double Word Data Point Tag that can be associated with the ODBC Query Tag.

Odbc Client Driver

For an overview of the Odbc Client Driver.

Glossary

For the meaning of terms used in Fernhill SCADA.