ODBC Client Query Tag

Help Contents

Introduction

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

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 user name 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 is an ODBC connection string that tells the ODBC Driver Manager how to connect to the ODBC data source. The ODBC connection string 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 into the connection string is a security risk. Anyone who has access to the Connection property can read the user name and password. To mitigate this risk, use the UserName and Password properties to store the user name and password. For example:

PropertyValue
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:

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:

PropertyValue
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

Depending on the external database you want to connect to, 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 MethodComments
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 user name and password as part of a System DSN (Data Source Name). 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.

Dynamic Properties

The ODBC Client Query Tag supports the dynamic 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 Statement.
Parameter2Value STRING The value of the second dynamic parameter (?) passed to the SQL Statement.
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:

QueryStateDescription
0Initial state before the driver has started
1There is an error in the ODBC Client Query Tag configuration
2There was an error allocating an ODBC connection handle
3The connection to the ODBC data source failed
4There was an error allocating an ODBC statement handle
5There was an error executing the SQL statement
6There was an error querying the result set column metadata
7There was an error fetching the result set
8There was an error in the parameters to the SQL statement
15The 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.