Named Query in Tag Change Script

[Richard_Dixon]

I am doing some API work using the Web Dev module. When data posts to my endpoint it is written to a few memory tags. I have a tag change script on one of the memory tags to write to another memory tag in a hierarchy model according to the value in data posted. To do this I have to do a lookup in a database first.
This is the error I get in the Diagnostics pane in the Tag Editor:

Error executing script.
Traceback (most recent call last):
  File "<tagevent:valueChanged>", line 29, in valueChanged
						UPDATE oee.vin_location 
						SET exit_timestamp = ?
						WHERE vin_id = ?::UUID
						, [2022-07-29 09:13:58, d36a13a0-64c0-4489-87b4-71f274538831], , , false, false)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
	at jdk.internal.reflect.GeneratedMethodAccessor112.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(
						UPDATE oee.vin_location 
						SET exit_timestamp = ?
						WHERE vin_id = ?::UUID
						, [2022-07-29 09:13:58, d36a13a0-64c0-4489-87b4-71f274538831], , , false, false)

This is my code in Value Changed section in Tag Editor:

data = system.tag.readBlocking("[default]EOL_Testing/Camera_Data")[0].value
	vin = system.tag.readBlocking("[default]EOL_Testing/Another_Test_Post")[0].value
	stationIn = system.tag.readBlocking("[default]EOL_Testing/Test_Post")[0].value
	line = system.tag.readBlocking("[default]EOL_Testing/Line_Test")[0].value
	
	metadataValue = system.util.jsonEncode({"sos_name": stationIn})
	lookupVin = system.db.runNamedQuery("SCADA_Map","EOL/LookupVIN", {"lookup_vin": vin})
	lookupStation = system.db.runNamedQuery("SCADA_Map","EOL/GetShortName", {"sos": metadataValue})
	vinId = system.db.runNamedQuery("SCADA_Map","EOL/GetVinId", {"lookup": vin})
	vinLocationId = system.db.runNamedQuery("SCADA_Map","EOL/VinLocationId", {"lookup_id": vinId})
	
	date = system.date.format(system.date.now(),"YYYY-MM-dd HH:mm:ss")
	model = ""
	color = ""
	

	if currentValue.value == lookupVin and currentValue.value != previousValue.value:
		if vinId == vinLocationId:
			if previousValue.value:
				prodTagPath = "[OEE]Normal 1/End of Line/%s/%s/ProdOEE/%s" %(line, lookupStation, "ObjectID")
				objIdValue = system.tag.readBlocking(prodTagPath)[0].value
				query = """
						UPDATE oee.vin_location 
						SET exit_timestamp = ?
						WHERE vin_id = ?::UUID
						"""
				args = [date, vinId]
				system.db.runPrepUpdate(query, args)
			if currentValue.value:
				insert_statement = """
					INSERT INTO oee.vin_location (vin_id, entry_timestamp, object_id)
					VALUES (?::UUID, ?, ?::UUID)
				"""
				values = [vinLocationId, date, objIdValue]
				system.db.runPrepUpdate(insert_statement, values)
				
				writeVin = "[OEE]Normal 1/End of Line/%s/%s/ProdOEE/%s" %(line, lookupStation, "VIN")
				system.tag.writeBlocking(writeVin, currentValue.value)
			
			

Edited this post:
I solved the previous question about the Named Query. I had to cast the value to make sure it matches using the ::JSONB.

Now I have this new error with regards to line 29. I made sure to cast in my Named Queries as well depending on value whether it is ::JSONB or ::UUID. Now I dont why I am still getting this error.

Any help with this is much appreciated. Thanks

[bkarabinchak.psi]

The issue is for named query paramters you need a flat dictionary like

params = {'param1':'value2','param2':2,....}

etc.
But you have a nested dictionary here of {'sos':{'sos_name':stationIn}} which I don’t think will work.

Are you trying to insert a json object in your database? I’ve never done that via named query. I know some databases support it like PostGres but I amy not sure how you would do that in a named query unfortunately.

However, since you are writing this in a API endpoint that only executes in the gateway context you can use system.db.runPrepUpdate or system.db.runUpdateQuery safely without fear of SQL injection since all it seems like you are writing anyways is a tag value, nothing from the API request itself. You may have more luck with those functions for inserting a JSON.

However, if you are not trying to insert into a JSON column, then its just about how your parameter for the named query looks - it should not have a nested dictionary in any param you are actually using (you can have extra keys in the dictionary if they don’t correspond to any parameter they will just be ignored).

[Richard_Dixon]

The nested dictionary worked it seems cause it is not erroring out anymore on that line. The sos_name object is in the column just like that with key and value.
I have to use the json column as a lookup to do a comparision. Right now I have a new issue with the edited post above.
I am using system.db.runPrepUpdate and getting the above error message. What do you think about the new error message and my edited code?

[bkarabinchak.psi]

Can you specify exactly what query is erroring out and then also post the error message you are getting?

[Richard_Dixon]

It is in the main post above. I edited it to contain the new error. It has the code in there which is in error. It is the system.db.runPrepUpdate(insert_statement, values).
EDIT: This is the error that would be in the Diagnostics pane in the tag editor

[bkarabinchak.psi]

Is there no more to that error? Normally there’s caused by which will give you the actual SQL issue ie some column does exist or something.

One thing I will do to troubleshoot is to try the query directly in my database workbench and see if it works there with the values you provided.

Perhaps better, you can try it in script console so it’s being done through Ignition

# make sure you set vinLocationId, date, objIdValue
insert_statement = """
					INSERT INTO oee.vin_location (vin_id, entry_timestamp, object_id)
					VALUES (?::UUID, ?, ?::UUID)
				"""
values = [vinLocationId, date, objIdValue]
system.db.runPrepUpdate(insert_statement, values)

and I think you should see a much longer error message that should actually give us a reason as to what is going wrong inside the SQL itself.

[Richard_Dixon]

So I read the error message in the script console and it hinted at timestamp issue. I casted the value in code below to test and no more errors now. And I checked db and it updated column like I want it to. But when I run it with the rest of the code in the tag change script I still get the same error. I am wondering if the arguments like currentValue and previousValue is causing some issue

[pturmel]

When run from a tag event, the runPrepUpdate function needs an explicit database name. See its documentation for the difference between client & designer project scope versus gateway scope.

[Richard_Dixon]

Thank you. Was able to update the table. Wish I could give the solution to you and Profile - bkarabinchak.psi - Inductive Automation Forum cause his answer helped out a lot with finding out the timestamp issue as well.

[bkarabinchak.psi]

Don’t worry about it they’re just internet points happy I could help

[JordanCClark]

@bkarabinchak.psi

they’re just internet points

EDIT: Had to resize to hit the 4MB limit.