openPDC with external database

Hello,

I am working in a project team on openPDC proof of concept. I admit I am new in openPDC subject. I have two questions regarding how openPDC cooperates with external databse.

We would like to configure external database (for PoC, SQLite) with separate tables for PMU’s, based on our schema. Will openPDC send data to such external DB? Or the DB schema needs to be created according to internal openPDC database?

Second issue is whether the data sent as output stream are validated. I mean if some dataframes from PMU are incorrect are they rejected or all data as obtained are forwarded to output streams (meaning our external DB)?

I would appreciate any comments or hints on such issues.

Best,
Anna

Hello Anna,

The database created by the openPDC software is intended only for configuration data. It’s actually fairly uncommon to use this database for saving PMU data. However, the request is common enough that we developed the AdoOutputAdapter to write measurements into a relational database table. It would be possible to write these measurements back to the openPDC database or an external database. Using multiple AdoOutputAdapters, you can write data to multiple tables or databases so it would indeed be possible to set up your proof of concept.

The AdoOutputAdapter doesn’t really care about the schema of the database that writes to, but the table it’s writing to will have to be mapped to fields of the IMeasurement interface via adapter configuration. It’s designed to simply write to the table whenever it receives a new value, so I’d highly recommend at least three fields in your PMU tables for a signal identifier, a timestamp, and the value itself.

Thanks,
Stephen

Hello Stephen,

Thank you very much for your fast reply. It definitely solves my question regarding an external database.

Since you state that the adapter is designed in order to write to the table whenever it receives a new value, I would like to clarify if all data even with incorrect CRC, of dataframe, for instance, are directed to the databse. I would appreciate your comment on it.

Best regards,
Anna

Frame checksum validation in openPDC is applied at the point of ingress into openPDC. Therefore, it is independent of the destination for the data, whether it’s the database, the local historian, another IEEE C37.118 stream, or some collection of multiple destinations. That is to say, if checksum validation is disabled in the input device’s configuration, it will pass data into openPDC’s routing engine to be consumed by the AdoOutputAdapter and written to the database regardless of the CRC.

Checksum validation can be controlled for each frame type independently by adding the checkSumValidationFrameTypes parameter to the connection string for the input device. The value of this setting is a comma separated list of frame types for which validation should be enabled. Valid frame types come from the following list.

  • NoFrames
  • ConfigurationFrame
  • DataFrame
  • HeaderFrame
  • CommandFrame
  • AllFrames

For example, to disable checksum validation specifically on data frames, add the following setting to your input device’s connection string: checkSumValidationFrameTypes=ConfigurationFrame, HeaderFrame, CommandFrame. To ignore all checksums, add the following setting to your input device’s connection string: checkSumValidationFrameTypes=NoFrames.

By default, validation is enabled for all frame types, which means data would not be passed along to the AdoOutputAdapter and would not be written to the database in the case of an invalid CRC.

Thanks,
Stephen

1 Like

Stephen,

thank you so much for the very thorough answer. Having your explanations we can go further with the openPDC proof of concept.

Best regards,
Anna

Hello,

I would like to ask a question as a continuation of the thread with an external data base. According to our client requirements we should set a connection of openPDC to an external database in SQLite.
In my project team we have found a definition of the Connection String for MySQL at one of the threads.

Connection String : BulkInsertLimit=500; DataProviderString={ AssemblyName={MySql.Data, Version=6.9.5.0 , Culture=neutral, PublicKeyToken=c5687fc88969c44d}; ConnectionType=MySql.Data.MySqlClient.MySqlConnection; AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter }; DbConnectionString={ Server=localhost; Database=openPDC; Uid=openPDC; Password=openPDC }; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value; InputSourceIDs=PPA

Could you please comment on how this would be different or problematic to define for SQLite? How we should redefine it for SQLite external database? My team colleague responsible for this connection states that he obtains errors with ajusting the string to SQLite. Any hint or comment would be most welcome.

Best regards,
Anna

I’m a little confused as openPDC supports SQLite natively. You can even migrate your database from any of the supported RDBMS to SQLite. Just use the ConfigurationSetupUtility that comes with openPDC.

When you setup a database using SQLite you can check the ConnectionString and DataProviderString in the openPDC.exe.config file.

Thanks,
Ritchie