Check DB vs tag values, run update if different or

[rfolkes]

8.1.27, vision, MSSQL

looking for is this a good way or is there a better way to do this in ignition.
I have 1000s of UDTs that I am putting some of those mostly static values into MSSQL.
Values initially come from PLC --> Ignition --> DB table
On the tags themselves I have tag change script that runs if the value is updated it updates the value in the DB and creates a special audit log (not ignition audit log) entry. This part is working.

For additional reassurances though we want to add a mechanism to verify that the DB value = the tag value and if it doesn't match update the DB value and generate special audit entry of what changed.

Tag names and Column names match

What I was thinking of was either a tag that runs SQL with Rate set by a trigger tag (want this to run either rarely or on call)

This is just rough idea syntax isn't all there.

get current data from DB

arg = 22
query = select ColA, ColB, ColC From table where ID = ?
data = runPrepQuery (query, arg, db)
#grab tag values
tags = tag.readblocking (TagA, TagB, TagC)
values = tags[0].value, tags[1].value, tags[2].value

#check if tag value <> DB value then update DB and add audit entry
if values[0] != data.getValueAt(0,0):
run SQL update statement for that value
run SQL audit insert statement
Repeat for each value / Col.

Or would this idea work better because it is less calls to database?
#grab current data from DB
arg = 22
query = select ColA, ColB, ColC From table where ID = ?
data1 = runPrepQuery (query, arg, db)

#grab tag values
tags = tag.readblocking (TagA, TagB, TagC)
values = tags[0].value, tags[1].value, tags[2].value. arg

#update all col with current tag values
updatequery = update table set ColA=?, ColB = ? ColC = ? WHERE ID = ?
system.db.runUpdateQuery(updatequery, values, db)

#grab data from DB post update (need to make sure Update query finishes before this runs)
data2 = runPrepQuery (query, arg, db)

#compare the 2 datasets from the database and make entry if they are different.
if data1 != data2 then
run SQL audit insert statement

[lrose]

@rfolkes

tag change script

This is a bad idea, you should be using Gateway Tag Change events. There are a couple of methods to do this, if the script is generalized enough you can run the same script for all of the tags and just assign those tags to the tag change event (Gateway Tag Change Events can be triggered by multiple tags).

You could also generate a top level list of tags, and at a trigger run the script which does all of the work for all of the tags at once.

Reference for why this is a bad idea:

Tag value change event

This. Events on tags themselves should run in single digit milliseconds or less, due to the likelihood of blocking the system-wide thread pool for tags. That means no database operations nor any network access operations nor any recursive searches in these events. Gateway tag change events in a project have their own (unbounded) queues and their own threads. Put any time-consuming operations there. But do note that only one thread services all the tags for a given event, so there can be mo…

https://docs.inductiveautomation.com/display/DOC81/Gateway+Event+Scripts

As for the script that you provided:

• Perhaps you know this, but if not, system.tag.readBlocking() takes a list of tag paths.
• If the tags are mostly static, I wouldn't see the need to read their values more than once.
• system.db.runUpdateQuery() will return the number of row effected by the query, you can check that to insure that "something has changed" in the db.
• This data1 != data2 will not work as written. Verifying equality of datasets is a complex operation, if you have access to @PGriffith's Ignition Extentions Module, it has a function that will check the equality of datasets. Otherwise you will need to script that operation.
• I would recommend using Named Queries rather than hard coding the queries as you have shown, either will work though.

[rfolkes]

@lrose

This is a bad idea, you should be using Gateway Tag Change events. There are a couple of methods to do this, if the script is generalized enough you can run the same script for all of the tags and just assign those tags to the tag change event (Gateway Tag Change Events can be triggered by multiple tags).

Is there anyway to associate particular tags of every instance of a UDT to gateway tag change events?

For the tag change update on a single IO server there is 28 tags I am doing this with in a single UDT. There are 3,000 instances of this UDT so adding that many tags to the gateway change script would be very time consuming. Unless I did a Folder/* to get all instances of that UDT and then used a filter in the script to only take actions on those submembers of the UDT I wanted to do something with?

For instance

PLC1/Input1 -1000
PLC2/Input1-1000
PLC3/Input1-1000
Each input(x) has roughly 600 tags per UDT instance
I need tag change event on roughly 35 out of those 600 tags per UDT

If I do gateway tag change and set it to monitor
PLC1/Input*
PLC2/Input*
PLC3/Input*

and then in script
if event.tagPath.getItemName() in [list of tag names to monitor]:
SQL commands etc...

would that be a big nono because we are running the gateway script on a lot of tags that don't need it or not a big deal because we escape out with the if statement at the beginning if it doesn't fit?

[rfolkes]

@lrose

As for the script that you provided:

• Perhaps you know this, but if not, system.tag.readBlocking() takes a list of tag paths.
• If the tags are mostly static, I wouldn't see the need to read their values more than once.
• system.db.runUpdateQuery() will return the number of row effected by the query, you can check that to insure that "something has changed" in the db.
• This data1 != data2 will not work as written. Verifying equality of datasets is a complex operation, if you have access to @PGriffith's Ignition Extentions Module, it has a function that will check the equality of datasets. Otherwise you will need to script that operation.
• I would recommend using Named Queries rather than hard coding the queries as you have shown, either will work though.

1. I would only be doing the tag read once. not sure where I show it twice but yes that would be my plan to just single read line.
2. Using script tool to test it appears that data1 != data2 comparison works maybe because they are both returning single row datasets?
3. yes I plan on converting what I can to named queries.. Just like to work on the directly and then convert.

[lrose]

@rfolkes

Using script tool to test it appears that data1 != data2 comparison works maybe because they are both returning single row datasets?

I assure you that it doesn't work the way you might think it does.

Datasets not equal but no differences found?

I have to rewrite some stored procedures so I am writing a test to make sure for every input I am getting the same output. I have something like from com.inductiveautomation.common.util import DatasetTestUtils def testSP(): orig_call = system.db.createSProcCall('orginalSP') system.db.execSProcCall(orig_call) new_call = system.db.createSProceCall('newSP') system.db.execSProcCall(new_call) orig_data = orig_call.getResultSet() new_data = new_call.getResultSet() compar…

You can use wildcards at the folder level, and then filter from there to insure that the tag which triggered the event is one you care to work on.

[rfolkes]

@lrose

I assure you that it doesn't work the way you might think it does.

ok thanks for that will look into other methods then.

[pturmel]

@rfolkes

For the tag change update on a single IO server there is 28 tags I am doing this with in a single UDT. There are 3,000 instances of this UDT so adding that many tags to the gateway change script would be very time consuming.

A possibility to consider, if somewhat challenging, would be to have the UDT tag events delegate to a function in a project script module (gateway scripting project). That function would stuff the event details as a tuple into a Java concurrent queue instantiated in a persistent manner (that's a big part of the tricky stuff). Separately, multiple timer events (quantity determined by workload) on dedicated threads would quickly and repeatedly drain and process the queued events. That would effectively give you deployment based on UDT events, but load handling in an independent subsystem.

[rfolkes]

I have setup some code that at least works but probably could use some efficiency improvements, Here it is below if you want to look it over. I'm open for some feedback in how to improve. Looks like it takes just under a min to run without changes ( normally shouldn't expect any changes since tag change script should take care of that --- I did move that into gateway tag change instead of in the tag)
I have this set at fixed rate with delay run of 6 hrs currently.

#set starting point and DB to connect to
plc = 1
num = 1
DB = "MSSQL"
for plc in range(1,7):
	for num in range(1,1001):
#ID to check
		UniqueID = plc*10000+num

#get provider Data for given PLC
		providerlistpath = ["[TGMSSERVFRONT1]Provider List/Dataset"]
		providertag = system.tag.readBlocking(providerlistpath)
		provider = providertag[0].value.getValueAt(plc,1)

#get Tag Values
		strProvider = str(provider)
		strplc = str(plc)
		strnum = str(num)
		tagpathpartA = "[TGMSSERVIO"+strProvider+"]PLC"+strplc+"/Inputs/Input"+strnum
			
# [x] = tagpart..    [x] value needs to match column name in DB 
		POINT = tagpathpartA+"/Info/POINT"
		INPUT_TYPE = tagpathpartA+"/Info/INPUT_TYPE"
		LONG_NAME1 = tagpathpartA+"/Info/LONG_NAME1"
		RACK = tagpathpartA+"/Info/RACK"
		SHORT_NAME = tagpathpartA+"/Info/SHORT_NAME"
		SLOT = tagpathpartA+"/Info/SLOT"
		BUILDING = tagpathpartA+"/Info/Physical Location/BUILDING"
		LEVEL = tagpathpartA+"/Info/Physical Location/LEVEL"
		ROOM = tagpathpartA+"/Info/Physical Location/ROOM"
		SECTOR = tagpathpartA+"/Info/Physical Location/SECTOR"
		EXTENDED_DELAY = tagpathpartA+"/Control/EXTENDED_DELAY"
		EGU = tagpathpartA+"/ANALOG/EGU"
		FULL_SCALE = tagpathpartA+"/ANALOG/FULL_SCALE"
		MAXIMUM_EGU = tagpathpartA+"/ANALOG/MAXIMUM_EGU"
		MINIMUM_EGU = tagpathpartA+"/ANALOG/MINIMUM_EGU"
		NO_LATCH = tagpathpartA+"/DISCRETE/NO_LATCH"
		NO_WARNING = tagpathpartA+"/DISCRETE/NO_WARNING"
		NORMAL_STATE = tagpathpartA+"/DISCRETE/NORMAL_STATE"
		NAC_ZONE_01 = tagpathpartA+"/NAC_ZONE_01"
		NAC_ZONE_02 = tagpathpartA+"/NAC_ZONE_02"
		NAC_ZONE_03 = tagpathpartA+"/NAC_ZONE_03"
		NAC_ZONE_04 = tagpathpartA+"/NAC_ZONE_04"
		
# Grab tag values for all tags listed in one call
		tagPaths = POINT, INPUT_TYPE, LONG_NAME1, RACK, SHORT_NAME, SLOT, BUILDING, LEVEL, ROOM, SECTOR, EXTENDED_DELAY, EGU, FULL_SCALE, MAXIMUM_EGU, MINIMUM_EGU, NO_LATCH, NO_WARNING, NORMAL_STATE, NAC_ZONE_01, NAC_ZONE_02, NAC_ZONE_03, NAC_ZONE_04
		values = system.tag.readBlocking(tagPaths)

# Grab tag values for all tags listed in one call
tagPaths = POINT, INPUT_TYPE, LONG_NAME1, RACK, SHORT_NAME, SLOT, BUILDING, LEVEL, ROOM, SECTOR, EXTENDED_DELAY, EGU, FULL_SCALE, MAXIMUM_EGU, MINIMUM_EGU, NO_LATCH, NO_WARNING, NORMAL_STATE, NAC_ZONE_01, NAC_ZONE_02, NAC_ZONE_03, NAC_ZONE_04
values = system.tag.readBlocking(tagPaths)

# DBcollist needs to be duplicate with "[name]" of the tagPaths = statement above to keep columns aligned with same index
DBcollist = "POINT", "INPUT_TYPE", "LONG_NAME1", "RACK", "SHORT_NAME", "SLOT", "BUILDING", "LEVEL", "ROOM", "SECTOR", "EXTENDED_DELAY", "EGU", "FULL_SCALE", "MAXIMUM_EGU", "MINIMUM_EGU", "NO_LATCH", "NO_WARNING", "NORMAL_STATE", "NAC_ZONE_01", "NAC_ZONE_02", "NAC_ZONE_03", "NAC_ZONE_04"

# get Dataset from SQL DB to compare to tag values
query = "SELECT {} FROM TGMS_INPUT WHERE UniqueID = ?".format(','.join(DBcollist))
args = [UniqueID]
InitialDB = system.db.runPrepQuery(query, args, DB)
		
		
# For each Tag Path, iterate through our results...
		for index in range(len(tagPaths)):
#set values to compare and Column name checking
		    ColName = DBcollist[index]
		    tagPath = tagPaths[index]
		    DbValue = InitialDB.getValueAt(0,ColName)
		    TagValue = values[index].value
		
#check if DB value and Tag Value don't match
		    if DbValue != TagValue:
		
#update DB value
		   		query = "UPDATE TGMS_Input SET " + ColName + " =  ? OUTPUT INSERTED.UniqueID WHERE UniqueID = ?"
		   		args= TagValue , UniqueID
				Update = system.db.runPrepQuery(query,args, DB)
				UpdateID = Update.getValueAt(0,0)
				OldValue = str(DbValue)
				NewValue = str(TagValue)
				ColName = str(ColName)
#add entry to audit log		
				auditQuery = "INSERT INTO InputAuditLog (ModifiedID, OldValue, NewValue, ColumnChanged, TagPath) VALUES (?,?,?,?,?)"
				auditArgs = UpdateID, OldValue, NewValue, ColName, tagPath
				system.db.runPrepUpdate(auditQuery, auditArgs, DB)

[lrose]

Just a cursory glance:

This:

for i in range(len(DBcollist)):
	if querySelect == "":
		querySelect = DBcollist[i]
	else:
		querySelect = querySelect + ", " + DBcollist[i] 
	query = "SELECT "+querySelect+" FROM TGMS_INPUT WHERE UniqueID = ?"

Can be refactored as:

query = 'SELECT {} FROM TGMS_INPUT WHERE UniqueID = ?'.fomrat(','.join(DBcollist))

Also, anytime you have code of the form:

for i in range(len(someList)):

there is almost certainly a better way to write it. There are examples all over the forum.

[rfolkes]

I made the change you pointed out. Much cleaner way of doing it thank you I wasn't aware of that command.

for the "for i in range(len(somelist))" I wasn't able to find anything in this forum but I did a general web search and what I find is that it isn't preferred because it is old programming style and not needed if you don't use the index value for anything within the iteration so why have it. However in my lower use case I am using the index and after looking at some of the alternative methods I was able to find I am not able to think of a way to do it a different way.

Besides the look of the code I haven't found a reason to not use the "for i in range.."

my last programing class taught something like this for iteration.

20 x=x+1
30 do something with x
40 if x<20 goto 20

[bkarabinchak.psi]

In my personal testing named queries run faster than runPrepUpdate. In one off queries you probably won't notice a difference but since you are running many queries in a loop I think you can see an improvement with making that change.

[rfolkes]

I did start with Named query but with the modification of the query string and not just the variables I wasn't sure if named query would work well (guard against sql injection) and I didn't want to leave 2 places to modify the code. With this set to run every 6 hrs now and probably later will set to 12 hrs I am not that concerned with speed. Right now it is taking under a min to complete however that is without finding anything that needs to be updated. No updates should be the standard though and this is just a ... just in case edge case something gets out of sync due to unknown cause get it fixed and make a record of it.

[bkarabinchak.psi]

You're safer with a named queries even if they require SQL Strings. To use system.db.runQuery or system.db.runPrepQuery or system.db.runPrepUpdateQuery, you need Legacy DB Access which opens up a can of worms.

Legacy Database Access Allows Clients to run queries directly against the database. This doesn't effect named queries.

This means if someone is able to spoof a client (not easy but not impossible) they can run queries directly against your db.

Name queries don't allow that - the client can only run the pre-defined named queries (and really the client doesnt' really run them afaik, but instead tells the gateway hey run this NQ with these parameters and the GW is the only one with access to the db). That's my understanding of it at least.

[lrose]

@rfolkes

20 x=x+1
30 do something with x
40 if x<20 goto 20

Wow now that's BASIC. Maybe I'm not as funny as I think.

Anyway:

For this loop, you can get rid of the range function by utilizing other functions at your disposal.

For instance you could use the enumerate function:

for index,tagPath in enumerate(tagPaths):
    ColName = DBcollist[index]
    DbValue = InitialDB.getValueAt(0,ColName)
    TagVAlue = values[index].value

However, in this case using zip() is the better option imho:

for tagPath,ColName,TagValue for zip(tagPaths,DBcollist,[val.value for val in values]):
   if DbValue != TagValue:

[rfolkes]

this system will be dark (no web access) with tons of physical security. someone getting in and spoofing a client isn't a concern

[rfolkes]

Thanks will try that out. Appreciate the help.

[bkarabinchak.psi]

Then no need to worry about sql injection at all anyways. I still prefer always to use named queries though - its the only way to setup a single source of truth for your queried data sources. Otherwise if you need to get the same data out of a table in another script you have to rewrite your query there in another system.db.runPrepUpdate.

The real benefit comes apparent if you do this (run the same or similar queries in various scripts) and now comes time when you need to modify them all. With named queries you just modify it in one spot.

[rfolkes]

@bkarabinchak.psi

you do this (

yes in most cases I 100% agree with you. This gateway script is the only place these queries will be used. If I were to use this script somewhere else or similar query somewhere else I would def use named query to keep a single point of change.

[pturmel]

@bkarabinchak.psi

With named queries you just modify it in one spot.

A project library script function that runs a specific query (where the query text is in the script library) on behalf of callers is just as maintainable as named queries, and offers the opportunity to perform other validation in a common location.

The big win for Named Queries is for bindings, especially in Vision, not for scripts. (Vision with legacy DB access disabled can still use message handlers to run functions that securely execute queries.)

[bkarabinchak.psi]

The other win for named queries I neglected earlier but is worth mentioning would be the enabling of caching on the results. Doesn't really apply to this situation here as his SELECT is for a different ID every time and he would need a very long cache time to be able to use the last result instead of re-querying the db, but in some cases it can speed things up.

Why do you say bindings in vision specifically as opposed to for vision and perspective? Do you mean the convenience of binding to the parameters?