PGSQL Error on runPrepUpdate

mitchell-ACS · June 10, 2019, 6:16pm

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)
>>>

MMaynard · June 10, 2019, 6:22pm

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

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

mitchell-ACS · June 10, 2019, 6:52pm

@MMaynard

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.

JordanCClark · June 10, 2019, 7:22pm

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

mitchell-ACS · June 10, 2019, 7:27pm

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

JordanCClark · June 10, 2019, 7:34pm

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

mitchell-ACS · June 10, 2019, 8:05pm

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

KathyApplebaum · June 10, 2019, 8:10pm

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…

mitchell-ACS · June 10, 2019, 8:15pm

No, same thing:

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

pturmel · June 10, 2019, 8:16pm

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

mitchell-ACS · June 10, 2019, 8:50pm

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.

PGriffith · June 10, 2019, 8:53pm

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.

mitchell-ACS · June 10, 2019, 9:01pm

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.

mitchell-ACS · June 10, 2019, 9:10pm

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

ybfautom · July 9, 2020, 7:26pm

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.

ybfautom · July 10, 2020, 9:17pm

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
		)
#

ybfautom · July 13, 2020, 5:54pm

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