Data Pump to the Database

Today I’d like to show how easy it is to import data from the D2000 into a database.

The client’s request was simple: They have a list of about 30 I/O tags, and they need to import the values into a table in an SQL database. However:

• They need the value for each I/O tag every 15 minutes, with the original timestamp. So if the value last changed an hour ago, it will not be recorded.
• If a value is invalid, it will not be recorded in the database.

First, we worked with the customer to define the format of the database table. The sensor_data table will have the following columns:

• sensorid – Text name of the I/O tag in D2000.
• value – Value of the I/O tag.
• time – Measurement time from the communication.

Based on the format, we have designed the SD.sensor_data Structure Definition with the same column names:

We created a scada_data object of type Database and entered the defined DSN and username.
Important note: We agreed with the customer that timestamps will be in UTC+1 hour (which corresponds to Slovak standard time). The advantage over using local time is that there is no need to deal with ambiguous times during the transition from daylight saving time to standard time. Therefore, the “Use monotonic time” option is configured on the scada_data object with a 1-hour offset.

The customer used PostgreSQL as the SQL database server. On the D2000 application server, it was necessary to configure a 64-bit DSN (since a 64-bit DBManager was used), whose name (scada_data) is used in the configuration of the scada_data object of type Database in D2000.

In the DSN advanced settings (page 2), it is recommended to set the "Level of rollback on errors" parameter to "Statement" so that the failure of a single operation within a transaction does not cause the entire transaction to fail.

Now, we still need a Table object to represent the sensor_data table itself in the SQL database. The created Structure Definition will be used in its configuration. The column names in the table must be identical to the column names in the Structure Definition, though the order of the columns does not matter.

The table name is specified as „.sensor_data“. The period at the beginning prevents DBManager from prefixing the table name with the user name (scada_user), since PostgreSQL separates users and schemas (unlike Oracle or Sybase SQL Anywhere).

Note: PostgreSQL behaves differently concerning column names if they are enclosed in quotes (case-sensitive). Therefore, when using PostgreSQL, it is recommended to run the DBManager process with the /NQ startup parameter to suppress quotes (No Quotes).

The assignment specified that we were to copy the values of approximately 30 I/O tags into the database. To avoid having to reference them one by one in the script (and call the “pump” procedure for each one), it’s best to store these I/O tags in a structured variable and then loop through all the rows of the structured variable in the script.

So, we’ll create one more auxiliary structure definition, SD.sensor_list, which will have a column named Object of type Object.

We have connected all the required I/O tags to the SV.sensor_list structure, which uses the SD.sensor_list structure definition.

The final step is to write the ESL script (event) E.data_pump, which will insert data into the sensor_datatable once every 15 minutes. This script will run at the start of each minute (system object Min).

What can we notice?

• Line 9 contains the definition of the interval (900 seconds, i.e., 15 minutes).
• Lines 11–12 terminate the script if the minute is not a multiple of 15.
• Line 16 contains redundant rounding of the Min time to 15 minutes (a remnant from earlier development).
• Lines 18–19 define the local structure _dta using the definition of the SD.sensor_datastructure and resizing it to the same dimensions as the SV.sensor_liststructure.
• Lines 21–30 contain the loop for populating the _dta structure by iterating through the SV.sensor_list structure. If there is a valid value on the corresponding _i-th row of SV.sensor_list (line 22) and its timestamp is newer than 15 minutes (line 23), we increment the auxiliary index _idx (24) and populate the next row of the _dta structure with the name of the connected object, the time of the value, and the value itself (25–27).
• Lines 32–33 reduce the size of the _dta structure (if not all rows have been filled and the auxiliary index _idx has a value smaller than the size of SV.sensor_list).
• And finally, on line 36, all values are inserted into the database table using the DBS_INSERT action. Inserting multiple rows at once is optimized and faster than inserting them one by one.

For those interested, I’m also including the ESL script in text form 😊

============================================

;*********************************************************
;  DESCRIPTION: Data pump to PostgreSQL database
;  AUTHOR:      Humaj
;  LAST CHANGE: 2026 07 01
;*********************************************************

BEGIN

PRAGMA "ENABLE_INOUT_BY_REF"

INT  _intvl = 900 ; size of interval (900 sec)

IF %Mod(Min, _intvl/60) # 0 THEN          ; only export every 15 minutes

RETURN

ENDIF

INT _i, _idx, _ret

TIME _currTime = Min\TIM - %ModTime(Min\TIM, _intvl)  ; current time (aligned to 15 min)

RECORD NOALIAS (SD.sensor_data) _dta

REDIM _dta [SV.sensor_list\DIM]          ; resize to structure with I/O tags

FOR _i RANGE SV.sensor_list DO_LOOP

IF SV.sensor_list [_i]^Object\VLD THEN ; only valid value is copied to DB

IF _currTime - SV.sensor_list [_i]^Object\TIM < _intvl THEN ; if it was change in last 15 minutes

_idx := _idx + 1

_dta[_idx]^sensorid := %HBJToStr(SV.sensor_list [_i]^Object\HBJ)

_dta[_idx]^time     := SV.sensor_list [_i]^Object\TIM

_dta[_idx]^value    := SV.sensor_list [_i]^Object

ENDIF

ENDIF

END_LOOP

IF _idx < SV.sensor_list\DIM THEN

REDIM _dta [_idx]       ; reduce size of _dta to filled-in rows only

ENDIF

DBS_INSERT DB.sensor_data, _dta, _ret

END

============================================

What else is there to say?

The proposed solution demonstrates several things:

• By connecting I/O tags (or other objects) to the auxiliary structure SV.sensor_list, the code becomes independent of the data. The auxiliary structure can be expanded to include additional D2000 objects without requiring any changes to the code. At the same time, referential integrity can be used to ensure that the created E.data_pump script uses the SV.sensor_list structure, which in turn uses the connected I/O tags —which cannot be deleted without first being disconnected from the structure. Referential integrity thus simplifies navigation within the D2000 application and facilitates the mapping of data flows.
• D2000 features sophisticated time handling—at the Database object level, you can define how times in the database will be interpreted (local time or UTC with an offset).
• D2000 features optimized handling of database tables—for example, it can insert or retrieve multiple rows with a single database operation.
• ESL scripts can contain local variables, which can also be structured, utilizing existing structure definitions. The size of such variables can be dynamically changed (resized).

I haven’t demonstrated this, but readers can try it out for themselves—an ESL script created this way can be easily debugged in the ESL editor—viewing the contents of local variables, stepping through the code, setting breakpoints—regardless of whether the ESL script is running on a local or remote computer.

In a future blog post, I’d like to show another script that can work with historical data. Another customer requirement was to import historical data (stored as historical values of I/O tags) and, if necessary, to import data covering a specified period of downtime, which could be caused, for example, by a server outage involving the PostgreSQL database.

August 10, 2026, Ing. Peter Humaj, www.ipesoft.com