PGSQL Error on runPrepUpdate

I am trying to get a system.db.runPrepUpdate command to run after updating to 8.0.2 and it is throwing an error I have never seen before and does not make any sense because it’s saying I am using a bad value for type int, when inserting into a text column.
Here is the script I am trying to run:

name = "Well 2"
customer = "f1864e92-3051-449b-991c-5edc34a2e557"
asset_uuid = system.db.runPrepUpdate("INSERT INTO asset_list (asset_name, customer_uuid) VALUES (?::text,?::uuid)",[name,customer],"Cloud_Carefree",getKey=1)
print asset_uuid

Here is the error its throwing:

Caused by: org.postgresql.util.PSQLException: Bad value for type int : Well 2

Full error:

>>> 
Java Traceback:
Traceback (most recent call last):
  File "<buffer>", line 3, in <module>
	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 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 asset_list (asset_name, customer_uuid) VALUES (?::text,?::uuid), [Well 2, f1864e92-3051-449b-991c-5edc34a2e557], Cloud_Carefree, , true, false)

	at org.python.core.Py.JavaError(Py.java:552)
	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 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)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:514)
	at org.python.core.PyObject.__call__(PyObject.java:413)
	at org.python.pycode._pyx163.f$0(<buffer>:4)
	at org.python.pycode._pyx163.call_function(<buffer>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1614)
	at org.python.core.Py.exec(Py.java:1658)
	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:276)
	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:131)
	at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:116)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:506)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:494)
	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO asset_list (asset_name, customer_uuid) VALUES (?::text,?::uuid), [Well 2, f1864e92-3051-449b-991c-5edc34a2e557], Cloud_Carefree, , true, false)
	... 26 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO asset_list (asset_name, customer_uuid) VALUES (?::text,?::uuid)": Bad value for type int : Well 2
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:215)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256)
	... 24 more
Caused by: org.postgresql.util.PSQLException: Bad value for type int : Well 2
	at org.postgresql.jdbc.PgResultSet.toInt(PgResultSet.java:2844)
	at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2073)
	at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:623)
	at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:623)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:69)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:28)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:68)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:409)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:852)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:335)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:61)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.server.Server.handle(Server.java:530)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)
	at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:289)
	at org.eclipse.jetty.io.ssl.SslConnection$3.succeeded(SslConnection.java:149)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626)
	at java.lang.Thread.run(null)
Traceback (most recent call last):
  File "<buffer>", line 3, in <module>
	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 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 asset_list (asset_name, customer_uuid) VALUES (?::text,?::uuid), [Well 2, f1864e92-3051-449b-991c-5edc34a2e557], Cloud_Carefree, , true, false)
>>> 

VALUES (?::text,?::uuid)

Why do you have ::text and ::uuid in there?
I believe it should just be VALUES(?,?)

@MMaynardUSG

I got this error without the ::uuid

Caused by: org.postgresql.util.PSQLException: ERROR: column "customer_uuid" is of type uuid but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

So I assumed the issue with the asset_name column was similar so I tried casting it to a text type.

You should be able to use Java’s uuid library:

import java.util.UUID as UUID

name = "Well 2"
customer = UUID.fromString("f1864e92-3051-449b-991c-5edc34a2e557")

asset_uuid = system.db.runPrepUpdate("INSERT INTO asset_list (asset_name, customer_uuid) VALUES (?,?)",[name,customer],"Cloud_Carefree",getKey=1)

print asset_uuid

Thanks for the tip @JordanCClark.
That works for not needing to use the ::uuid in line with the insert statement but I still get the same error in regards to the asset_name column.

Caused by: org.postgresql.util.PSQLException: Bad value for type int : Well 2

Did you try using str(name) in your list? It’s a bit grasping, I know.

import java.util.UUID as UUID

name = "Well 2"
customer = UUID.fromString("f1864e92-3051-449b-991c-5edc34a2e557")

asset_uuid = system.db.runPrepUpdate("INSERT INTO asset_list (asset_name, customer_uuid) VALUES (?,?)",[str(name),customer],"Cloud_Carefree",getKey=1)

print asset_uuid

That’s what I thought when I tried it earlier, but no, that still throws the same error.

Does it run correctly if there’s no space between “Well” and “2”? It really shouldn’t make a difference, but seeing as how we’re grasping at straws here…

No, same thing:

Caused by: org.postgresql.util.PSQLException: Bad value for type int : Well2

You might need to take a closer look at your column type for asset_name.

2 Likes

I tried recreating the table with the following column setup:

CREATE TABLE "public"."asset_list" (
  "asset_name" varchar(255) NOT NULL,
  "asset_uuid" uuid NOT NULL DEFAULT uuid_generate_v4(),
  "customer_uuid" uuid NOT NULL,
  PRIMARY KEY ("asset_uuid")
)
;

and removed the cast from the VALUES, and I am still getting the same error.
The most annoying part is the script is inserting values into the database, it’s just erroring when doing so, and not returning my primary key.

Is this a new Ignition 8 install, or did you restore a .gwbk from an older version onto it?
If you restored a .gwbk, you might try upgrading the JDBC driver used for Postgres.

I believe I have updated the driver recently, but I just did again anyway and it gave the same error.
Interesting discovery though, if I use an int value in the name property “5” in this case, it returns the value from the asset_name column instead of asset_uuid which is set as the primary key.

Ok, I think I have at least a workaround. This issue either seems to be a bug with the JDBC driver or with the runPrepUpdate query not being able to return a UUID as the primary key.
So what I did was pull in pythons UUID library, generated my own UUID and put that in, instead of using the columns default values, and removing the getKey=1 flag.
Working script:

import uuid
new_uuid = str(uuid.uuid4())
name = "Well 2"
customer = "f1864e92-3051-449b-991c-5edc34a2e557"
asset_uuid = system.db.runPrepUpdate("INSERT INTO asset_list (asset_uuid, asset_name, customer_uuid) VALUES (?::uuid,?,?::uuid)",[new_uuid,name,customer],"Cloud_Carefree",getKey=0)
print asset_uuid

I am trying to solve a runPrepUpdate faulted script after updating to 8.9.14

Testing Tag Event script is not able to get alarmEvent.eventId as showed below.
The alarmId_tag is writen ‘None’, elsewhere an eventId is present on the Alarm Status Table.

Here what I found on gateway log

#
([default]KPI/L2/2-Ligne/3-Couteau/Test/test_KPI/_AlarmProductNotFlowing, alarmActive) 

Error executing tag event script: Traceback (most recent call last): 
File "<tagevent:alarmActive>", line 17, in alarmActive 
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 sun.reflect.GeneratedMethodAccessor71.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.runPrepUpdate(
			insert into machine_kpi_event (	machine_id, type, start_time, event_id ) 
			values (?, ?, ?, ?), 
			[L2_test, L2_test-AlarmProductNotFlowing, Fri Jul 10 16:57:05 EDT 2020, 283ffb35-a7cb-4b79-9c9a-b38be4e43973], 
			PostgreSQL, , false, false
		)
#

The problem was solved converting alarmEvent.eventId to string with str() function then casting with “?::uuid” on the prepared statement value key.