Query tags vs transaction manager vs system.db.runScalarQuery

Ignition
1

hi
I am trying to organize my understanding regarding topics mentioned in the title.
Please feel free to comment and share your knowledge/understanding below topics

1 Query tags might be putting significant load on database so ideally reasonable refresh rates (eg. 2s-5s but not 100ms should be used). Ideally event triggers should be used for query tags and there are 2 possibilities for these events-
version a - if the event originates from the ignition it can set the trigger tag to true
version b - if the event is external to ignition and writing data directly to the database how would this trigger work? Will it need to be linked from the database via transaction manager to ignition memory tag and then used as event trigger for the query tag?
2 when retrieving data from the database is transaction manager preferred option than query tags performance wise? any other fundamental major differences?
3 is command system.db.runScalarQuery synchronous or asynchronous? If synchronous in the line below runScalarQuery read in python script i could assign the value to the tag value - is this correct?

2

Query Tags can definitely put a significant load on your database if not managed properly.

I'm not sure I understand your two event examples well enough to comment. I can only say that if I have an external process which is changing the database and I want to insure Ignition has that data, then I would also set a trigger tag from the external process and force Ignition to update.

It really depends on what you are trying to accomplish.

system.db.runScalarQuery is synchronous. You could assign the value to the tag value on the same line if you wanted.

system.tag.writeBlocking(['tagPath'],[system.db.runScalarQuery('Query','Database')])

I would however caution you to use the prepared query versions of the system.db.* query functions. In this instance system.db.runScalarPrepQuery.

You have also left Named Queries off your list.

1 Like
3

thanks a lot. I am bit biased towards named queries and i am not convinced about the real gain from them. They are meant to be safer but if I have a system communicating in one factory then in theory if hacker gets in to the network he/she can do a lot of damage anyway. Any thoughts on that please? I might be missing something fundamental please feel free to comment anything.

4

i am interested if you could share any more info on the above please

5

That's a Red Herring. If a hacker gets into the network he/she can do a lot of damage, doesn't matter if you have used Named Queries or not. If they get in, you have bigger issues.

Yes, named queries are "safer" as their parameters are sterilized (unless you use a query string parameter), but that is only 1 advantage.

  • They centralize the query, which means you don't have multiple copies of the same query running around
  • They insure data type conversion.
  • They incorporate the Table Browser into the UI which helps prevent typos in table and column names
  • They have a testing section allowing you to test your query by itself, which is useful for initial creation and troubleshooting.

Prep queries are safer. Any time you have user input being incorporated into a query you should use a "Prepared" Query. They help to prevent SQL Injection attacks. They have many of the same advantages that Named Queries have.

2 Likes