We have a 3rd party data collection system, which is backed by a MySql database. We have established a connection to this database inside of the Gateway, allowing us to browse its contents inside of the Designer.
The question: What would be the best approach to getting this external data into tags, or another mechanism which supports history, within Ignition?
We briefly looked at Transaction Groups, but are unsure how to execute that in this scenario.
First off what version of Ignition?
Second what kind of data are you needing?
If you want to show the data in a table you can do that w/o needing to put anything into tags.
If you want the last value for a specific piece of information, you can use a query tag type and query for the information that you need to have it show up in a tag.
8.1.0 (tag)
The data contains various machine sensor readings.
Ultimately, we we want this in tag form to allow for easy ‘historization’ and querying. Will update the question to reflect that.
So then you would use the query tag type.
What does the table look like you are querying? Just a sample…
This sounds very strange. It's already in a database in historical form? If so, just query that database for trends and analysis. A transaction group that keeps tags refreshed with latest value is the most I'd recommend.
We are in the process of determining exactly which tables we would like to ‘pull in’, however, here is one that we believe will be included. Not all columns would be needed, but hopefully this conveys what we’re working with.
You are correct. It does appear to already be historical in the source database. The second part, having this available as an Ignition tag(s), is where the main question is. I mentioned that we briefly looked at Transaction Groups, but need a little direction in executing. Could this be accomplished within a standard transaction group? What would be the Update Mode? Thanks.
I’m still confused at how the data is currently organized. In your sample are all sensors in one table with current values? Or one table per sensor?
Inquiring minds…
Yes, a standard transaction group would do fine, with the direction on the items set to DB=>OPC (not really opc for memory tags, naturally). If you have large, repetitive hierarchy, a script that pulls in all the latest values in a single query would likely be more efficient than many transaction groups. (And you could avoid the $$ for the SQL Bridge module.)
Thanks Phil. Just so I understand, the Transaction Group using DB=>OPC, will create a table, group_table in this case, on the External Database?
As for scripting, I can think of a few of ways.
have the script call a named query which would basically execute a merge of the latest values into an internal table, which tags could then be pointed to.
have the script execute a named query which returned the latest values, then have the script set the appropriate tags.
use straight up query tags to get each desired, current data point.
Did you have another approach in mind?
No, don’t let a transaction group create your table or store a timestamp. Your table(s) already exist(s) and have/has a datetime column. You’d use a where clause to select the latest value for each item.
But scripting probably is better. A query would join against a grouping subquery of max(timestamp). This would yield the latest value and timestamp for every unique item in your database. The script would loop through these results building a list of tag writes, constructing each tag path from the other columns in the table. The SQL would look something like this:
SELECT alias.*
FROM "myTable" alias INNER JOIN (
SELECT ItemName, MachineNo, PresetNo, SequenceNo, max(DateTime) As mdt
FROM "myTable"
GROUP BY ItemName, MachineNo, PresetNo, SequenceNo
) latest ON alias.ItemName = latest.ItemName AND alias.MachineNo = latest.MachineNo AND
alias.PresetNo = latest.PresetNo AND alias.SequenceNo = latest.SequenceNo AND
alias.DateTime = latest.mdt
Ok, I’m with you. That makes sense. From a scheduling standpoint, would you recommend using a Gateway Timer Script?
Yes. You might also cache the timestamps (in a project script top level dictionary or similar jython object) to trim the query to only recent changes.