Db tags based on sql query

Ignition
1

Is it a good practice to use these? My polling software stores some historical info into a separate database instance than what ignition uses. I was just wondering if it would be a good idea to use this to bring that data into ignition instead of using a transaction group to move the data from the historical table instance into ignitions instance. It only updates 1 time per day, but Ill have about 12000 of these tags.

2

How is the data laid out, and how do you want to use it?

Using db tags with seperate queries probably isn’t the way to go (you’d have 12000 tags and 12000 queries being executed)… but the best way will probably depend on what you want to do. In general, you’d probably be better off bringing the data in to a dataset based dynamic property and binding to that, if you wanted to display them on a screen.

Regards,

3

The data is laid out in a table. my polling server collects the data stored on the device and puts it into a table. this is what the query looks like for 1 tag. meterid will be different for each device, and the dataid is the identifier for 1 of the 6 values that I need to grab for each device.

[color=#008040]SELECT Top 1 DataValue FROM EFM_Daily_History WHERE (MeterID = 1) AND (DataID = 1010) order by timestmp desc[/color]

Id like to display them on a screen for each device, thats why I was thinking that doing it on the tag might work a little better, especially since I am using indirect’s on the screen. 6 tags per device, approx 2000 devices.

I also have to write them to a separate table to make them available to a different program.

These tags only update once per day, and the times that they update will be staggered, like every 5 minutes or something.

My server gives me a string value with the date and time of the last data grab, If I could figure out a way to trigger off of that I could make it trigger only when it updates, which would be staggered like I just mentioned.

4

Ok, I think the much easier thing to do would be to use External SQLTags, and basically create something of your own “driver” that simply copies values across periodically. The query to do this could be run by a group or DB tag on a slow scan class.

Here are the coarse steps:

  1. Set up a external SQLTags provider in Ignition. You might need to add a “driving” provider and a sample tag in order to get it to generate the tables for you. For other reasons, which I’ll talk about below, I would recommend creating a ‘driving provider’ anyhow. For the queries to follow, I’m using the driver name of ‘dbdriver’.
  2. To help later, I would add two custom columns to the sqlt_core table: ‘meterid’ and ‘dataid’, both ints.
  3. Create the tags in the table. This should be easy to do automatically with the right query. Something like:
INSERT INTO sqlt_core(name, path, drivername, tagtype, datatype, enabled, scanclass, meterid, dataid) SELECT 'Data'+cast(DataId as varchar), 'Meter'+cast(MeterID as varchar)+'/', 'dbdriver', 1, 5,1, 0, MeterId, DataId FROM EFM_Daily_History
  1. With that done, you just need to update the values periodically:
UPDATE sqlt_core s SET s.float=d.datavalue, valuechange=CURRENT_TIMESTAMP FROM EFM_Daily_History d WHERE d.meterid=s.meterid and d.dataid=s.meterid

Now, some explanation:

  • I’m defining the tags as ‘db tags’ (1), with type Float8 (5). Got these values from looking at this whitepaper.
  • I suggested making a driving provider and making Ignition think it was driving the tags by using the same driver name, even though it’s not. Why? Because then you don’t have to worry about scan classes and stale timeouts. You might still need to just a bit, in that you need to make sure the scan class id is set to a scan class that exists, but Ignition will drive the scan class and you won’t have to worry about staleness.
    The driving provider allows value updates to come in from the outside for ‘static’ db tags, which is what we’ve defined.
  • You can add any columns you want in addition to the required ones, so by adding a column for meterid and dataid, I made it easy to write the update query.

I didn’t get a chance to actually do all of this, so something might be a bit off, but the idea should be good. You would take the query in #4 and put it in a DB tag or a group set to run once an hour or however much you want. With decent indexes it shouldn’t take long to run at all.

Regards,