...
The D2000 DBManager process is used to work with application databases. This process accesses the SQL databases and performs all necessary actions. Requests for DbManager DBManager come from D2000 Event Handler processes (scripts executed on the server) and from D2000 HI processes (direct user access to databases via the Browser viewer + active schemas with scripts).
...
- Via ODBC interface to any database for which an ODBC driver is installed in the system and an ODBC DSN (Data Source Name) is created. We routinely work with PostgreSQL, MySql, MariaDB, Sybase SQL Anywhere, Microsoft SQL Server, Informix, Firebird, and other
- Via OCI interface (Oracle Client Interface) to the Oracle database. In the past, the Oracle database was widely used in large D2000 applications, and therefore a specialized version of the DbManager DBManager process using advanced OCI features was created.
...
Within the DODM model, the DbManager DBManager process is the parent of objects of type Database, and these are the parents of objects of type Table.
...
The Database object represents the SQL database together with the access rights to it, as it also contains the configuration of the username and password. Therefore, it is necessary to create several objects of type Database if we need to access the SQL database with different access rights (e.g. to access different schemas).
The D2000 DbManager DBManager process is optimized for the parallel work of several users with a Database type object. Therefore, it is able to create multiple connections with one SQL database. Each such connection is serviced by its own task and can work in transactional mode (reserved for the specific ESL script that created the transaction) or in non-transactional mode (shared by several scripts or D2000 HI processes, with a COMMIT automatically performed after each operation, so we call such a connection also automatic). In the configuration dialog of the Database object, you can set the number of predefined connections (they are created after the start of the DbManager DBManager process), limit the maximum number of connections, the maximum number of non-transactional (automatic) connections, and even reserve automatic connections for the Browser displayer.
...
It is also possible to specify after what time unused connections (created beyond the scope of predefined connections) should be closed. DbManager DBManager allows the created connections to be recycled - since the creation of a connection can be a rather demanding operation with high overhead for some SQL databases (e.g. Oracle).
If there is a firewall on the network between the DbManager DBManager process and the SQL database, it can be useful to specify empty operations after some time of inactivity - sometimes it happens that the firewall "cancels" a TCP connection that has not been used for a long time, and when DbManager DBManager tries to use it again, an error occurs. Empty operations make it possible to continuously check the connection status with the SQL database and to recreate it in the event of a breakdown.
It is also important to be able to set the interpretation of time data in the database - times can be in local time (according to the time zone of the server on which DbManager DBManager is running), or in monotonic time with a specified offset from UTC.
The value of an object of Database type is equal to the current number of connections that the DbManager DBManager process has created for this object.
...
A useful feature is defining the time depth. DbManager DBManager can automatically delete data in the table whose defined column (of type Absolute Time) is older than the time depth. Alternatively, different time depths can be defined for different periods using a Data Purpose object.
...
Database operations
The actions performed by DbManager DBManager can be divided into several groups.
...
- Actions for "variable" work with the database: if it is not known in advance from which table and which columns should be read, it is possible to use the SQL_SELECT command, which allows specifying the entire SQL command. This can also be parameterized (SQL_PREPARE and others related to it). The advantage of parameterized commands is the possibility of recycling them in the DbManager DBManager (execution multiple times with different parameters) and in the SQL database (the SQL command is parsed and an execution plan is created for it only once). This also includes actions to execute an arbitrary SQL statement and run a stored procedure:
- SQL_CONNECT - "connecting" to the database
- SQL_DISCONNECT - "disconnecting" from the database
- SQL_EXEC_DIRECT – execution of any SQL command (without returning data)
- SQL_EXEC_PROC – execution of a stored procedure with a list of parameters
- SQL_SELECT – execution of any SQL SELECT command (without parameterization)
- SQL_PREPARE – preparation of any SQL SELECT command (with parameterization)
- SQL_BINDIN – the setting of parameter values for SQL_PREPARE
- SQL_FETCH – fetch 1 or more rows returned after SQL_PREPARE
- SQL_FREE – releasing resources and ending SQL_PREPARE
...
- Special actions: other specialized actions:
- DB_REFRESH_TABLE – forcing the data displayed in the Browser to be refreshed. By default, after changing data through a specific object of the Table type, the data is restored for the Browsers in which this Table is displayed (unless they have data refresh turned off in the Browser configuration). However, there may be a situation where the data changes in another way (e.g. through another Table or by the SQL_EXEC_DIRECT action) and it is necessary to force the refresh from the script.
- DB_SET_PROCESS_PARAMS - set or clear "context". Context means to set named parameters (name-value pairs), which are stored in the global temporary table D2000_PROCESS_PARAMS. By default, the context is common to one D2000 HI or D2000 Event Handler process, or it can be limited by the start parameter --batch_mode to events started by the OPENEVENT action with the same instance number. The context is visible not only within D2000, but can also be used by views and stored procedures in the SQL database. An example of use can be the use of two named parameters for setting the period (PERIOD_FROM, PERIOD_TO), for which e.g. invoices will be displayed using database views.
- ON DB_CHANGE – registration of handler for data change. The handler will be called when the content of the database table changes, the DB_REFRESH_TABLE action is called, the active instance of the DbManager DBManager process is switched over (or the redundant D2000 Server is switched over), or the table is deleted from the configuration.
...