# Named Query in Tag Change Script

**URL:** <https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414>\
**Category:** Ignition\
**Tags:** ignition81, database, scripting\
**Created:** [July 28, 2022, 3:33pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414 "2022-07-28T15:33:49Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Richard\_Dixon](https://avatars.discourse-cdn.com/v4/letter/r/5e9695/32.png) [@Richard\_Dixon](https://forum.inductiveautomation.com/u/Richard_Dixon)\
**Post date:** [July 28, 2022, 3:33pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/1 "2022-07-28T15:33:49Z")

</div>

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:

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [July 28, 2022, 5:05pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/2 "2022-07-28T17:05:48Z")

</div>

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

```auto
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).

---

<div class="post-metadata">

**Author:** ![Richard\_Dixon](https://avatars.discourse-cdn.com/v4/letter/r/5e9695/32.png) [@Richard\_Dixon](https://forum.inductiveautomation.com/u/Richard_Dixon)\
**Post date:** [July 29, 2022, 4:10pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/3 "2022-07-29T16:10:15Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [July 29, 2022, 4:14pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/4 "2022-07-29T16:14:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Richard\_Dixon](https://avatars.discourse-cdn.com/v4/letter/r/5e9695/32.png) [@Richard\_Dixon](https://forum.inductiveautomation.com/u/Richard_Dixon)\
**Post date:** [July 29, 2022, 4:22pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/5 "2022-07-29T16:22:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [July 29, 2022, 4:33pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/6 "2022-07-29T16:33:14Z")

</div>

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

```auto
# 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.

---

<div class="post-metadata">

**Author:** ![Richard\_Dixon](https://avatars.discourse-cdn.com/v4/letter/r/5e9695/32.png) [@Richard\_Dixon](https://forum.inductiveautomation.com/u/Richard_Dixon)\
**Post date:** [July 29, 2022, 5:30pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/7 "2022-07-29T17:30:40Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/3/a351b6a1e47f95635dfb39ac516d0f081afc87f7.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 29, 2022, 5:36pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/8 "2022-07-29T17:36:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Richard\_Dixon](https://avatars.discourse-cdn.com/v4/letter/r/5e9695/32.png) [@Richard\_Dixon](https://forum.inductiveautomation.com/u/Richard_Dixon)\
**Post date:** [August 2, 2022, 4:56pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/9 "2022-08-02T16:56:48Z")

</div>

Thank you. Was able to update the table. Wish I could give the solution to you and [Profile - bkarabinchak.psi - Inductive Automation Forum](https://forum.inductiveautomation.com/u/bkarabinchak.psi) cause his answer helped out a lot with finding out the timestamp issue as well.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [August 2, 2022, 4:58pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/10 "2022-08-02T16:58:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [August 2, 2022, 7:32pm UTC](https://forum.inductiveautomation.com/t/named-query-in-tag-change-script/62414/11 "2022-08-02T19:32:53Z")

</div>

> [@bkarabinchak.psi](#):
>
> they’re just internet points

![[resize output image]](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/9/c9487798a5fd058b7abe2bddd86ef54bd3d5ac10.gif)  
EDIT: Had to resize to hit the 4MB limit. 😉