Problem inserting / update intoa db table

I need to insert a record into a sql db when a tag changes its value, so I putted the attached script in the “Value Changed Event” of the tag:

value1 = 1
value2 = 2.0

system.db.runUpdateQuery(“INSERT INTO Gancio (NomeGancio, TForno1) VALUES (?,?)”, [value1, value2])

Anyway in the log section of the gateway I found this message:

([default]Insert_Update_DB/GancioPos64, valueChanged) Error executing tag event script: Traceback (most recent call last): File "", line 6, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error( at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery( at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO Gancio (NomeGancio, TForno1) VALUES (?,?), [1, 2.0], , false)

and the record is not inserted into the table
(If I copy and paste the insert statement into Sql Management the query works fine; actually the table is not empty, so it connot be a problem on primary key)

Any idea why it doesn’t work?
Thank you

system.db.runUpdateQuery() doesn’t work that way. Try using system.db.runPrepUpdate().

I’m using runUpdateQuery
but it doesn’t work

It won’t work, because runUdateQuey does not work in the format you are using. You will also need to specify the database connection, since tag scripts run in the gateway scope and doesn’t know which connection to use.

You can:

  • Change it to runPrepUpdate, which does use the format you are using.
system.db.runPrepUpdate("INSERT INTO Gancio (NomeGancio, TForno1) VALUES (?,?)", [value1, value2], databaseConnection)
  • If your heart is set on using runUpdateQuery, you must change the format:
system.db.runUpdateQuery("INSERT INTO Gancio (NomeGancio, TForno1) VALUES (%d,%f)" % (value1, value2), databaseConnection)

system.db.runUpdateQuery() doesn’t let you use the ? ( which you are using )
system.db.runPrepUpdate() let you use the ?
So, as @JordanCClark says, you should use system.db.runPrepUpdate() instead of system.db.runUpdateQuery() and it should be fine.
If you want to use system.db.runUpdateQuery() at all costs, you have to change your code to:

value1 = 1
value2 = 2.0
system.db.runUpdateQuery(“INSERT INTO Gancio (NomeGancio, TForno1) VALUES (%d, %f)” % (value1, value2)))

Sorry, I didn’t see your reply :sweat_smile:

1 Like

@JordanCClark Great !!!
Thank you so much … the problem was missing database connection parameter !!!