I encountered an error when using the runPrepUpdate function to insert a record into my database. To eliminate all other possible issues, I created a table called "myTest" in my MSSQL database with only one column and no restrictions on the data field. When I run the insert script directly in SQL, it works without any issues. However, when I execute the following script in Ignition, I get an error:
import system
system.db.runPrepUpdate("INSERT INTO myTest (IDSN) VALUES (?)", ['aaaaa'], 'DBConnection')
To debug this script, I executed the following query statement, which successfully returns a number. This shows that my database connection is working correctly:
existing_record = system.db.runScalarPrepQuery("SELECT COUNT(*) FROM myTest WHERE 1=?", [1], 'DBConnection')
print(existing_record)
Here is the detailed error message:
File "<input>", line 6, in <module>
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.runPrepUpdate(INSERT INTO myTest (IDSN) VALUES (?), [aaaaa], DBConnection, , false, false)