I'm having some trouble here and I would appreciate any suggestions and hints.

Background: I have successfully connected to a SEL-451 PMU with openPDC and receiving data from the synchrophasor .I used the instructions in the wiki and successfully created a node a historian and the device with the respective measurements defined.

Objective : I need to save the measurements in some format , preferably in a database.

What I understand so far : This must be done through an output stream from the openPDC manager. Th documentation in the wiki is a bit vague on that matter , so would you please provide a short guide or some directions on how something like this can be accomplished ? I tried to set for example a connection string in the form of "file=\path\to\acsvfile" in the output connection settings as described here but no luck. I also saw the respective connection string settings for a mysql database but what abouse the target database ? Should it be created beforehand ? and if yes which would be the necessary tables ?

If I understand correctly the received measurements are saved locally by the Historian in the Archives subfolder in the installation directory. Is it a valid assumption that I could use the historian playback utility to export the measurements from there to a csv file and use that to import them in a database ? I would prefer if there is a way to use a more automated way but it is not bad as a last resort.

Thank you in advance for your time and answer

Yiannis Kakavas

You can archive data directly to a database instead of the local historian if useful, there is a MySQL Output Adapter and an ADO Output Adapter for any database. Their connection strings are defined below.

MySQL Output Adapter 

 ADO Output Adapter

The relevant historian fields are defined here:

Historian Fields

Defining the Historian in openPDC Manager

Ritchie

ritchiecarroll wrote:

You can archive data directly to a database instead of the local historian if useful, there is a MySQL Output Adapter and an ADO Output Adapter for any database. Their connection strings are defined below.

MySQL Output Adapter 

 ADO Output Adapter

The relevant historian fields are defined here:

Historian Fields

Defining the Historian in openPDC Manager

Ritchie



 

Hell Ritchie, thanks for the timely response. 

What you are suggesting would be ideal for what I need to do, but still I didn't manage to get it working .

a) First I tried with the Mysql output adapter, I created the historian with the following values :

TypeName = MySqlAdapters.MySqlOutputAdapter

AssemblyName = HistorianAdapters.dll

ConnectionString : server=mysql_server_ip;port=3306;protocol=tcp;database=openpdcmeasurements;uid=myusername;pwd=mypassword 

Assigned the historian to the device, reinitialized the device, no luck ..

Should I create the table in the database manually beforehand ? If yes what would be the necessary columns ( can they be defined through openPDC ?) 

b) With the generic AdoOutputAdapter :

TypeName = AdoOutputAdapter

AssemblyName = HistorianAdapters.dll

ConnectionString : tableName=picomp;connectionString={server=mysql_server_ip;port=3306;protocol=tcp;database=openpdcmeasurements;uid=my_username;pwd=my_pass};dataProviderString={AssemblyName={MySql.Data, Version=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d}; ConnectionType=MySql.Data.MySqlClient.MySqlConnection; AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter}

Still nothing ..

The database exists, the server is reachable and the user has all rights on the database schema. Any ideas on what I might be doing wrong or if there is something else that needs configuring ?

Your assembly name is the issue. Use MySqlAdapters.dll for the MySQL input and output adapters and the AdoAdapters.dll for the ADO input and output adapters.

Also, yes, you need a database table to hold the destination data: see schema.

I'll see if we can add the assembly names to the connection string parameters for extra clarity.

Thanks!
Ritchie

Thanks for all the help Ritchie ,

Everything works as it should now :)

Yiannis

 I would appreciate your help on following issue. I have database connection setup table name / id / signal id / value but default definition doesn't work my OpenPDC console returnds unknown @TAG if i put tag unknown @NAME ... etc and SQL tracer dispalys  INSERT INTO Measurements(TAG,NAME,VALUE) VALUSE (@TAG,@NAME,@VALUES) it is something i have to define on measurement page as a name!?  Thanks.

I am assuming you are wanting to use the ADO data export adapter, if so you will need a predefined database schema with a time tag, a point name and a value.

The goal of the ADO Output Adapter is to be able to use any database as an archive source (e.g., SQL Server, MySQL, Oracle or even OSI-PI via ODBC) - it is designed to be flexible, but has a few requirements for use:

1. The connection string needs to define a table name in which to store the data.
2. This table needs to have fields to hold measurement data - possible fields are:
   1. an integer based ID field
   2. a Guid based SignalID field
   3. a string based TagName field
   4. a string based data Source field
   5. a Timestamp field (can be a date/time or long integer)
   6. a boolean TimestampQualityIsGood field
   7. a boolean ValueQualityIsGood field
   8. a double-precision Value field
   9. Any other IMeasurement properties can be defined as well
3. You are only required to define one field for identification (one of "ID", "SignalID" or "TagName"), then a timestamp field and a value field - other fields are optional
4. The database field names can be any name, the connection string will map the property name to the field name, for example:
   1. tableName=DataValues; IDFieldName=PointID; TimestampFieldName=Time; ValueFieldName=Value; timestampFormat="dd-MMM-yyyy HH:mm:ss.fff"
5. A database connection string must be provided in the connection string parameter called dbConnectionString, for example:
   1. dbConnectionString = { Data Source=localhost\SQLEXPRESS; Initial Catalog=openPDC; Integrated Security=SSPI };
6. A database provider string must be provided to tell the system which ADO assemblies and types to use for the connection, for example:
   1. dataProviderString = { AssemblyName={System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089}; ConnectionType=System.Data.SqlClient.SqlConnection; AdapterType=System.Data.SqlClient.SqlDataAdapter };
7. You can look in the openPDC.exe.config settings file under the <exampleConnectionSettings> section for examples of database connection and provider strings.
8. You would then add a new historian output adapter from the openPDC Manager (accessible via Adapters / Historians menu) or the Configuration Setup Utility using:
   1. Type Name = AdoAdapters.AdoOutputAdapter
   2. Assembly Name = AdoAdapters.dll
9. Once enabled, you should be able to start using your ADO data source as a historian for the openPDC :-)

Hope that helps!

Ritchie

this is my connection string =

         tableName=Measurement; dbConnectionString={DSN=OpenPDCSQLDataCollector;Uid=xxx;Pwd=xxx}

and this is the oputput from OpenPDC console:

       must declare scalar variable @TAG

and this is SQL Tracer info:

      INSERT INTO Measurement(TAG,TIME,VALUE) VALUES (@TAG,@TIME,@VALUE)

and table scema:

    CREATE TABLE Measurement (
    SignalID NCHAR(36) NULL,
    Timestamp BIGINT NOT NULL,
    Value float NOT NULL
);

  if i include: SignalID (ID)FieldName=PointID; TimestampFieldName=Time; ValueFieldName=Value

  console saying: incorrect cast...!?

Please, advice.

Thanks

Based on your data source name definition in the connection string it looks like you are trying to use ODBC to get data into your database. I cannot tell by the schema above what kind of database you are trying to connect to, but ODBC should only be used as a last resort. There are .NET ADO drivers for most any database. For example, based on your schema if you are using MySQL your connection string would look like this:

tableName=Measurement; SignalIDFieldName=SignalID; ValueFieldName=Value; TimestampFieldName=Timestamp; TimestampFormat=null; dbConnectionString={server=mysql_server_ip; port=3306; protocol=tcp; database=measurement; uid=my_username; pwd=my_pass}; dataProviderString={AssemblyName={MySql.Data, Version=5.2.7.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d}; ConnectionType=MySql.Data.MySqlClient.MySqlConnection; AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter}

Here are the connection strings for a few other databases:

    <exampleConnectionSettings>

      <add name="SqlServer.ConnectionString" value="Data Source=serverName;Initial Catalog=openPDC;User Id=userName;Password=password"

        description="Example SQL Server database connection string"

        encrypted="false" />

      <add name="SqlServer.DataProviderString" value="AssemblyName={System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089};ConnectionType=System.Data.SqlClient.SqlConnection;AdapterType=System.Data.SqlClient.SqlDataAdapter"

        description="Example SQL Server database .NET provider string"

        encrypted="false" />

      <add name="MySQL.ConnectionString" value="Server=serverName;Database=openPDC;Uid=root;Pwd=password"

        description="Example MySQL database connection string" encrypted="false" />

      <add name="MySQL.DataProviderString" value="AssemblyName={MySql.Data, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d};ConnectionType=MySql.Data.MySqlClient.MySqlConnection;AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter"

        description="Example MySQL database .NET provider string" encrypted="false" />

      <add name="Oracle.ConnectionString" value="Data Source=openPDC;User Id=username;Password=password;Integrated Security=no"

        description="Example Oracle database connection string" encrypted="false" />

      <add name="Oracle.DataProviderString" value="AssemblyName={System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089};ConnectionType=System.Data.OracleClient.OracleConnection;AdapterType=System.Data.OracleClient.OracleDataAdapter"

        description="Example Oracle database .NET provider string" encrypted="false" />

      <add name="OleDB.ConnectionString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=openPDC.mdb"

        description="Example Microsoft Access (via OleDb) database connection string"

        encrypted="false" />

      <add name="OleDB.DataProviderString" value="AssemblyName={System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089};ConnectionType=System.Data.OleDb.OleDbConnection;AdapterType=System.Data.OleDb.OleDbDataAdapter"

        description="Example OleDb database .NET provider string" encrypted="false" />

      <add name="Odbc.ConnectionString" value="Driver={SQL Server Native Client 10.0};Server=serverName;Database=openPDC;Uid=userName;Pwd=password;"

        description="Example ODBC database connection string" encrypted="false" />

      <add name="Odbc.DataProviderString" value="AssemblyName={System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089};ConnectionType=System.Data.Odbc.OdbcConnection;AdapterType=System.Data.Odbc.OdbcDataAdapter"

        description="Example ODBC database .NET provider string" encrypted="false" />

    </exampleConnectionSettings>

Thanks!
Ritchie

  Thank you Richie,

       Actually i have no issues connecting to the database, but on mapping functionality since I don't fully understand it. You wrote: SignalIDFieldName=<SignalID>; ValueFieldName=<Value>; TimestampFieldName=<Timestamp>, is <SignalID> actual signal id or just an internal OpenPDC name for all signals. If you'll point me to C# module which does logic with ConnectionString for Historian i would appreciate it. Could i use my own xml configuration file to store data needed to connect to external source to collect measurements instead of typing ConnectionString on Historian page of OpenPDC Manager.

   I'm using MSSQL 2008 R2.

Regards,

DK

-----

 after careful reviewing documentation and code i've got this working except of TagName is there any special characters inside tag should i declare quote escape on tag name like TagNameFieldName = ''TagName''?

-----

P.s.s. All issues are solved!