Custom Tables - An Overview
The ClearSCADA database consists of a series of pre-configured tables, based on classes (see Tables in the ClearSCADA Guide to the Database). The classes contain fields that you can query to obtain the data or configuration of specific items in the database. You can view the data in List format using one of ClearSCADA's pre-configured Queries Lists or write your own 'custom' List. You can also configure external applications to read or write to specific fields in the tables in order to access or configure the database.
In some circumstances, you might want to add your own 'custom' tables to the ClearSCADA database. ClearSCADA supports the creation of two different kinds of 'custom' table; use whichever suits your needs:
- A 'custom' table that collates, organizes, and summarizes existing data in the database. For example, to make the data from multiple database items easier to query using SQL from, for example, a Crystal Report or List. For this type of 'custom' table, you use a Data Grid or Data Set.
- A 'custom' table in which to store your own data. For example, data extracted from another application and that you wish to access in ClearSCADA. For this type of 'custom' table, you use a Data Table.
Like pre-configured ('standard') tables, you can use the Database Schema to determine the fields and methods that ClearSCADA provides for each type of 'custom' table. Such information is available from the Tables and Grids branch in the list of Creatable Classes in the Database Schema. You will find this information useful if you want to query or populate a 'custom' table programmatically. For information about the fields that have been created for an individual 'custom' table that exists in your database, use the All Classes listing in the Database Schema (the name under which the table appears is the name that you specified for the 'custom' table when you configured that item in the database). For more information about using the Database Schema, see Understanding the Database and see Working with the Database Schema in the ClearSCADA Guide to the Database.
Use the tables below to determine the type of 'custom' table you require. For more information about a particular type of 'custom' table, select the heading link in the Type of 'Custom' Table row. This will take you to the introductory topic about that type of database item.
| Type of 'Custom' Table: | Data Table |
|---|---|
| Intended Use |
Use for storing data, such as the results of custom calculations that are running in Logic programs; the values that are returned by Mimic scripts; or data values from third-party applications. You can then query the data from, for example, a List or Report. |
| Means of Populating the Table |
Intended to be populated automatically by, for example, server automation, Logic, SQL, or ODBC (see Automatically Populate a Data Table). To populate a Data Table from a third-party application that uses ODBC for data transfer, use ClearSCADA's Data Access components for that data transfer (see Install Data Access Components on a Third Party ODBC Client in the ClearSCADA Guide to Client Administration). |
| Associated File Locations |
The Data Table's definition is stored as: Configuration The Data Table's data is stored as: Data Files (one file per Data Table) For ease of use, you need to determine which fields might need indexing (to speed up searches) and whether to retain the Table's records in cache memory (see Preparation). |
| Restrictions and Limitations |
General recommendations: Maximum size of a Data Table: 100MB There is no specific limit to the number of Data Tables per database, but you should consider the Data File Cache Size (see Data Files Settings) and the total size of all of the data files, as these could impact adversely on system performance. You can use the Data File Cache section of the Server Status Tool to obtain statistical information about the data files. |
| Further Information |
Example demonstrating how script might be used to write values to a Data Table: see Server in the ClearSCADA Guide to Scripting. |
| Type of 'Custom' Table: | Data Grid |
|---|---|
| Intended Use |
Use to perform calculations on data from elsewhere in the database, and to collate, organize and summarize the resulting values in a tabular format. You can then query the data from, for example, a List or Report. |
| Means of Populating the Table | You configure an expression to calculate the value in each cell (see Add an Entry to a Data Grid). |
| Associated File Locations |
The Data Grid's definition is stored as: Configuration (Data Grids do not store any data - they are merely used to collate existing data from elsewhere in the database.) |
| Restrictions and Limitations | If you are collating data from the instances of a Group Template, use a Data Set instead. |
| Further Information |
Data Grid Expressions (used for populating the table cells). Example demonstrating how the Server.Query Mimic script property might be used to query the data in a Data Grid: see Server.Query in the ClearSCADA Guide to Scripting. |
| Type of 'Custom' Table: | Data Set (and associated Data Set Rows) |
|---|---|
| Intended Use |
The same intended use as Data Grids, but specifically for use with a 'modular' database; particularly when collating data from the instances of a Group Template. You use Data Set Rows to populate the cells of a Data Set. |
| Means of Populating the Table |
You configure an expression to calculate the value in each cell (see Define the Content of a Cell in a Data Set Row). When corresponding Data Set Rows are used in Group Templates, records are automatically added or removed from the Data Set's table as instances of the Group Template are created or deleted. |
| Associated File Locations |
The Data Set's definition is stored as: Configuration (Data Sets do not store any data - they are merely used to collate existing data from elsewhere in the database.) |
| Restrictions and Limitations |
Due to their nature, you cannot include a Data Set in a Group Template (although you can include a Data Set Row in a Group Template). You usually only add one Data Set Row per 'normal' Group or Group Template (for a given Data Set). |
| Further Information |
Data Set Expressions (used for populating the table cells). |
Further Information
Logic programs: see the ClearSCADA Guide to Logic.
Mimic scripts: see the ClearSCADA Guide to Scripting.
Server automation: see the ClearSCADA Guide to Server Automation.
Using ODBC to access the ClearSCADA database: see the ClearSCADA Guide to the ODBC Driver.
Using expressions: see the ClearSCADA Guide to Expressions.
The locations of the folders that determine where 'custom' table content is stored: see Locations of the Database Folders in the ClearSCADA Guide to Server Administration.