Writing to a database by using a script called by a tag script

Ignition
, ,
1

Hi,
I have many Tanks with some information that I need to store into a database. Instead of having a transaction group for each tank to create a new line or update the existing one, I want to use a script where, at the right tag condition, I'm able to call the right method to create a new line or update the values in the existing one.
The script itself is not an issue. I have already made it and I have tested with a button. Now I want to apply the triggers to some variables to do all the steps but I see that is not working at all in this way.
I have read some other thread like this one around but I can't figure out how to solve it.

This is my script:

def tankInsert(serbatoio,tipoLavaggio):
	#serbatoio = 'T301'
	#tipoLavaggio = 'Test'
	dtCIP = system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm')
	system.db.runUpdateQuery("INSERT INTO serbatoi (nrSerbatoio, dtUltimoCIP, tipoCIP)" + "VALUES ('%s', '%s', '%s')" %(serbatoio, dtCIP,tipoLavaggio))

If i call it from a button, is working. If I call from a tag script with a value change, it is not working and i can see, from the gateway status page, this kind of error:

([default]PLANT/RAW/T301/Tank/Inputs/CIPDone, valueChanged) Error executing tag event script: Traceback (most recent call last): File "tagevent:valueChanged", line 8, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183) at jdk.internal.reflect.GeneratedMethodAccessor179.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO serbatoi (nrSerbatoio, dtUltimoCIP, tipoCIP)VALUES ('T3703', '2021-47-13 15:47', 'Test'), DatabaseSRV, , false)

2

I would use runPrepUpdate instead, and since you are using this in a tag script you must provide the database connection as tag scripts run on the gateway not the client.
system.db.runPrepQuery - Ignition User Manual 7.9 - Ignition Documentation (inductiveautomation.com)

def tankInsert(serbatoio,tipoLavaggio):
    query = '''
            INSERT INTO serbatoi (nrSerbatoio, dtUltimoCIP, tipoCIP)
            VALUES (?,?,?)
            '''
	#serbatoio = 'T301'
	#tipoLavaggio = 'Test'
	dtCIP = system.date.format(system.date.now(), 'yyyy-MM-dd HH:mm')
	system.db.runPrepUpdate(query,[serbatoio, dtCIP,tipoLavaggio], database = 'dbName')
5 Likes
3

I totally miss, from the documentation, where to set the database. I also change my script using you advice.

Thank you very much!

1 Like