Help/advice with data from SQL

Hi to all,

I have a project in the ignition perspective, this is my first project with the ignition. I need help/advice.
All my tags are from SQL database, I will have thousands and thousands of tags, I am thinking about the best way for reading the tags. To use query tags, in my opinion, will be ok for some smaller number of tags but not for thousands of the tags.
My idea is to use SQL Query tags and to read the complete table as data set, to do that for each table from SQL that I need, then to fire script in which I will rearrange data from ignition tags, based on my needs, and write it to ignition memory tags. And all my components (almost all) will use memory tags. In some case that will be data set memory tags and in some cases that will be UDT structures… Ok, when is possible I will use query tags (data set), I will not use memory tags. Is it ok, approach? I am not sure how ignition mange scripting (executes scripts) in parallel with other processes.


There are multiple ways to do what your talking about. Also I agree you don’t want that many query tags.

One is like you described using a query tag to pull a dataset then using a script to write to memory tags. That would be fine but you would have to decide where to do the script. You can do it as a tag change script on the query tag that runs the script on value change or you can do a gateway script to do it. I don’t know if doing it from a tag change script would get a dedicated thread. If you use a timer script though you can have it use a dedicated thread so it doesn’t stop other processes while it runs.

The other way to do it is to use expression tags and do a lookup.

Personally I try to use expressions before writing scripts. They are normally easier for someone that comes after you to understand. But either option I think should work fine.

Depending on what data you need from the database and for what purpose it might be worth using bindings on your components.
Or try to get all necessary information from your database during navigation (In the navigation script) and passing that data along to the new window as parameters for custom properties.

Keep in mind that you could simply bind a query to a component and then use transforms to “transform” your data into the structure you need.

In the end you want to avoid running too many queries for information that’s mostly static.

What are you planning on using the data from the database for?

I would absolutely use a timer script. Read all tags’ current values from SQL in a single operation per datatype. Make sure the query result includes a column containing the memory tag path for each value. Then construct a list of tagpaths and a list of values from the query result, and pass that to system.tag.writeBlocking() (v8) or system.tag.writeAllSynchronous() (v7.9).

Thank you very much for your answer. I will also consider expression tags and lookup. Good idea.

Thank you for your answer. I am not in the team that writing SQL database, what means that it is hard for me to control and change SQL tables. It’s good to know that that solution also exists. Thanks.