Promotic
WikipediaLinkedInYoutubeTwitterFacebook

Description of handling PmaDatabase object referencing to existing table

 

1. Connect and open database table

By a database connection the database specified in the "Database (file, folder, data source)" configurator is connected. It is possible to connect a database on starting the application (the "On start" configurator is set to "Connect database and open table (the same as the Open method)" or "Connect database and do NOT open table (the same as the Connect method)") or any time later by calling the PmaDatabase.Connect method. This operation is quicker that opening a table.

By opening a table the above mentioned database connection is carried out and moreover the table specified in the "Table (file)" configurator is opened. It is possible to open a table after starting the application (the "On start" configurator is set by the 'connect database and open table' option) or any time later by calling the PmaDatabase.Open method. This operation is slower that the database connection. The table can be opened in the modes "for read and write", "read only", etc., see for example the "Table open mode" configurator (this influences the opening speed).

By configurators of the "Database" tab it can be defined a table and method of its opening. The "Open using the SQL statement SELECT" configurator specifies, if the table is opened according to the specified SQL statement of the SELECT type, otherwise it is opened directly according to the "Table (file)" configurator.

 
The type of entering SQL statements depends on setting the "Technology" configurator:
- if is set to Access through ODBC, then possible SQL statements are defined in the SQL syntax of the database which the PmaDatabase object is connected to.
- if is set to Direct access to table - DAO, then it is necessary to enter SQL statements in the Microsoft syntax supported in the PROMOTIC system; the differences are minimal - see further.
 

Caution!!! - supported SQL implementation differs from the ANSI SQL in the Like statement syntax as follows:

ANSI SQL supported SQL implementation
_ ?
% *
 

and in the WHERE statement syntax for entering of date and time as follows:

ANSI SQL supported SQL implementation
WHERE ... '25 10 2002 12:22:30' ... WHERE ... #10 25 2002 12:22:30# ...
Example1:
(Microsoft syntax in the PROMOTIC system) selection of all columns in the table named "Table" where values of the "column1" column are in the range from 10 to 100.
SELECT * FROM Table WHERE column1 BETWEEN 10 AND 100
Example2:
(Microsoft syntax in the PROMOTIC system) selection of the "column1" and "column2" columns in the table named "Table" where values of the "column1" column are greater than 10.
SELECT column1, column2 FROM Table WHERE column1 > 10
Example3:
(Microsoft syntax in the PROMOTIC system) selection of all columns in the table named "Table" where values of the "Time" column are in the given limits. The time is defined in the format: #month day year hour:minute:second#.
SELECT * FROM Table WHERE Time BETWEEN #12 25 2020 23:53:30# AND #12 25 2020 23:55:30#
Example4:
(Microsoft syntax in the PROMOTIC system) selection of the "column1" and "column2" columns in the table named "Table" where values of the "column1" column that is of the String type, begin with the chars from "A" to "D".
SELECT column1, column2 FROM Table WHERE column1 LIKE '[A-D]*'
Example5:
(Microsoft syntax in the PROMOTIC system) selection of all columns in the table named "Table" where values of the "Time" column are in the given limits and they are sorted according to this column. The time is defined in the format: #month day year hour:minute:second#.
SELECT * FROM Table WHERE Time BETWEEN #12 25 2020 23:53:30# AND #12 25 2020 23:55:30# ORDER BY Time

2. Work with table

If the database is just connected, then it is possible to work with the connected database by means of the SQL statements (PmaDatabase.Execute, PmaDatabase.ExecuteSQL methods) and ExistTable, DeleteTable, Create, CreateField, CreateIndex, CreateEnd methods can be used. The other methods are not functional because the table is not opened.

If the table is opened, then it is possible to work with the connected database by means of the SQL statements and moreover all other methods are functional.

The pointer (current position) specifies unambiguously the table record which the methods of the PmaDatabase object will work with. If not set, then the methods, working over the table record, end with an error. If it is for example necessary to write a value into the record, then at first the pointer must be moved to the record and then it is possible to write into this record. Caution! - after opening the table, the pointer is not initialized (if the pointer is not valid, then the methods for writing and reading fail).

The table pointer is moved by calling the methods PmaDatabase.Move, PmaDatabase.MoveTo, etc. or PmaDatabase.FindFirst, etc.

Tests if the pointer is at the beginning or at the end of the table, can be carried out by means of the PmaDatabase.IsBOF, PmaDatabase.IsEOF methods.

It is possible to read a value from the table by means of the PmaDatabase.FieldValues property. It is possible to write a value into the table by means of the PmaDatabase.FieldValues property or by the PmaDatabase.SetRecordValueNull method. If it is necessary to write just one value into the table record, then the PmaDatabase.FieldValues property (PmaDatabase.SetRecordValueNull) is called directly. If it is necessary to write more values into the table record, then it is better and quicker to apply the following walkthrough:

1. call the PmaDatabase.Edit method.
2. repeatedly call the PmaDatabase.FieldValues property (by this a data buffer is ready but the table itself isn't modified so far).
3. only after calling the PmaDatabase.Update method the data are written into the database.
 
An empty record can be added by the PmaDatabase.AddNew method. The initialized record can be added by the PmaDatabase.Add method:
1. call the PmaDatabase.Add method.
2. repeatedly set the PmaDatabase.FieldValues property (by this a data buffer is ready but the table itself isn't modified so far).
3. only by calling the PmaDatabase.Update method the data are written into the database.

A record can be deleted by the PmaDatabase.Delete method. To find a record in the table the PmaDatabase.FindFirst method,... can be used. (these methods are not functional if the "Technology" configurator is set to Access through ODBC).

 
It is possible to work with the table by a transaction processing. The transaction processing is a batch processing (either the entire command batch is processed or it isn't processed at all). The principle is as follows. At first the PmaDatabase.BeginTrans method is called. From this point all operations that modify the database and the table, are watched. Only after calling the PmaDatabase.Commit method, all changes made from previous calling the PmaDatabase.BeginTrans method, are effected. If the PmaDatabase.Commit method isn't called (e.g. due to the termination of the running application, a power outage - reset or the PmaDatabase.Close method is called), then all changes made from previous PmaDatabase.BeginTrans aren't effected. On transaction processing the PmaDatabase.RollBack method can be used as well. This method returns all table modifications back into the state when the PmaDatabase.BeginTrans method was called previously. Thus if the PmaDatabase.BeginTrans method for the transaction processing is called, any subsequent modifications are made (these would be effected only when the PmaDatabase.Commit method is called) and the PmaDatabase.RollBack method is called, then no modification of the table occurs.

Caution: The transaction processing cannot be supported by some databases, for example databases Excel, dBase, PARADOX, basic version of the MySQL database, etc.

 
The PmaDatabase.Execute and PmaDatabase.ExecuteSQL methods can be used for a multiple processing the tables.

To get the information about the table structure, the PmaDatabase.GetFieldCount and PmaDatabase.GetFieldInfo methods can be used.

3. Close and disconnect database table

The database table can be closed by calling the PmaDatabase.CloseTable method. In this case the database remains connected, it is possible to call for example the Execute, ExecuteSQL methods.

It is possible to close the table and disconnect the database by the PmaDatabase.Close method.

The fact that the database is closed automatically on the termination of the running application, can be used (all database tables are closed as well).

Tip

It is possible also to work with the PmaDatabase object as follows:
- to fill in configurators of the "Database" tab.
- to open the specified table (in the "On start" configurator or by the Open method).
- to work with it (reading, writing) and then to close it by the Close, CloseTable method.
- consequently to change the Table or PmaDatabase.OpenSQL property (according as the "Open using the SQL statement SELECT" configurator is checked) and to open (now already another table in the same database), to work with it, to close it, etc.
 
On this walkthrough instead of the PmaDatabase.Close method it is better to use the PmaDatabase.CloseTable method (if the database remains unchanged). It doesn't close the whole database object with a table but it keeps the database connected and it closes only the table (quicker approach). After finishing the work with the database it is suitable to call the PmaDatabase.Close method.
PROMOTIC 9.0.13 SCADA system documentation - MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice