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 the application is launched (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 the application is launched (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 table 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)" configurator.
The type of entering
SQL statement depends on setting the "
Technology" configurator:
- if is set to
Access through ODBC, then possible
SQL statement 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 statement 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 10 - 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 2024 23:53:30# AND #12 25 2024 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 2024 23:53:30# AND #12 25 2024 23:55:30# ORDER BY Time
2. Work with the table
If the database is just connected, then it is possible to work with the connected database by means of the SQL statement (
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 read and write 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 methods
PmaDatabase.IsBOF,
PmaDatabase.IsEOF.
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 method (
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:
An empty record can be added by the
PmaDatabase.AddNew method. The initialized record can be added by the
PmaDatabase.Add method:
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 to 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.
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.
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 the table (reading, writing) and then to close it by the
Close,
CloseTable method.
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 the 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.