Gateway Tag Change Script infrequent failure

I have a Gateway Tag Change Script running on the value of a boolean tag. The script grabs some tag values and uses and runPrepUpdate function to push the data to the database. it uses the result of the runPrepUpdate to determine if the write was completed and then sets a boolean tag using writeBlocking functions to confirm that the database update is complete or failed.

This works fine 95% of the time. once in a while it doesn’t set the final tag

Snippet of the Tag Change Script

if not initialChange and (newValue.getValue() == 1) :
	#collect the required data 
	...
	
	# write the data to the database table 
	databaseWriteResult = system.db.runPrepUpdate("INSERT INTO CIPRecords (StartTime, EndTime, Route, CIPType, Complete) VALUES (?,?,?,?,?)", [startTime, finishTime, route, CIPType, complete])
	if databaseWriteResult == 0 :
		system.tag.writeBlocking('[WP_TagProvider]CIP/CP21_ReportFailed',1)
	else :
		system.tag.writeBlocking('[WP_TagProvider]CIP/CP21_ReportDone',1)

I think the problem is if runPrepUpdate fails (i.e. doesn’t complete in a limited time), the script stops functioning and aborts. would this be correct? (I have never seen the ReportFailed Tag set.)

If not, what is the result of the runPrepUpdate function if it does not complete the database transaction?

I think the issue is that system.db.runPrepUpdate returns the number of affected rows, if the update fails for some reason it won’t return anything and might error. You’ll only get a zero if everything works fine but nothing is changed.

You could try using an exception (try, except) to write in case of a failure.

The writes are wrong, this shouldn’t work at all.
system.tag.writeBlocking parameters should be lists:

if databaseWriteResult == 0:
	system.tag.writeBlocking(['[WP_TagProvider]CIP/CP21_ReportFailed'], [1])
else:
	system.tag.writeBlocking(['[WP_TagProvider]CIP/CP21_ReportDone'], [1])

I’m not quite sure what error could come from runPrepUpdate, maybe you could try catching everything it throws and log it somewhere:

logger = system.util.getLogger("reportWrite")
try:
	databaseWriteResult = system.db.runPrepUpdate("INSERT INTO CIPRecords (StartTime, EndTime, Route, CIPType, Complete) VALUES (?,?,?,?,?)", [startTime, finishTime, route, CIPType, complete])
except BaseException as e:
	logger.warn(repr(e))
else:
	if databaseWriteResult == 0:
		path = '[WP_TagProvider]CIP/CP21_ReportFailed'
	else:
		path = '[WP_TagProvider]CIP/CP21_ReportDone'
	writeReturn = system.tag.writeBlocking([path], [1])
	if writeReturn[0].isNotGood():
		logger.warn("failed write to tag {} ({})".format(path, writeReturn[0]))

Recent versions of Ignition accept singletons instead of lists as a convenience.

2 Likes

Oooooh time for an update then !
I missed the changelog about this, when did that happen ?

I don’t recall. Been some months at least.

8.1.3

That is the expected jython behavior - if a line raises an exception, then the code stops dead in it’s tracks.

The real question is - why would what looks likes a simple INSERT query cause a timeout failure? Do you have some complicated trigger on that table BEFORE/AFTER INSERT or something?

… what !? I’m on 8.1.10 and still wrapping everything in [] ! Why didn’t you tell me sooner ?

That’s why you should come to the meetings. lol

I had to hunt for mention of it, and it’s not in the docs. It was changed because it was a stumbling block to new users. Using lists is still the preferred method. I just got used to always using lists to keep a consistent style.

3 Likes

Thanks All for your help.

Will add the exception logic and see if we can see what is causing the database query to fail.