Using CitectSCADA > Exchanging Data with Other Applications > Using Structured Query Language

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, Paradox, etc.

See Also

Connecting to an SQL database
Executing SQL commands
Using a transaction
Expressing dates and times in SQL

Limitations:

The SQL library used inCitectSCADA has a number of limitation and these are documented in our Knowledge Base (article Q4014). This library was not designed to handle large scale SQL data transactions. For large volumes of data it is recommended that CitectSCADA Reports (CitectHistorian) be used.

If you intend to use the embedded SQL library in CitectSCADA, consider the following:

  1. Use "Simple" model for a SQL database to limit transaction information logging to transaction log file.
  2. Use the fixed size of transaction log to restrict the log file growing.
  3. Back up the database regularly to keep the transaction log size under control. When SQL Server finishes backing up a database or its transaction log, it automatically truncates the inactive portion of the transaction log. If the transaction log file is full, your database transactions will cease.
  4. Keep working tables as small as possible. When CitectSCADA SQL adds or appends a new row to data table, it uses SELECT * first to get column information. If there are hundred and thousand of records in the table, this action will certainly hinder the performance and may cause lock ups as these SQL Cicode functions are block functions.
  5. Use SQL Server trigger to remove records from the working tables to the permanent tables. In this way, sizes of the working tables that are directly interfaced with Citect are not growing unrestricted.
  6. Do NOT run SQL Cicode functions on a critical CitectSCADA machine. As SQL Cicode functions are block functions and running in the main thread, #COM or trend miss samples are likely to occur when SQL functions are executed on IO or trend. Similarly, an alarm may miss a scan or a report may miss a trigger if the functions are running on an alarm or report server.