system.db.runPrepQuery working but throwing an error

Ignition
1

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.

image
image934×670 17.2 KB

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

Thanks in advance,

2

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 (?,?,?,?,?,?,?,? ) "

3

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

4

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

1 Like
5

Thanks that was the issue!!

1 Like