I noticed in standard transaction groups, one of the data types for a OPC/Group item is DataSet. How does one use this, seeing that I cannot create DataSet type SQL Tags? I have a window containing a table that displays the recipe currently assigned to a tank that is selected from a drop-down list. The recipe is obtained from a SQL query to Oracle. Rather than have each client (there could be dozens) re-query the database each time a different tank is chosen on the screen (and have to poll to know if another client has changed something on the recipe), we’d like to maintain the current recipe on the Gateway, using the same architecture/data flow used for individual OPC and DB SQLTags. Recipes can have varying amounts of instructions, so it’s difficult to maintain a static set of individual tags to represent this for each tank.
We currently have a stored procedure group for each tank that is used to retrieve, update, and increment through each individual step of the recipe. We would like to refresh the recipe display (they can and do change mid-batch when the lab adds instructions) each time the stored proc group posts an update to Oracle. It seems a bit wasteful and hurts scalability to have each client querying over and over when on distinct, known events it could be refreshed on the gateway. It appears the only types of SQLTags that can be DataSets are client tags, which cannot be used in transaction groups. I’m sure I could create client DataSet tags for each tank that only are updated when something changes on the Gateway, but I don’t know if this is an optimal solution either.
Assuming what I asked makes sense, does anyone have any suggestions?
Unfortunately, we can’t create DataSet SQLTags in Ignition. However, we are looking into creating Gateway Tags like Client Tags that are stored in memory but only execute on the Gateway and are available to all clients.
I understand what you’re trying to do, and it does make sense. Currently your best option is the one you’d already thought of - query the values in a Client-scoped SQLTag (which can be a dataset), and only update them on demand (perhaps whenever you write to the underlying table, you could also increment some DB-tag so that the clients could detect the change)
It would be a bit more efficient from the database’s perspective to be able to create Gateway-scoped dataset SQLTags, but like Travis said, this currently isn’t possible. The chief reason is that there isn’t a graceful way for us to store their values in an external database - we’d have to write them to XML and then compress it or something like that. This is somewhat at odds with our open-format style, but our options aren’t very good here.
Lastly - You should consider whether or not you’re optimizing prematurely. Even with dozens of clients it might not be all that much load on the database. It all depends on your poll rate, result size, and query complexity. Especially if you can make the polling only “on demand” by detecting when the underlying data has changed.
Thanks for the help, guys. Perhaps you are correct about optimizing too early. I’m just trying to keep all our bases covered. I’ll have to read up more on how the gateway pools db connections and manages larger client loads (many clients with possible tables and multiple trends running at the same time). However, if you don’t think it’s an issue, and with the possibility of gateway-side datasets is in the future, I think we’ll be OK.
Also, just a thought, although I’m sure you may have already considered it. Although still not totally “open-format”, could you serialize a dataset and store it as a BLOB in the database? If that’s possible, it seems like you could possibly store them as DB SQL Tags.
Anyway, thanks again. I’ll see you guys next week!