This project has moved and is read-only. For the latest updates, please go here.

save measurements in mysql database

Jun 29, 2015 at 4:25 AM
Hi
I am new to openPDC.
I am able to receive data through openPDC Manager currently and these data are archived in C:\Program Files\openPDC\Archive, as D file.
My question is that I need to set up another way to archive these data in Mysql database. There are already some documentations and discussions about MysqlAdapters.dll, but I still not exactly know how to do.
Can I do this only through openPDC?
Is there any more documentation or guide in detail on this?

thanks.
Jun 29, 2015 at 6:57 PM
Hi TaoH,

You may have seen this already, but the following thread is probably our best resource on how to set up a MySQL archive for synchrophasor data.

http://openpdc.codeplex.com/discussions/571880

While it's possible to do, especially for smaller installations, we don't generally recommend using a relational database to archive synchrophasor data. As a result, we don't have any formal documentation on the topic.

Thanks,
Stephen
Jul 8, 2015 at 8:40 AM
Hi Stephen,

I am sorry for the late reply.
This is exactly what I need, write these measurement into Mysql database then fetch them by Matlab, thanks a lot.
I create a table under database openpdc first, then set up connection string under openpdc tool bar>outputs>Historians Instances, and I take AdoOutputAdapter as you recommend, but it's not work. Although I tried serveral days and looked for more documentation about AdoOutputAdapter, I don't know what's happened.
Here is what I did:

There is already a table called measurement in database openpdc, but definitions are different. so I changed the table name.

CREATE TABLE TimeSeriesMeasurement ( SignalID CHAR(36) NOT NULL, Timestamp BIGINT NOT NULL, Value DECIMAL(9, 6) NOT NULL);

Then, in the openPDC Manager, under Outputs > Historians Instances...

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=root; Password=mypassword }; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value
Load Order: 0
Reporting Interval: 100000
Local and Enabled are checked

The result is the table I created is always empty, and it seems that measurement is still saving into D file.

Thanks,
TaoH
Jul 9, 2015 at 3:22 PM
Hi TaoH,

For the best results, I would recommend the following table.
CREATE TABLE TimeSeriesMeasurement(
    SignalID NCHAR(36) NOT NULL,
    Timestamp VARCHAR(24) NOT NULL,
    Value DOUBLE NOT NULL
);
If you would prefer to use a BIGINT for your timestamps, be sure to add the timestampFormat=null to your connection string.

BulkInsertLimit=500; timestampFormat=null; 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=root; Password=mypassword }; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value

Note that in the other thread, I had assumed that these settings would be applied to the existing PPA historian and would therefore replace the .d file capture. After you make changes to your historian adapter, be sure to initialize the adapter. If you don't want to replace .d file capture, then you will need to set up one of these historians as a Custom Output Adapter instead.

Thanks,
Stephen