Using Structured Query Language

Extensibility > Using External Databases > Using Structured Query Language | Extensibility

Using Structured Query Language

You can use Structured Query Language (SQL) functions for direct access to an SQL database, instead of accessing the database as a device. Using direct database access can provide greater flexibility. The SQL functions provide access to SQL databases through any ODBC-compatible database driver, for example, MS Access, FoxPro or Paradox.

NOTICE

PERFORMANCE DEGRADATION

To avoid system and database performance degradation, please consider the following:

Build queries that require an acceptable use of system resources (memory, CPU, threads).
Limit scope of queries with parameter-driven WHERE clauses.
Avoid using SELECT * to return all columns.
Do not perform queries with null values for date time parameters.
Try to balance load between relational database management systems (RDBM) and SCADA, for example, consider sorting data on the client side rather than the server side.
Consider using stored procedures to minimize the number of round trips to the server.

Failure to follow these instructions can result in equipment damage.

SQL functions provide absolute control and flexibility over connections and queries. Querying a database via the Device system is slower than constructing a query via a SQL function due to the generic nature of a device.

ActiveX Data Objects

Citect SCADA 7.30 introduced the use of the ADO.NET library of SQL functions, which can be programmed using any of .NET managed languages, and is supported and further developed by its manufacturer. Further, the ADO.NET approach enables you to have multiple recordsets per connection, and multiple queries per recordset. Previous versions restricted you to 1 recordset per connection and 1 query per connection. You can also delegates connections, disconnections and query executions to separate application threads, meaning that SQL queries need no longer be blocking actions.

The technology facilitates connections to databases from various vendors and obtains data sets by executing either ANSI SQL commands or any database specific dialects of SQL (if a dedicated provider is used) such as T-SQL or PL/SQL.

Connections can be established to databases which:

Have an ODBC driver
Have an OleDB provider
Use MS SQL Server.

Specifically, the Cicode SQL DB interface can be used to connect to either Historian or Historical Alarm databases as long as they use databases fulfilling one of the above conditions and store their data in types that can be read by the Cicode SQL interface.