Hello
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(AbstractDBUtilities.java:364) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:189) 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)
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() 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: