Gateway timer script to log folder of tags to SQL

I’m attempting to create a gateway script that will log all of the tags of a folder into the MySQL table on a time interval. This script does not report any errors when operating, but nothing appears in the SQL table. I’m stuck on what the issue is with the script and was hoping to find some help.

def logDB(tagPath):
	import system
	from datetime import datetime
	tpName = str(tagPath)+'.Name'
	#tpDoc =   str(tagPath)+'.documentation'  
	#tpID =   str(tagPath)+'.Tooltip'
	tpTStamp = str(tagPath)+'.LastChange'
	tpPrevValue = str(tagPath)+'.Tooltip'
	
	Name = system.tag.read(tpName).value
	Value = round(float(system.tag.read(tagPath).value),3)
	
	fmt = '%Y-%m-%d %H:%M:%S'
	now = system.tag.read("Internal/DateTime_Now").value
	TStampNow = system.db.dateFormat(now, "yyyy-MM-dd HH:mm:ss")
	TStampNow_frmt = datetime.strptime(TStampNow, fmt)
	
	try:
		prevValue = round(float(system.tag.read(tpPrevValue).value),3)
	except:
		prevValue = Value
		system.tag.write(tpPrevValue,Value)
		
	if abs(prevValue) > 0.1: 
		val_diff = abs((Value - prevValue)/float(prevValue))
	else:
		val_diff = abs(Value - prevValue) # avoid divide by zero  

	#Desc = system.tag.read(tpDoc).value
	#CatID = system.tag.read(tpID).value
	# The following condition ensures that if the tag value becomes NULL due to loss of communications
	# then we do not log this tag to DB. 
	if((Value is not None) and  (val_diff > 0.1) ): 
		system.tag.write(tpPrevValue,Value)
		my_query = "INSERT INTO `ride_activity_trends` (`Name`, `TagValue`, `t_stamp`) VALUES (?,?,?) "
		print system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ],["MySQL_Conn"])

tags = system.tag.browseTags("Hydraulic_Pressure")

for tag in tags:
	logDB(tag.path)

What happens when you run that query a single time in the DB Query Browser? Does the insert work?

Gateway scripts require the use of a tag provider (e.g. '[default]path/to/tag')

Just a code comment, but can you avoid the imports by using system.date functions?
See system.date - Ignition User Manual 8.1 - Ignition Documentation. What are you really trying to do there?

I am not sure if it will make a difference, but try changing this:

system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ],["MySQL_Conn"])

To this:

system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ], datasources=["MySQL_Conn"])

It’s not getting stuck in the SF queue is it?

1 Like

Apologies my responses have been disappearing. It seems to do that when editing posts.

Here is the code based on comments above. I’ve realized a typo in SQL name but that did not fix it still.

def logDB(tagPath):
	import system
	tpName = str(tagPath)+'.Name'
	tpTStamp = str(tagPath)+'.LastChange'
	tpPrevValue = str(tagPath)+'.Tooltip'
	
	Name = system.tag.read(tpName).value
	Value = round(float(system.tag.read(tagPath).value),3)
	
	fmt = '%Y-%m-%d %H:%M:%S'
	#now = system.tag.read("Internal/DateTime_Now").value
	now = system.date.now()
	TStampNow = system.db.dateFormat(now, "yyyy-MM-dd HH:mm:ss")
	
	try:
		prevValue = round(float(system.tag.read(tpPrevValue).value),3)
	except:
		prevValue = Value
		system.tag.write(tpPrevValue,Value)
		
	if abs(prevValue) > 0.1: 
		val_diff = abs((Value - prevValue)/float(prevValue))
	else:
		val_diff = abs(Value - prevValue) # avoid divide by zero  

	# The following condition ensures that if the tag value becomes NULL due to loss of communications
	# then we do not log this tag to DB. 
	if((Value is not None) and  (val_diff > 0.1) ): 
		system.tag.write(tpPrevValue,Value)
		my_query = "INSERT INTO `ride_activity_trends` (`Name`, `TagValue`, `t_stamp`) VALUES (?,?,?) "
		print system.db.runSFPrepUpdate(my_query,[Name, Value,  TStampNow ], datasources=["MySQL"])

tags = system.tag.browseTags("[default]Hydraulic_Pressure")

for tag in tags:
	logDB(tag.path)

I’m fairly new to using Ignition so do not know how to check if this issue is occurring.

On the gateway > status > Store & Forward :
Aggregate Throughput shows as 0.0 per second. I suppose that means it is not making it through the S&F queue?

Also, I would check your wrapper log.

I don’t think that this:

tags = system.tag.browseTags("[default]Hydraulic_Pressure")

for tag in tags:
	logDB(tag.path)

Does what you’re expecting.

From the manual, system.db.browse() returns a list of tag dictionaries system.tag.browse - Ignition User Manual 8.1 - Ignition Documentation

So the proper way to write that code would be:

tags = system.tag.browseTags("[default]Hydraulic_Pressure")

for tag in tags:
	logDB(tag['path'])

There are other optimizations that could be suggested here, but I think this is the bug you’re looking for.

Just in the event this is useful (and perhaps you’re implementing it this way because you don’t have access to SQL Bridge module in an existing project, etc), I feel that I should mention consideration of SQL Bridge (Transaction Groups) as it is explicitly designed to do what you’re shooting for in script.

3 Likes

I will have a read, thank you!

I tried this change and it produced an error.

Thinking the data was not reaching store & forward, I removed the IF statement and it started writing to the SQL table. It seems that logic was not working how I expected it to.

I will have to re-evaluate that logic.

It would show in quarantine in S+F status. Normally, when scripting something new, I won’t use SF initially, until I know the script works. Also, if lots of data, I initially use a limit x (MariaDB) or top x (MS SQL) so I don’t choke the gateway with millions of rows.

What was the error?

Some things to consider when reworking your code:

  1. You should be using system.tag.readBlocking() and system.tag.writeBlocking() in place of the now deprecated system.tag.read() and system.tag.write()

  2. You should take advantage of the ability of these new functions to read and write multiple tags at the same time. I like to read all tags as soon as possible and write all tags as late as possible, doing all of the processing between those two calls

  3. You should consider condensing your insert query such that you insert all rows with one call to the db. There is a limit to how many rows you can insert in one call but why make 10 calls when you can make 1?

  4. Don’t make unnecessary type conversion calls. If the tag type is a float, then you’ll get a float value back.

  5. Don’t make blanket except blocks, instead be specific about the error you’re trying to catch.

  6. Don’t check for a value one way and then use it another way. Specifically checking a value for zero by using the absolute value. Consider:

val = -0.2

if abs(val) > 0.1:
    print round(val)
  1. print in a gateway script doesn’t necessarily output where you think it does. Consider using a logger.

Thank you for the advice! I will consider and try to use it to the best of my abilities.

So answer your question about changing the for loop to:

for tag in tags:
	logDB(tag['path'])

The following error was received.
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 37, in TypeError: ‘com.inductiveautomation.ignition.common.script.builtin.ialabs.BrowseTag’ object is unsubscriptable

Huh, seems like they changed something between 8.0 and 8.1.

I believe in 8.0 this will do the trick then.

for tag in tags.getResults():
    logDB(tag['path'])

limit x (MariaDB) or top x (MS SQL)
I unfortunately have not come across what you’re referring to to understand it and it’s implementation. Would you mind describing it further?
Is this a function to write in the script or an S&F config?

It’s SQL.

It can be used to Limit the number of rows affected or returned by a query.

1 Like

The following produces the error below. line 38 is the for loop.

for tag in tags.getResults():
    logDB(tag['path'])

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File “”, line 38, in AttributeError: ‘array.array’ object has no attribute ‘getResults’

EDIT: What specific version of Ignition are you using? That hasn’t been stated, yet.

getResults() should work regardless. Let’s start small and work our way up. This is a sample using my server, change to your tag path and see if we can print some values from the script console.

tagPath = '[default]Assembly/3425/Production'

# Browse for tags in a Folder
tags = system.tag.browse(tagPath, {'tagType' : 'AtomicTag'}).getResults()

# Initialize list of tags to read
tagReadList = []

# Add fullpath of each tag from the browse to the list 
for tag in tags:
	# fullpaths are objects so we must coerce them to strings.
	tagReadList.append(str(tag['fullPath']))

# Read the tags. Note that these are qualified values, not just the values.
qvalues = system.tag.readBlocking(tagReadList)

# Print the results. zip() lets us iterate through multiple lists at once.
for tagName, qvalue in zip(tagReadList, qvalues):
	value = qvalue.value
	t_stamp = qvalue.timestamp
	quality = qvalue.quality
	print tagName, value, t_stamp, quality

output

[default]Assembly/3425/Production/actual 696 Mon Jun 27 14:48:29 EDT 2022 Good
[default]Assembly/3425/Production/Efficiency 0.633879780769 Mon Jun 27 14:48:30 EDT 2022 Good
[default]Assembly/3425/Production/Efficiency 60 0.538461506367 Mon Jun 27 14:48:27 EDT 2022 Good
[default]Assembly/3425/Production/goal 1098 Mon Jun 27 14:48:13 EDT 2022 Good
[default]Assembly/3425/Production/total 769 Mon Jun 27 14:48:29 EDT 2022 Good
1 Like