# Configuration to get data from database **URL:** \ **Category:** openPDC\ **Created:** [October 14, 2019, 1:42pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583 "2019-10-14T13:42:30Z")\ **Posts on this page:** 13\ **Page:** 2
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 6, 2019, 3:22pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/21 "2019-11-06T15:22:38Z")
If you downloaded the link I sent you, the version you should be using is 8.0.18, not 8.0.17. You’ll need to change your connection string accordingly. ---
**Author:** ![juanjo](http://discussions.gridprotectionalliance.org/letter_avatar_proxy/v4/letter/j/94ad74/32.png) [@juanjo](http://discussions.gridprotectionalliance.org/u/juanjo)\ **Post date:** [November 6, 2019, 3:59pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/22 "2019-11-06T15:59:18Z")
Stephen I’ve copied and pasted files from [https://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-8.0.17-noinstall.zip](https://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-8.0.17-noinstall.zip) inside openPDC folder and you remember I’ve installed Windows installer too. So, now I’ve followed all your tips but I still not have success. OpenPDC console send me same errors that you can watch in screenshot above. ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 6, 2019, 4:12pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/23 "2019-11-06T16:12:07Z")
One more thing, have you tried restarting the openPDC service after copying the assemblies for version 8.0.17 into the openPDC directory? ---
**Author:** ![juanjo](http://discussions.gridprotectionalliance.org/letter_avatar_proxy/v4/letter/j/94ad74/32.png) [@juanjo](http://discussions.gridprotectionalliance.org/u/juanjo)\ **Post date:** [November 6, 2019, 4:57pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/24 "2019-11-06T16:57:32Z")
I’ve rebooted computer and now openPDC service has troubles ---
**Author:** ![juanjo](http://discussions.gridprotectionalliance.org/letter_avatar_proxy/v4/letter/j/94ad74/32.png) [@juanjo](http://discussions.gridprotectionalliance.org/u/juanjo)\ **Post date:** [November 6, 2019, 5:51pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/25 "2019-11-06T17:51:33Z")
I’ve setup it again and now openPDC works fine 🙂 I attach a screenshot of my new results ![funcionalago](http://discussions.gridprotectionalliance.org/uploads/default/original/1X/25af28826e74ad9fc21b95787bc9949bd84d6fd6.png) I dont know if it work yet, but I’ve got new results. When I open database I watch that data doesn’t store there ☹ PD: There was a moment when openPDC send [CRITICAL], so I’ve disable connector for any problem. ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 6, 2019, 6:03pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/26 "2019-11-06T18:03:25Z")
The error message in your screenshot indicates either that the ADO output adapter is not able to send data to MySQL as fast as it is being received or that the output adapter is only queuing data and is not processing, perhaps due to an error during adapter initialization. Leaving the adapter on should not cause problems because it will be able to dump data from its queue before using up all your system memory, but you will continue to lose data until you can figure out a way to speed up the process. ---
**Author:** ![juanjo](http://discussions.gridprotectionalliance.org/letter_avatar_proxy/v4/letter/j/94ad74/32.png) [@juanjo](http://discussions.gridprotectionalliance.org/u/juanjo)\ **Post date:** [November 6, 2019, 6:57pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/27 "2019-11-06T18:57:47Z")
Stephen Now I’ve got next errors ![error](http://discussions.gridprotectionalliance.org/uploads/default/original/1X/58218e725de2a28c1c988f23741d2d4f1c8b6a7a.png) Do I have NaN values from data arrives or Adaptor has problems? Other question, When I make a new ADOAdapter it has a new RuntimeID. How can I watch if RuntimeID is working?? Can you say me where can I find commands for openPDC console, please? ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 6, 2019, 9:25pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/28 "2019-11-06T21:25:33Z")
The only thing I can think is that you must have NaN values. The ADO adapter should be converting your measurements into SQL queries that look something like this… ``` INSERT INTO TimeSeriesMeasurement(ID, Timestamp, Value) SELECT @p0, @p1, @p2 UNION ALL SELECT @p3, @p4, @p5 UNION ALL ... ``` The `@p#` things in that query are parameters for a parameterized query. Likely, one of the parameters is a `NaN` value and the query is interpreting that as selecting the NaN column, which couldn’t possibly exist without a FROM clause, but really shouldn’t exist anyway since we’re only dealing with raw values here. Honestly, I wouldn’t have thought it possible if we were dealing with SQL Server, but I guess MySQL allows you to use parameter values as column names in your queries? Regardless, since MySQL doesn’t support storing NaN in the database, you’ll have to either ensure that your input data can never be NaN or modify the AdoOutputAdapter code. As for the runtime ID, use the `list /o` command to see what your output adapter runtime IDs are. For a list of console commands, issue the `help` command. Use the `/?` modifier to get help using a specific command (for example, `list /?`). ---
**Author:** ![juanjo](http://discussions.gridprotectionalliance.org/letter_avatar_proxy/v4/letter/j/94ad74/32.png) [@juanjo](http://discussions.gridprotectionalliance.org/u/juanjo)\ **Post date:** [November 7, 2019, 3:02pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/29 "2019-11-07T15:02:29Z")
So, I have some options to solve it. About: `Regardless, since MySQL doesn’t support storing NaN in the database, you’ll have to either ensure that your input data can never be NaN or modify the AdoOutputAdapter code.` How can I modify it? Do I have to do it in ConnectionString? Another question is about add values to my database. You wrote it: BulkInsertLimit=500; DataProviderString={ AssemblyName={MySql.Data, Version=8.0.17, 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= TimeSeriesMeasurement ; IDFieldName= SignalID ; TimestampFieldName= Timestamp ; **ValueFieldName= Value** ; InputSourceIDs= PPA If I want to add more data, How can I add it and how can I indicate which one (voltage, angle, so on)?? PD: I’ve noticed in connectionString a parameter **InputMeasurementKeys** and it has measureaments. Maybe there I must to get speficical data. (¿?) ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 7, 2019, 7:27pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/30 "2019-11-07T19:27:36Z")
The code is here: [https://github.com/GridProtectionAlliance/gsf/blob/master/Source/Libraries/Adapters/AdoAdapters/AdoOutputAdapter.cs](https://github.com/GridProtectionAlliance/gsf/blob/master/Source/Libraries/Adapters/AdoAdapters/AdoOutputAdapter.cs) To modify the code you would have to download the Grid Solutions Framework source code, modify the AdoOutputAdapter.cs file, build the AdoAdapters project, locate the AdoAdapters.dll file in the build output, and then copy/paste that file into the openPDC installation folder, replacing the one that gets installed with the openPDC. * * * As for your question about adding values, the `InputSourceIDs=PPA` parameter ensures that your adapter receives all the same measurements as your primary phasor archive. If you’d like to add additional measurements, you can add them to your primary phasor archive by setting the `Historian` field on the `Metadata > Measurements` page of the openPDC Manager. You can also use the `InputMeasurementKeys` property to explicitly add them to your ADO adapter without adding them to your primary phasor archive. ---
**Author:** ![juanjo](http://discussions.gridprotectionalliance.org/letter_avatar_proxy/v4/letter/j/94ad74/32.png) [@juanjo](http://discussions.gridprotectionalliance.org/u/juanjo)\ **Post date:** [November 8, 2019, 10:12pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/31 "2019-11-08T22:12:30Z")
Stephen, I’m talking about add more values to my database using ConnectionString. I know that PPA has all measurements (voltages, angles and so on) but I don’t know how ConnectionString interprets measurements. You wrote me one ConnectionString example just has three parameters: IDFieldName , TimestampFieldName and ValueFieldName, but **ValueFieldName** I don’t know what is (voltage, angle, frecuencie and so on?) Now I’ve create another Table has more colummns. I’ll write it: ``` CREATE TABLE MyMeasurement ( SignalID NCHAR(36) NOT NULL, Timestamp VARCHAR(24) NOT NULL, VPhase DOUBLE NOT NULL, APhase DOUBLE NOT NULL, Frequencie DOUBLE NOT NULL, ) ``` Now, I want to tell to connectionString writes into new table my new parameters. PD: **Voltage phase A** , **Angle phase A** and **Frecuencie** How can I do it? Regards ---
**Author:** ![StephenCWills](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/stephencwills/32/20_2.png) [@StephenCWills](http://discussions.gridprotectionalliance.org/u/StephenCWills)\ **Post date:** [November 11, 2019, 4:24pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/32 "2019-11-11T16:24:03Z")
You can’t do it, because that would require concentration which is not supported by the ADO adapter. The expectation is that you’d handle concentration after you have archived your data. Here’s a simple example, based on various assumptions, that uses SQL JOINs to concentrate the TimeSeriesMeasurement table. ```auto WITH tsm AS ( SELECT SignalType.Acronym AS SignalType, TimeSeriesMeasurement.Timestamp, TimeSeriesMeasurement.Value FROM TimeSeriesMeasurement JOIN Measurement ON TimeSeriesMeasurement.SignalID = Measurement.SignalID JOIN SignalType ON Measurement.SignalTypeID = SignalType.ID ) SELECT FREQ.Timestamp, VPHA.Value AS VPhase, IPHA.Value AS APhase, FREQ.Value AS Frequencie FROM tsm FREQ JOIN tsm VPHA ON FREQ.Timestamp = VPHA.Timestamp JOIN tsm IPHA ON FREQ.Timestamp = IPHA.Timestamp WHERE FREQ.SignalType = 'FREQ' AND VPHA.SignalType = 'VPHA' AND IPHA.SignalType = 'IPHA' ORDER BY FREQ.Timestamp ``` ---
**Author:** ![Andre\_Santos](http://discussions.gridprotectionalliance.org/user_avatar/discussions.gridprotectionalliance.org/andre_santos/32/856_2.png) [@Andre\_Santos](http://discussions.gridprotectionalliance.org/u/Andre_Santos)\ **Post date:** [September 29, 2023, 10:55pm UTC](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583/33 "2023-09-29T22:55:48Z")
Hi, I´m still having troubles configuring this ADO. I´ve created the table, setted the connection string, downloaded .net mysql\_data and put it in openpdc dir, and get the following errors: [9/29/2023 7:44:48 PM] (Inner Exception) Date and Time: 9/29/2023 7:44:48 PM Machine Name: GETPDT01 Machine IP: ::1 Machine OS: Microsoft Windows NT 6.2.9200.0 Application Domain: openPDC.exe Assembly Codebase: C:/Program Files/openPDC/openPDC.exe Assembly Full Name: openPDC, Version=2.9.42.0, Culture=neutral, PublicKeyToken=null Assembly Version: 2.9.42.0 Assembly Build Date: 5/15/2021 12:14:12 AM .Net Runtime Version: 4.0.30319.42000 Exception Source: AdoAdapters Exception Type: System.NullReferenceException Exception Message: Object reference not set to an instance of an object. Exception Target Site: AttemptDisconnection ---- Stack Trace ---- AdoAdapters.AdoOutputAdapter.AttemptDisconnection() openPDC.exe: N 00021 GSF.TimeSeries.Adapters.OutputAdapterBase.Stop() openPDC.exe: N 00108 (Outer Exception) Date and Time: 9/29/2023 7:44:48 PM Machine Name: GETPDT01 Machine IP: ::1 Machine OS: Microsoft Windows NT 6.2.9200.0 Application Domain: openPDC.exe Assembly Codebase: C:/Program Files/openPDC/openPDC.exe Assembly Full Name: openPDC, Version=2.9.42.0, Culture=neutral, PublicKeyToken=null Assembly Version: 2.9.42.0 Assembly Build Date: 5/15/2021 12:14:12 AM .Net Runtime Version: 4.0.30319.42000 Exception Source: Exception Type: System.InvalidOperationException Exception Message: Exception occurred during disconnect: Object reference not set to an instance of an object. Can you guys clarify what and how can I workaround ?? [Previous page](http://discussions.gridprotectionalliance.org/t/configuration-to-get-data-from-database/583.md?page=1)