Contents

Chapter 1
The PqDrv Driver and the REXYGEN System

1.1 Introduction

This manual describes the PqDrv driver for connecting the REXYGEN system to a PostgreSQL database. This driver facilitates both reading from and writing to the database. Additionally, it enables the export of the REXYGEN system’s archives, including alarms, events and trends.

1.2 System Requirements

The PqDrv driver is compatible with both Windows and Linux target devices. A TCP/IP stack (Ethernet card, USB WiFi dongle, etc.) is required for network communication.

For effective use of the driver, the following software must be installed on both the host (development) and target (runtime) computers:

1.3 Installation of the Driver on the Host Computer

The PqDrv driver is included in the REXYGEN system’s Development tools installation package. During installation, ensure to select the relevant package. The REXYGEN system typically installs in the following directory: C:\Program Files\REX Controls\REXYGEN <version>.

The installation process copies the following files to the installation folder:

Bin\PqDrv_H.dll – The configuration component of the PqDrv driver.

Bin\PqDrv_T.dll – The target component of the PqDrv driver, invoked by the RexCore runtime module.

Doc\PDF\ENGLISH\PqDrv_ENG.pdf – This user manual.

1.4 Installation of the Driver on the Target Device

1.4.1 Windows Machines

The target component of the driver for connecting to the database on Windows 10/11 is included in the REXYGEN system’s Development tools, as previously mentioned.

1.4.2 Linux Machines

If the RexCore runtime module is not installed on your target device, refer to the Getting Started guide of the REXYGEN system [1] for installation instructions. This includes all necessary drivers, including PqDrv.

To install PqDrv separately on Linux, use the following command:
sudo apt-get install rex-pqdrvt

Chapter 2
Including the Driver in the Project

The driver is included in the project as soon as it is added to the project’s main file and its inputs and outputs are connected in the control algorithms.

2.1 Adding the PqDrv Driver

The project’s main file with the PqDrv driver included is shown in Figure 2.1.

To include the driver in the project, add a block of type IODRV, renamed to PQ, and connect it to the Drivers output of the main EXEC block. The name of this block (PQ, see Fig. 2.1), is the prefix of all input and output signals provided by this driver. The four most important parameters are:

module – name of the module linked to the driver, PqDrv in this case.

classname – class of the driver, PqDrv for the respective driver.

cfgname – name of the driver configuration file, e.g., PQdrv.rio.

factor – multiple of the EXEC block’s tick parameter defining the driver’s task execution period.

These parameters of the IODRV function block are configured in the REXYGEN Studio program. The configuration dialog is also shown in Fig. 2.1.

The Configure button opens the configuration dialog of the PqDrv driver, which is described in chapter 3.

2.2 Connecting the Inputs and Outputs in the Control Algorithm

The inputs and outputs of the driver must be interconnected with the individual tasks (.mdl files). The individual tasks (QTASK or TASK blocks) are connected to the QTask, Level0,…, Level3 outputs of the main EXEC block. Use the blocks depicted in Fig. 2.2 within the individual tasks to interchange data between the control algorithm and the PqDrv driver.

The From block, which allows the user to read one input signal, has the Goto tag set to PQ__<IN>. The Goto block, which allows the user to set one output signal, has the Goto tag set to PQ__<OUT>, where <IN> and <OUT> are strings referring to the items defined in the *.rio configuration file. The blocks always have the PQ prefix right at the beginning of the tag followed by two underscores. The blocks with multiple inputs/outputs have this prefix directly in their name.

The use of multi-input/output blocks is recommended if data exchange rate (sampling frequency) is the priority. See the function block reference manual [2] for details about INOCT, OUTOCT, INHEXD, OUTHEXD blocks.

Each I/O name must be unique in all groups. Therefore, the section Group has an optional parameter Name (see chapter 3 for details about the Group section). In this case, an I/O signal is referenced by <group_name>_<item_name>.

The installation of the REXYGEN system includes a library of examples, where, among other things, the section 0404-02_PqDrv is dedicated to the use of the PqDrv driver. The example 0404-02-00_IO_Flags contains a library of usable inputs and outputs.

Chapter 3
I/O Driver Configuration

This chapter describes the configuration of individual input and output signals and their symbolic naming. The signals are mapped to the corresponding database.

The configuration dialog is part of the PqDrv_H.dll. It can be activated from REXYGEN Studio by pressing the Configure button in the parameters dialog of the IODRV block (see chapter 2). The resulting configuration is stored in a *.rio file, standard for other REXYGEN drivers. The dialog is segmented into three tabs, each detailed below.

3.1 Connection Tab

The first tab is dedicated to configuring the connection parameters for establishing a link with the database. This tab is illustrated in Figure 3.1. Below are explanations for each of the individual parameters:

Server host – Address of the server where the database is hosted.

Server port – Port number used for the database server connection.

Database – Name of the database to connect to.

User (UID) – Username associated with the database account.

Password (PWD) – Corresponding password for the database user.

Additional parameters – Extra parameters or settings required for specific database connections.

In addition to the above parameters, the following checkboxes are available:

Local time – Use local time instead of UTC in SQL requests.

System clock – Use system clock instead of RexCore timer as time source in SQL requests. See TRND block documentation for more information about SYSCLOCK and CORETIMER.

ISO format – Use ISO time stamp format in SQL requests.

3.2 Archives tab

This tab can be used to configure archiving from RexCore to the database. The tab’s appearance is shown in Figure 3.2, and the meaning of its items is as follows:

Mode – Defines the structure of exporting data to the database. The options are:

0	Nothing gets exported (used for disabling the item temporarily).
1	Only the alarms and events are exported (filtered by additional parameters). The table in the database must contain the following columns: Time, AlarmID, Code, Level, Value.
2	Only the trends are exported, i.e. the data stored by the TRND block. The data is filtered by additional parameters. The table in the database must contain the following columns: Time, GroupID, Value1, Value2, ...
3	Only the trends are exported, but on the contrary to the above the SQL parameter has the meaning of a full SQL query, to which the values are injected. The following placeholders can be used: ?G = value of parametr GlobalId in *.rio file; ?S = value of parametr GlobalString in *.rio file; ?Y = year; ?M = month; ?D = day of month; ?T = timestamp (time and date of archive item); ?N = nanoseconds (in second) of timestamp; ?C = code; ?L = level; ?I =itemID; ?1 =1st item; ?2 =2nd item; ... A plain question mark has the meaning of next item in the following order: 1st value, 2nd value, ...; ). Additional SQL2 parameter has the meaning of an SQL query used for detection of last record stored in the database which is used for automatic loading of data archived when the database was not available.
4	Only the alarms are exported and the SQL parameter has the meaning of a full SQL query. The placeholders and the SQL parameter are like in previous mode.

Archive ID – Number of archive to read the data from. The archives are numbered from 1 in the order of appearance in the configuration of REXYGEN executive (ARCHIVE blocks connected to the EXEC block).

Item ID ranges – A range of IDs (the id parameter of the originating block) to export from archive to the database (in the Archive section).There must be even number of entries, where the odd entries define the start of an interval and the even entries define its end. Therefore e.g. "100,100,104,109" means IDs 100, 104, 105, …, 109. The entries must be sorted in ascending order.

SQL table name – Table name or full SQL command as defined by the Mode parameter. In some cases the notation <database_name>.<table_name> must be used for referencing database tables.

3.3 Groups tab

The final tab is used for directly reading and writing data to and from the algorithm. The Groups tab is depicted in Figure 3.3 and is subdivided into three sections:

• Group table – This section displays all the prepared data groups. Meaning of the columns are as follows:

  Name – Name of the group for reading/writing items.

  Mode – The available modes are described in the table below.

  Period [s] – Period in seconds to generate the SQL query.

• Group settings – This section encompasses the configurations for the presently selected group. Within this section, you will find the three aforementioned items, along with an additional item, either SQL table name or SQL query, contingent upon the selected Mode. In some cases the notation <database_name>.<table_name> must be used for referencing database tables.
• Item table – This table is only shown when a group is selected, displaying the reading and writing items for that group. Columns have following meaning:

  Name – Name of the reading/writing item.

  Type – The possible types are: bool, int, double, string, large.

3.4 Special signals

There are additional auxiliary signals for each I/O signal. These can be assessed by appending the following strings to the signal reference:

_Status – status code - result of last query. Codes are: 0 ... last request succeeded 1 ... last request failed 2 ... no request perform yet 3 ... last request return empty dataset 101 ... last request failed due database disconnected 102 ... connection to database is not established, no request required

_Disable – If True, the read/write operations for the whole group are disabled.

_Trigger – A rising edge triggers execution of the read/write operation.

_Age – Number of seconds since the last read/write database access.

_Fresh – Same as _Age

_AgeDb – Number of seconds since the last read/write database access, on the contrary to the above the age is defined by the database item.

_FreshDb – Same as _AgeDb

_Period – Contains or sets the Period parameter, i.e. the period of SQL query execution as defined for each group.

_Done – indicate last request finished successfully (value is set to off after read if Trigger is off)

_Error – indicate last request failed (value is set to off after read if Trigger is off)

_Empty – indicate last request return empty dataset (value is set to off after read if Trigger is off)

This attribute are attribute of group, because whole group is written/read by one SQL command. Therefore attribute can be accessed by signal reference string
PQ__<group_name>_<attribute_name>, where <group_name> is value of optional parameter Name in section Group.

There are additional auxiliary global signals:

Connect – connection to database server.

Connected – status of the connection to database server.

Reset – resetting archive reading (all archiving groups).

Resetting – pushed off when resetting is finished.

GlobalId – set or get value of GlobalID parameter in *.rio file.

GlobalString – set or get value of GlobalString parameter in *.rio file.

There are additional auxiliary signals for each archive worker (associated with Archive section in configuration file). It require optional parameter Name in Archive section. These can be accessed by signal reference string PQ__<archive_name>_<option_name>, where <option_name> is:

_Count – number of successfully written archive items into database

_ErrorCount – number of failed archive items (skipped and not written into database)

_LastErrorStr – timestamp of last processed archive item (written into database or skipped)

Chapter 4
Implementation Details

Additional information about the use and implementation of the PqDrv driver in the REXYGEN system is gathered in this chapter.

• The Items parameter in the Archive section is a list of numbers, where the odd entries mean from and the even ones to. E.g., Items "2, 5, 10, 15" exports items with IDs 2 to 5 and 10 to 15. There must be an even number of entries, even if exporting items with only one ID. The entries in the Items parameter must be sorted in ascending order.
• After connection to the database is established, data configured by Archive sections are automatically loaded to the database starting from the last record stored. This feature is not active when the database table is empty. In such a case, data loading starts from the current time only. If you want to load old data from archives to an empty database, insert a dummy record with a timestamp before the point you want to start from. Loading of old data is limited to 100 records per driver execution period to avoid performance issues. Please keep this in mind when setting the execution period.
• Although the majority of database systems are case-insensitive, the control system REXYGEN is case-sensitive. Therefore, the PqDrv driver is also case-sensitive in the I/O flags (the flags correspond with column names in the database).
• All values written or read to/from the database are decimal numbers (type double). The database columns can be of other types because SQL queries are textual. The optional parameter Type can be used, where i means the value is processed as long type, b denotes a bool type, s denotes a string type, and r denotes a real. For example, Type "rrisb" means the 1st and 2nd values are real numbers, the 3rd value is an integer, the 4th value is a string, and the 5th value is a boolean.
• The flags must be unique in the whole project because they contain no Group identifier. Only the first occurrence is processed in the case of duplicities.
• It is possible to define an (optional) parameter Name in the Group section. The flags must be in the form <group_name>_<item_name> in this case.
• The driver needs a username and password for login into the database. Both are stored in the *.rio file in plain text as all other parameters. Therefore, it is strongly recommended to use a dedicated login name with very restricted permissions.
• All timestamps (e.g., substitute for ?T in SQL queries) are expanded into a string in the form <year>-<month>-<day> <hour>:<minute>:<second>.<microsecond>. The UTC timezone is used (if not defined by another timezone by the Options-parameter).
• Using an SQL data type with at least microsecond resolution is recommended for timestamps (https://www.postgresql.org/docs/current/static/datatype-datetime.html).
• Current implementation limits the SQL string to 1023 characters (after expansion of question marks). The parameter Items (in both Archive and Group sections) is limited to 64 values.
• The column Code in alarm export is integer number where lowest 5bit is alarm class and higer 3 bits is alarm subtype. The classes are:
  

  0	System alarm
  1	Bool alarm
  2	Byte value alarm
  3	Short value alarm (signed 16 bits integer number)
  4	Long value alarm (signed 32 bits integer number)
  5	Word value alarm (unsigned 16 bits integer number)
  6	DWord value alarm (unsigned 32 bits integer number)
  7	Float value alarm
  8	Double value alarm
  10	Large value alarm (signed 64 bits integer number)
  12	String value alarm
  13 .. 16	Not used
  17	Bool value group event
  18	Byte value group event
  19	Short value group event
  20	Long value group event
  21	Word value group event
  22	Dword value group event
  23	Float value group event
  24	Double value group event
  26	Large value group event
  31	Alarm acknowledge

  The subtypes for system alarms are:
  

  0	Date mark (is not exported)
  1	Executive event
  2	Archive event

  The level indicate event in this case. The executive events are:
  

  0	System reset
  1	Download begin
  2	Download end
  3	Download failed
  4	Executive stop
  5	Executive start
  6	Executive swap
  7	Time set

  and the archive event:
  

  0	Archive cleared (not used now)
  1	Archive reconstruction saved (not used now)
  2	Archive reconstruction normal (not used now)
  3	Checksum error (not used now)
  4	Integrity error (not used now)
  5	Sizes changed (not used now)
  6	Limit exceed (disk archives only)
  7	Buffer overflow

  The subtypes for boolean alarms are:
  

  0	High to Low (e.g. the attached boolean variable have been changed from the high/true/1 value to the low/false/0 value)
  1	Low to High (e.g. the attached boolean variable have been changed from the low/false/0 value to the high/true/1 value)

  The subtypes for numeric alarms are:
  

  0	low alarm
  1	high alarm
  2	2nd low alarm
  3	2nd high alarm

  The level 0 indicate end of alarm conditions. The alarms with level from 128 to 255 not indicate end of alarm conditions by this special alarm event. The alarm acknowledge should has same subtype and level as acknowledged alarm. However not active alarms are regarded as acknowledged by the acknowledge of any subtype. The 1st level alarm is also regarded as acknowledge by the acknowledge of the 2nd level.

Chapter 5
Troubleshooting

First and foremost, it’s advisable to explore the library of examples, especially the section 0404-02_PqDrv, which pertain to the usage of PqDrv.

Just like in the case of any other problem, it is recommended to view the error and debug information (the System Log section in REXYGEN Studio). Unsuccessful database connections, misconfigured SQL queries, and other related issues are listed in the log. The most frequent problems include:

• The requested tables are not available in the database. Remember, the database might be case-sensitive.
• Inconsistency in naming of the columns. Again, the database might be case-sensitive.
• Although the PqDrv driver uses very simple SQL syntax, there are some differences among individual database systems.
• Especially when defining the SQL queries by hand, it is necessary to double-check the syntax.
• Duplicated item name in the Items parameter, which results in the item being not available.

In the case that the given input or output works with other software tools and does not work in REXYGEN, please report the problem to us. E-mail is preferred; reach us at support@rexygen.com. Please include the following information in your description to help us process your request as soon as possible:

• Identification of the REXYGEN system you are using. Simply export it to a file using the REXYGEN Studio (Target $\to$ Licensing $\to$ Export).
• Short and accurate description of your problem.
• The configuration files of REXYGEN (.mdl files) reduced to the simplest case which still demonstrates the problematic behavior.

Bibliography

Documentation reference number: 16710

2024 © REX Controls s.r.o., www.rexygen.com