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 after 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 to 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 whether the table is opened according to the specified SQL statement of the SELECT
type, otherwise it is opened directly according to the "Table (file)
The type of entering SQL statements
depends on setting the "Technology
- if is set to Access through ODBC
, then possible SQL statements
are defined in the SQL language 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:
||supported SQL implementation|
and in the WHERE statement syntax for entering of date and time as follows:
||supported SQL implementation|
|WHERE ... '25 10 2002 12:22:30' ...
||WHERE ... #10 25 2002 12:22:30# ...|
(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
(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
(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 2022 23:53:30# AND #12 25 2022 23:55:30#
(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]*'
(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 2022 23:53:30# AND #12 25 2022 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
methods) and ExistTable
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 read and write fail).
The table pointer is moved by calling the methods PmaDatabase.Move
, etc. or PmaDatabase.FindFirst
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
It is possible to read a value from the table by the ReadFieldValue
method. It is possible to write a value into the table by the WriteFieldValue
method or by the PmaDatabase.SetRecordValueNull
method. If it is necessary to write just one value into the table record, then the PmaDatabase.WriteFieldValue
) 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:
An empty record can be added by the PmaDatabase.AddNew
method. The initialized record can be added by the PmaDatabase.Add
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 database table occurs.
The transaction processing cannot be supported by some databases, for example databases Excel
, basic version of the MySQL
methods can be used for a multiple processing the tables.
To get the information about the table structure, the PmaDatabase.GetFieldCount
methods can be used.
It is possible also to work with the PmaDatabase
object as follows:
- to fill in configurators of the "Database
- to open the specified table (in the "On start
" configurator or by the Open
- to work with it (reading, writing) and then to close it by the Close
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