Configuration to get data from database

GPA Products openPDC
1

Hi sirs,

Well, I’m newer to use openPDC so I’ve get some questions and issues to setup it to my requirements.

Well, I could installed openPDC but now I need to get its data from its database.
When I installed openPDC, I’ve needed to setup a database but I’ve noticed that database is to load its configurations files
Is that rigth, or not?

My proposite is to get data from openPDC’s database with a API that I’ve write to my engineering tesis, so I don’t know if I can read from that database directly o should I made another configuration to do it ?

By default I use sqlite database.

I’ll be thanking for you helping

Regards

2

Hi Juan,

By default, the openPDC stores its configuration in a SQL database and the data in an openHistorian 1.0 archive. If your only requirement is to be able to query archived data, I recommend querying the openHistorian archive. If you must also use a SQL database, you will need to configure the openPDC to instead use the database as your archive by configuring an AdoOutputAdapter instance. There has been quite a bit of discussion over the years about how to configure the AdoOutputAdapter so I suggest you start by searching these boards for more information.

Thanks,
Stephen

3

Thanks for reply Stephen,

I’m trying to store data in a MySQL database.
Following your tips I’ve done this:

I’ve opened openPDC Manger -> Outputs -> Manage Custom Outputs -> Manage Custom Outputs Adapters.

Inside that window I can choose adapter type. I’ve choosen MySQL: Archive mesurenmet to a MySQL.
Conection String just get me two parameters:

  • InputMeasurementKeys
  • RequeueOnException

My question about it is: Why this Conection String doesn’t ask me database name, database password, conector version, so on?

I’ve noticed ADO adapter ask me database name, database password, conector version, so on.
Must I configurate ADO adapter first and then MySQL adpater to store data in a MySQL database?

Regards

4

The MySQL adapter was a very simple proof-of-concept adapter written during the openPDC’s infancy. It was never really intended for production deployment. Rather it was just a simple-as-you-can-get code sample to use as a template for custom adapter development. Therefore, I really can’t recommend using it for what you are trying to do and would instead suggest using the ADO adapter, even for MySQL.

As for your question about why it doesn’t ask for certain configuration parameters, the answer is simply that this adapter was written well before we implemented the openPDC Manager. The property annotations we use to describe the connection string parameter attributes didn’t exist at the time of development, and we never went back to update the code since then.

1 Like
5

Thanks for reply.

Well, I’ll use ADO Adapter.

I’ve made my conection string and I don’t had error ( by now), but when I open my database I don’t have information.

Stephen, How could I begin to save data to my MySql database??
Can you guide me with that please??

PMU’s data arrive to my openPDC ( I know because I can watch it in openPDC’s graphs).

Regards.

6

First you create this table in your MySQL database:

CREATE TABLE TimeSeriesMeasurement
(
    SignalID NCHAR(36) NOT NULL,
    Timestamp VARCHAR(24) NOT  NULL,
    Value DOUBLE NOT NULL
)

Then you tweak the following configuration to work for you. This was designed to work as a Historian Instance, replacing the default PPA archive.

Acronym: PPA
Name: Primary Phasor Archive
Type Name: AdoAdapters.AdoOutputAdapter
Assembly Name: AdoAdapters.dll
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

And with a slight adjustment, you should be able to use this as a secondary archive instead by setting it up as a Custom Output Adapter. Note that I have not tested this particular configuration, but it should work in theory.

Acronym: SPA
Name: Secondary Phasor Archive
Type: ADO
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

7

Hi
I’m so confusing :frowning:
Do I need to configurate two file to link to my database??

8

No, you can configure the MySQL database as your primary archive using the Outputs > Historian Instances page in the openPDC Manager, or you can set it up as a secondary archive using the Outputs > Manage Custom Outputs page. Either way, you need to create the TimeSeriesMeasurement table, but you only need to enter the configuration for one of either the primary archive or the secondary archive.

Also, you shouldn’t need to create any files in order to do this work. Just use your MySQL tools to create the database and then use the openPDC Manager for the rest.

9

Hi Stephen,

I’ve got this setup in my system:

1.- Credentials in my database are:
server: localhost
name: MyDatas
user: root
pass: sincrofasor#1

2.- Inside I’ve made this table:

 CREATE TABLE TimeSeriesMeasurement
(
    SignalID NCHAR(36) NOT NULL,
    Timestamp VARCHAR(24) NOT  NULL,
    Value DOUBLE NOT NULL
)

I’ll use next secondary archive :

Name : DATOS_OPENPDC
Type : ADO
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= MyDatas ; Uid= root ; Password= sincrofasor#1 }; TableName= TimeSeriesMeasurements ; IDFieldName= SignalID ; TimestampFieldName= Timestamp ; ValueFieldName= Value ; InputSourceIDs= PPA04

When I made a query to my database I don’t get nothing.

Which are my errors?

10

I see at least one error here:

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= MyDatas ; Uid= root ; Password= sincrofasor#1 }; TableName= TimeSeriesMeasurements ; IDFieldName= SignalID ; TimestampFieldName= Timestamp ; ValueFieldName= Value ; InputSourceIDs= PPA04

According to your post, you called the table TimeSeriesMeasurement, singular and not plural.

That said, there could be other errors in your configuration that I can’t see from what you posted. For instance, why did you change the InputSourceIDs to PPA04? Is that really the name of your primary historian instance? Also, are you really using MySQL Connector/NET version 6.9.5? It’s a pretty old one, and I’m not sure you can even download it anymore.

For troubleshooting, I recommend using the openPDC Console to interact with the openPDC service to see errors and double-check the runtime configuration of your adapter. Hint: try using the list /o ADO command to see if it has any input measurements.

11

Stephen;

When I initial ADOAdapter I’ve got this error by console:

[PPA04] Could not load file or assembly ‘MySql.Data, Version=8.0.17, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ or one of its dependenties.

12

It’s saying that it couldn’t load the MySql.Data assembly (MySql.Data.dll) provided by the MySQL Connector/NET adapter. You may need to get the .NET & Mono build of MySQL Connector/NET which is labeled “Architecture Independent”, as the Windows version appears to be labeled x86/32-bit. It’s a no-install version so the assemblies won’t go into the global assembly cache. You’ll have to copy the assemblies into the openPDC folder.

13

Ok,

Well I’ve found MySql.Data.dll inside my MySql Connector and I’ve copy it inside openPDC folder, but I still have same error :frowning:

14

For MySQL Connector/NET, did you use the .NET & Mono build that is architecture independent, or the Windows installer that is 32-bit only?

15

Only Windows installer 32-bit

16

You still need the other one, even if you already have the 32-bit one installed.

17

I’ve donwload Mono from https://www.mono-project.com/download/stable/ and I’ve installed but it still doesn’t work

What could I do?

18

I’m talking about MySQL Connector/NET. You go to the download page, select .NET & Mono from the dropdown, and then download the zip file from there.

Here, I’ve done it myself and obtained the download link. Just use this link to download the correct version of MySQL Connector/NET that will work with 64-bit applications like the openPDC.
https://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-8.0.18-noinstall.zip

19

Stephen, I’ve downloaded it, I copied and I pasted all files inside openPDC folder but it still doesn’t work.

By the way, openPDC folder has another folders… Do I have to copy files in any that folders?

PD: inside openPDC folder I have next folders:
Statics
Logs
DatabaseScripts
ConfigurationCacheArchive

20

OpenPDC console:

openpdcerror
openpdcerror440×680 135 KB

OpenPDC Manager:
MyConnec
MyConnec1251×685 288 KB

When I press initialize button from openPDC I’ve got that error in openPDC console.