Working with databases

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 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).

The D2000 DbManager process can access databases in two different ways:

• 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 process using advanced OCI features was created.

It depends on the used binary (in the *.DBM process configuration), which version of the DBManager is started:

• The dbmanager.exe binary (dbmanager on Linux/RPI) implements access via the ODBC interface.
• The dbmanager_ora.exe binary (dbmanager_ora on Linux/RPI) implements access via the OCI interface.

Within the DODM model, the DBManager process is the parent of objects of type Database, and these are the parents of objects of type Table.

Database object

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 process is optimized for the parallel work of several users with a Database type object. Therefore, it can create multiple connections to one SQL database. Each such connection is serviced by its 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 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 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 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 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 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 process has created for this object.

Table object

The Table object represents a table or view in an SQL database. Each table has its own structure - column names and data types, which are defined by an object of the Structure Definition type. It is possible to configure which columns make up the key, which are optional (i.e. exist in the structure definition in D2000, but not in the SQL database), and which are non-null.

On the Table object, it is also possible to define the type of access to the table (none, read, read+modify).

A useful feature is defining the time depth. 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.

Due to data protection laws (GDPR), the possibility of automatic data anonymization in selected columns based on the set time column was also implemented.

Example of use: data in the Name, Surname, Date_of_Birth columns in the Contract table will be anonymized after 5 years based on the Contract_End_Time column. Anonymization will take place in such a way that the Name and Date_of_Birth are deleted, the Surname is changed to "Anonymized yyyy-mm-dd", where yyyy-mm-dd is replaced by the date on which the anonymization was performed.

Database operations

The actions performed by DBManager can be divided into several groups.

Actions of the Browser displayer – this is a direct interactive viewing of data by the user within the scheme or directly in the D2000 HI. The user can navigate between individual pages and edit, insert new rows, and delete rows according to the configured access rights and properties of the Browser.

Script actions - actions performed in an ESL script (and their equivalents available from the Java language). These can be further divided into several categories:

• Work with transactions: opening/closing transactions, their commit and rollback. These actions include:
  • DB_TRANS_OPEN
  • DB_TRANS_CLOSE
  • DB_TRANS_COMMIT
  • DB_TRANS_ROLLBACK

The transaction handle can subsequently be used for actions belonging to other categories. If these actions do not use a transaction handle, COMMIT is performed automatically after each action.

• Standard work with data – based on the condition (WHERE) or based on the key column(s) defined on the Table object, it is possible to read/insert/update rows (one or more) in the table. These actions include:
  • DB_CONNECT – "connecting" to the table
  • DB_DISCONNECT - "disconnecting" from the table
  • DB_DELETE – deletion of data in the table
  • DB_INSERT – insertion
  • DB_UPDATE - update
  • DB_INSUPD – a combination of insertion and update (if the row with the given key already exists)
  • DB_READ – reading

• Accelerated work with data – DBS_* actions allow you to skip DB_CONNECT/DB_DISCONNECT, other functionality is identical:
  • DBS_DELETE
  • DBS_INSERT
  • DBS_UPDATE
  • DBS_INSUPD
  • DBS_READ
    
    
• Actions for working with BLOBs: BLOBs are used in databases to store large binary data (e.g. PDF invoices). These are represented as files on the D2000 side. Special actions are used for reading/writing:
  • DB_READ_BLOB, DBS_READ_BLOB – reading a BLOB into a file
  • DB_UPDATE_BLOB, DBS_UPDATE_BLOB – storing the BLOB in the SQL database
    
    
• Paged work with data: these are script actions that enable paged access to data. For large volumes of data, it is better to load and process the data in the script gradually, e.g. in batches of 100 rows - the equivalent of pagination in the Browser.
  • PG_CONNECT - "connecting" to the table and setting the page size
  • PG_DISCONNECT - "disconnecting" from the table
  • PG_READ – reading the specified page of data
  • PG_INSERT – inserting one row into the table
  • PG_DELETE – deleting one row in the table
  • PG_UPDATE – updating one row in the table

• 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 (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

After the SQL_PREPARE action, it is possible to perform the sequence SQL_BINDIN (setting parameters - different each time) and repeated SQL_FETCH-es (reading the returned rows) several times. Finally, we end the work by calling SQL_FREE. This is how the recycling of parameterized SQL commands is implemented.

• 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 process is switched over (or the redundant D2000 Server is switched over), or the table is deleted from the configuration.