In the PROMOTIC system databases are accessed preferably by the
PmaAdo object (and also by the
PmaDatabase object - but this one is now considered obsolete). These objects contain multitude of methods for creating databases and tables, inserting or deleting records in tables, writing and reading values in records, searching and sorting, SQL statements executions, etc. The functionality of the
PmaAdo and
PmaDatabase objects is bound to purchase of the
PmDB licence.
Some components of the PROMOTIC system (
Trends and
Alarms and Events) also allow to save data into database the system way. The
PmaTrendGroup and
PmaAlarmGroup objects functionality
is not conditioned by purchase of the
PmDB licence.
Common work with database
It is not suitable to enter column names like Time, Date, No, Desc and so on because these words are often keywords of particular database systems and meaningless errors are often raised on an attempt to use such words.
The maximum number of columns in the table is usually limited by a constant that is often changed when converting between several versions (it is usually increased when converting to a higher version of the database).
The maximum length of column name in the table is usually limited by constant.
Access to databases by using the ADO technology
The
ADO technology by the
Microsoft company allows to access database by available
ADO Providers provided by the
Microsoft company, by database manufacturers, from third parties, usage of the
ADO Provider for standard
ODBC, etc. The database (server) connection itself is executed by the
ADO Connection object, defined by the
ADO ConnectionString string. The
ADO Connection object allows to execute
SQL statements over thw database. Most SQL statements does not return any data (create/change/delete table, add/change/delete record in the table, etc.). The SQL statement
SELECT returns requested data in the table form as the
ADO Recordset object.
The
ADO technology is the fundamental data storage technology used by PROMOTIC components that produce data and need to be saved as history (
PmaTrendGroup,
PmaAlarmGroup). The
ADO technology is also a basic technology used for working with data in database the user way (
PmaAdo). The important fact is that the connection itself (
ADO Connection) to a specific database (server), defined by the
ADO ConnectionString is identical for all PROMOTIC components.
Database connection parameters: ADO ConnectionString
The
ADO ConnectionString (
http://www.connectionstrings.com) contains all necessary parameters needed by the
ADO Connection object for database connection via the selected
ADO Provider. The typical parameters are represented for example by connection provider (
provider), server address (
server), database name (
database), login name and password (
uid,
pwd), etc. The parameter list and exact syntax is defined by the corresponding
ADO Provider, parameters may differ by individual
ADO Providers. However for the sake of compatibility most providers
ADO Provider understand multiple names of the main parameters simultaneously (e.g. the database name can be represented by
database,
data source,
dbq, etc.).
For example:
"provider=SQLOLEDB;server=.\SQLEXPRESS;database=pm_data;uid=pm_admin;pwd=pmadmin;"
see
ADO ConnectionString
List of supported databases:
For next descriptions of database statements see: