system.db.runPrepQuery working but throwing an error

I am having an issue with a script, I havent been able to figure it out, it works but throws an error.

for testing I am using a memory tag with the script on change.

First I attempted to have the query in a string variable called query, but it was not working, after multiple changes, this scripts writes to the DB, but throws the error in the Tag Diagnostics shown below

I have the following script,

try:
		t_stamp = system.date.now()
		line = 'TestLN'
		station = 'VirSta'
		request_SN = '1234567890'
		request_ModelNo = 'ModelNumber'
		request_Rev = 'WW'
		requestType = 'RequestType'
		request_Timestamp = system.date.now()
		
		system.db.runPrepQuery("INSERT INTO dbo.AFN_PHSRequests ( t_stamp, line, station, request_SN, request_ModelNo, request_Rev, requestType, request_Timestamp)"                                   \
					" VALUES (?,?,?,?,?,?,?,? ) " ,[t_stamp , line, station, request_SN, request_ModelNo, request_Rev, requestType , request_Timestamp ], "IGN_DB")
		
	except Exception, ex:
		messagetoPrint = "Error inserting data into table, query: " + str(query) + "    Exception:"+ str(ex.message)
		shared.GW_Scripts.LogErrorMessage("AFN Requests",messagetoPrint)

I get the following error.

Error executing script.
Traceback (most recent call last):
  File "<tagevent:valueChanged>", line 18, in valueChanged
  File "<tagevent:valueChanged>", line 18, in valueChanged
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:287)
	at sun.reflect.GeneratedMethodAccessor113.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.runPrepQuery(INSERT INTO dbo.AFN_PHSRequests ( t_stamp, line, station, request_SN, request_ModelNo, request_Rev, requestType, request_Timestamp) VALUES (?,?,?,?,?,?,?,? ) , IGN_DB, [Wed Oct 09 09:22:54 CDT 2019, TestLN, VirSta, 1234567890, ModelNumber, WW, RequestType, Wed Oct 09 09:22:54 CDT 2019], )

I have cleared the table a few times and thats why there are only a few resutls of my tests.

I guess it should be something simple but I cannot figure it out.

Thanks in advance,

why is VALUES (?,…) in double quotes?

Try:

system.db.runPrepQuery("INSERT INTO dbo.AFN_PHSRequests ( t_stamp, line, station, request_SN, request_ModelNo, request_Rev, requestType, request_Timestamp) VALUES (?,?,?,?,?,?,?,? ) "

If your intention was to split that line into multiple lines, you need to use triple quotes “”" start and end

Wait, that system function should only be used for SELECT queries. Use system.db. runPrepUpdate for INSERT statements

1 Like

Thanks that was the issue!!

1 Like