Promotic
WikipediaLinkedInYoutubeTwitterFacebook

Description of handling PmDatabase object referencing to existing table

 

1. Connect and open database table

By a database connection the database specified by the Database (file, folder, data source) configurator is connected. It is possible to connect a database on starting the application (On start configurator is set by the option "Connect database and open table (the same as "Open" method)" or "Connect database and do NOT open table (the same as "Connect" method)") or any time later by calling the PmDatabase.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 by the Table (file) configurator is opened. It is possible to open a table on starting the application (On start configurator is set by the 'connect database and open table' option) or any time later by calling the PmDatabase.Open method. This operation is slower that the database connection. The table can be opened in the modes "for read and write", "for read only", etc., see for example the Table open mode configurator (this influences the opening speed).

By configurators of the Database page it is possible to define a table and method of its opening. The On opening use SQL statement Select configurator determines 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 commands depends on setting the Technology configurator:
- if it is set to Access through ODBC, possible SQL statements are defined in the SQL syntax of the database which the PmDatabase object is connected to.
- if it is set to Direct access to table - DAO, it is necessary to enter SQL statements in the Microsoft syntax supported in the PROMOTIC; 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 data 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 PROMOTIC) selection of all columns in the "Table" 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 PROMOTIC) selection of the "column1" and "column2" columns in the "Table" table where values of the "column1" column are greater than 10.
SELECT column1, column2 FROM Table WHERE column1 > 10
Example3:
(Microsoft syntax in PROMOTIC) selection of all columns in the "Table" 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 2016 23:53:30# AND #12 25 2016 23:55:30#
Example4:
(Microsoft syntax in PROMOTIC) selection of the "column1" and "column2" columns in the "Table" table where values of the "column1" column that is of the String data type, begin with the chars from "A" to "D".
SELECT column1, column2 FROM Table WHERE column1 LIKE '[A-D]*'
Example5:
(Microsoft syntax in PROMOTIC) selection of all columns in the "Table" 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 2016 23:53:30# AND #12 25 2016 23:55:30# ORDER BY Time

2. Work with table

If the database is just connected, it is possible to work with the connected database by means of the SQL statements (PmDatabase.Execute, PmDatabase.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, 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) identifies unambiguously the table record which the methods of the PmDatabase object will work with. If it is not defined, the methods, working over the table record, end with an error. If it is for example necessary to write a value into the record, 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 (it the pointer is not valid, the methods for writing and reading fail).

The table pointer is moved by calling the methods PmDatabase.Move, PmDatabase.MoveTo, etc. or PmDatabase.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 PmDatabase.IsBOF, PmDatabase.IsEOF methods.

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

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

A record can be deleted by the PmDatabase.Delete method. To find a record in the table use the PmDatabase.FindFirst method,... (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 PmDatabase.BeginTrans method is called. From this point all operations that modify the database and the table, are watched. Only after calling the PmDatabase.Commit method, all changes made from previous calling the PmDatabase.BeginTrans method, are effected. If the PmDatabase.Commit method isn't called (e.g. due to the termination of the running application, a power outage - reset or the PmDatabase.Close method is called), all changes made from previous PmDatabase.BeginTrans aren't effected. On transaction processing the PmDatabase.RollBack method can be used as well. This method returns all table modifications back into the state when the PmDatabase.BeginTrans method was called previously. Thus if the PmDatabase.BeginTrans method for the transaction processing is called, any subsequent modifications are made (these would be effected only when the PmDatabase.Commit method is called) and the PmDatabase.RollBack method is called, 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 MySql, etc.

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

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

3. Close and disconnect database table

The database table can be closed by calling the PmDatabase.CloseTable methods. 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 PmDatabase.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 PmDatabase object as follows:
- to fill in configurators of the Database page.
- to open the specified table (by 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 PmDatabase.OpenSQL property (according as the Open using the SQL statement SELECT configurator) 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 PmDatabase.Close method it is better to use the PmDatabase.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 PmDatabase.Close method.
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice