openPDC to mySQL database


#1

Hello All,

i have a question about the Measurement GUID in the openPDC Manager. How can somebody change the ID of the measurement data e.g. frequency ID 3436e402-49b6-11e6-b909-f0def10300b3 (!!) ? It is namely not very convenient to write this long id in each SQL query. Except of that, is it possible to save measurements in different tables with one historian? Or should i create more than one historians and in this way save the measurements in different tables in the openpdc database?

Thanks in advance!

Best regards,
Eva


#2

Hello Eva,

First, I’m going to link to this discussion where your question first appeared, because I believe it’s important to note my assumption that you’re probably using an AdoOutputAdapter to push data into MySQL.

I would recommend against changing the SignalID. Many of the associations in the database and across systems use this as a globally unique identifier for the measurement, so changing it may cause some issues. Furthermore, changing it to something other than a GUID would outright break the openPDC.

That said, the intent behind the SignalID was to provide a unique identifier which could be used reliably by automated processes, but not necessarily so it could be used by humans. There may be better ways to handle your data than to query the SQL database directly using the SignalID as your criterion. As an example, consider the following query to get the 50 most recent frequency values.

SELECT
    SignalReference,
    Timestamp,
    Value
FROM
    TimeSeriesMeasurement JOIN
    Measurement ON TimeSeriesMeasurement.SignalID = Measurement.SignalID
WHERE SignalReference LIKE '%-FQ'
ORDER BY Timestamp DESC
LIMIT 50

If your TimeSeriesMeasurement table is in a separate database from your Measurement table, you may need to craft cross-database queries or replicate your Measurement table in both databases.

If you need some help writing SQL queries, you can ask about it on these discussion boards.

Thanks,
Stephen


#3

Hello Stephen,

yes i am using Ado adapter and my timeseriesmeasurement table is in the openpdc databease like the measurements table, so i will use the signalID as you suggested.
Thanks again!

BR,
Eva