Please consider registering

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

— Forum Scope —

— Match —

— Forum Options —

Minimum search word length is 3 characters - maximum search word length is 84 characters

sp_Feed Topic RSS sp_TopicIcon
Store EventData in database
July 6, 2020
14:39, EEST
Forum Posts: 38
Member Since:
January 27, 2012
sp_UserOfflineSmall Offline


As I have to implement a HDA for Events, I was wondering if there is some code to be used in order to store EventData e.g. as BLOB in a MySql store.

In general, what is the recommended way to store Event History?

Many thanks.

July 7, 2020
10:21, EEST
Bjarne Boström
Forum Posts: 545
Member Since:
April 3, 2012
sp_UserOfflineSmall Offline


This would be an advanced and complicated topic. We do not have any samples regarding this. Maybe we should, but it depends a lot on what your application would do. And in general application level design has been outside the scope of the support.

The EventData is basically just a nicer API for

Map<List<QualifiedName>, Variant>


If you ask all keys via getFieldNames() and then retrieve all values sequentially via getFieldValue(…) you would get the same Map. If you implement some logic to concatenate the QualifiedNames, that could be simplfied maybe a bit. I would not try to save the EventData “as an object” so to say, but instead the data (or a selection) of it. The OPC UA service calls to ask event history have same kinds of EventFilter than subscribing, thus you might want to actually query the database and not do that in the application by fetching all the data every time from the time period.

Deciding what data to store is one choise you’ll have to make. All OPC UA events are is a snapshot of a subtree of the address space (with the Value Attribute of the nodes). Since that can be very large (like 100+ values for a single event, some Alarm types are HUGE) and changing depending on the events, it is not something a traditional SQL plays that nicely in my personal opinion. If you only care about storing the data and getting it back to the application later and the database is only used by that application it is easier.

The second, but more important one is that do you store namespaceindexes directly within the data or do you try to use namespaceuris. One way or another, unless you can guarantee that the application reading the database has the same exact NamespaceTable than the one who was writing, you will need to convert every namespaceindex when you read. This includes (but is not exhaustive list): NodeIds, QualifiedNames, any Structure that might have at least those types (or any field that is a Structure itself that contains those, recursively). And if you go with binary storage, their encoding id is also a NodeId (and that pretty much would stay as is, so you’ll need to decode with an old NamespaceTable and only then do the conversion).

You can use BinaryEncoder/BinaryDecoder to convert Variants to binary and back. And basically you’ll need to persist the NamespaceTable (unless you can guarantee that it is the same one from writing) alongside the data, and set that via an EncoderContext to the decoder later. In addition the reading side must have the StructureSpecifications/Serializers to do binary decoding (from the encoder context).

If you only decide to store certain event fields and know their datatype, you could maybe skip the Structure index conversion part, if you know you do not have them. In addition, if you know the data types, you could maybe define a proper table for them and skip most of the problematic parts.

If you try to save it all, you could try constructing a single Variant with an array of KeyValuePair (you’ll need to somehow concat the QualifiedNames to a single one for the name part however and probably just use 0 as the index always).

In general I think that is the most advice I can give, the scope is sort of outside the SDK, but this is something a future versions could try to improve.

In some distant future we’ll have JSON encoding/decoding, once we are that point a no-SQL database would probably be a lot better. (Though for the JSON decoding part we’ll need some rework as previously Structures were sequentially in field definition order, but with JSON due to very nature of JSON the order is random, so an encoder will be done first as there it wont matter)

July 14, 2020
16:24, EEST
Forum Posts: 38
Member Since:
January 27, 2012
sp_UserOfflineSmall Offline

Many thanks for your input. Looking forward to future version and will think about storing events once again.

Forum Timezone: Europe/Helsinki

Most Users Ever Online: 267

Currently Online:
11 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

hbrackel: 103

pramanj: 86

ibrahim: 70

kapsl: 57

gjevremovic: 49

TimK: 41

Fransua33: 39

fred: 38

Rainer Versteeg: 32

Thomas Reuther: 31

Member Stats:

Guest Posters: 0

Members: 1105

Moderators: 14

Admins: 1

Forum Stats:

Groups: 3

Forums: 15

Topics: 1009

Posts: 4268

Newest Members:

user1290, gregp, mariohetheringto, normagalindo47, aurelia27u, isobel41d356980, michaeldegli, gqbdolores, kez1399, jaclynmcvay358

Moderators: Jouni Aro: 851, Otso Palonen: 32, Tuomas Hiltunen: 5, janimakela: 0, Pyry: 1, Terho: 0, Petri: 0, Bjarne Boström: 545, Heikki Tahvanainen: 402, Jukka Asikainen: 1, Teppo Uimonen: 20, Markus Johansson: 19, Matti Siponen: 53, Lusetti: 0

Administrators: admin: 0