runNameQuerry in IGNITION

Is there a way to run a NameQuery without using any buttons?
I have a name querry an INSERT QUERRY
should run only when my tag is true and INSERTED into my database

Are you trying to run a Named Query when a tag goes true? I’m a little bit confused at your question.

If that is what you are trying to do try setting up a tag event on the tag and run system.db.runNamedQuery() from there.

Or even better, set up a tag change event in the gateway event scripts of a project.

Old rant here:

Note that Ignition v8's global scripting project has addressed one of the downsides of tag events. I still strongly recommend gateway tag change events over any of the tag events.

2 Likes

@pturmel This is really interesting to me, especially your linked post. If this makes more sense in a new thread, let me know and I can start it. I don’t want to hijack this one.

We have a project where all the tags are driven by sql queries. No device connections. We try to reduce the number of queries with a handful of query tags, but then have a UDT defined that pulls in the necessary information from these root query tags based on the UDT instance name. Within each of these UDTs (dynamic number, but could be up to 2,000 UDT instances, maybe more in the future) we have a handful of tag event scripts for doing some of the heavier lifting that would be too difficult with expressions alone. The tag events write back calculated values into other memory tags in the same UDT.

With something like this, specifically writing back numerous values to tags in the same UDT instance, would you still recommend a gateway tag change script? I feel like I’ve run into reliability issues in the past with gateway tag change scripts not always running when they should…but maybe those were more user error :slight_smile:

Also, if you recommend a tag change script for this, any recommendations on how to handle a dynamic number of UDTs for driving the tag change script in the gateway so that we are still able to write back to the proper triggering UDT instance? We have a gateway script to check for new device deployments and when found, automatically adds a properly named UDT instance. I just don’t know how I would automatically update the tag change script to include these new instances.

Thanks in advance. Feel like I’ve been learning a lot from you lately and I really appreciate it!

I would do all of this with a timer script that runs the repetitive SQL, fans out the raw data to the target UDTs (recipients would be memory tags, not expressions), and perform all of the "heavier lifting", also writing to target memory tags. Multiple driving SQL queries would each get their own timer event. This approach has minimum efficiency lost to multi-threading and minimum skew between data arrival and final results distribution. Timer events naturally prevent overrunning, so final target results are sure to be delivered before the next query is run (if anything bogs down the DB or GW). Be sure to optimize tag writes with lists of tags and values, and use .writeSynchronous/writeBlocking to keep each event deterministic.

With the above design, there wouldn't be a tag change script. (Which is good, since there's no way outside the designer to update the list of subscribed tags.) I'd simply cache a list of UDT instances in the script module and reference it in the query event. Let your UDT-searching script populate/update that list for the query event(s) to use.

In a pinch, if you really must, you can use the gateway's context to access the tag manager, and add and remove your own tag change listeners. There be dragons that way, though:

1 Like

@pturmel This is all great stuff. One thing I forgot to mention. The physical devices are uploading data to SQL irregularly. Some can be every minute, others could be days apart, and everything in between. So even though the SQL query is running every 15 seconds checking for new data, the values in the UDT instances are only changing as often as the physical devices upload. This is why I ended up going with the tag events since I can avoid running the scripts until the value actually changes. Which can be pretty significant as we can get a number of SQL scans where no new data was uploaded to the database.

I could setup a tag to cache the previous SQL results for the gateway to compare new SQL results to but it would be quite a few rows/columns to loop through. Curious if you think this would still be a better approach than tag events? Maybe efficiency wise the gateway script wouldn’t be quite as efficient in this scenario but the other benefits still outweigh any small performance gain tag events might give?

I could also look into changing the queries to return a “sql_update” timestamp value for the gateway script to use to determine which UDTs need to be updated but it would be a little complex as we are querying a view that pulls from a number of database tables…something I’m working on changing.

No real need. Just bulk read all relevant tags and use that for change comparisons. Deciding when a write is needed, and deciding when the heavy lifting code is needed.

1 Like

yes, i dont know what the syntax i am going to use

Interesting. Would you have any worries about performance issues bulk reading in >10,000 tags every 15 seconds (many tags per UDT that need to be checked/updated)? For some reason, I had it in the back of my mind that this would be an "expensive" operation that would bottleneck the gateway, but not really sure what I'm basing that off...

No worries. No different, conceptually, from the implied reads that have to happen within the tag infrastructure to decide if a newly-written value is a change. You’ll just be doing it before writing.

Your infrastructure that monitors for new UDT instances should pre-compute the lists of tagpaths so the query task can just submit it to readBlocking().

1 Like

I'm thinking I could achieve this by having a cache dataset or string array tag for holding a list of UDT names. Create a separate gateway script to check for new devices, add a UDT instance, and update the cache tag. Also periodically check to make sure the cache tag is up to date (gateway restarts, etc). Any pitfalls you see in this approach?

Don’t use a tag. Use a top level dictionary in the script module holding your event code as your cache. You want the python list object itself to be precomputed.

1 Like

I’ve never actually used the script module to hold a cache before. I found an old post of yours, but wondering if I even need to worry about thread locking since it should be just this one gateway script accessing it. I could see it being helpful if we create additional gateway scripts that need to access/update the cache though.