[BUG] Postgre getKey error returning uuid key

nminchin · September 21, 2023, 3:21pm

Using getKey=True with system.db.runPrepUpdate where the key field is a uuid type returns an error:

Caused by: org.postgresql.util.PSQLException: Cannot convert the column of type UUID to requested type int.

Is there any way to get around this? Or am I screwed...

Full stack

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 2, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at jdk.internal.reflect.GeneratedMethodAccessor274.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 cfg_jobs (name, added_on, file_ext, gw_location_id, stacking_order) VALUES (?, NOW(),?,?,?), [BOGUS, test, 2, true], , , true, false)


	at org.python.core.Py.JavaError(Py.java:547)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at jdk.internal.reflect.GeneratedMethodAccessor274.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:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:552)

	at org.python.core.PyObject.__call__(PyObject.java:400)

	at org.python.pycode._pyx681.f$0(<input>:3)

	at org.python.pycode._pyx681.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:626)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:614)

	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 cfg_jobs (name, added_on, file_ext, gw_location_id, stacking_order) VALUES (?, NOW(),?,?,?), [BOGUS, test, 2, true], , , true, false)

	... 24 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO cfg_jobs (name, added_on, file_ext, gw_location_id, stacking_order) VALUES (?, NOW(),?,?,?)": Cannot convert the column of type UUID to requested type int.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:293)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 22 more

Caused by: org.postgresql.util.PSQLException: Cannot convert the column of type UUID to requested type int.

	at org.postgresql.jdbc.PgResultSet.readLongValue(PgResultSet.java:3452)

	at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2411)

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

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:25)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:66)

	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)

	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)

	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)

	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)

	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)

	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.server.Server.handle(Server.java:563)

	at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)

	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140)

	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<input>", line 2, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at jdk.internal.reflect.GeneratedMethodAccessor274.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 cfg_jobs (name, added_on, file_ext, gw_location_id, stacking_order) VALUES (?, NOW(),?,?,?), [BOGUS, test, 2, true], , , true, false)

PGriffith · September 21, 2023, 3:25pm

Bad news. Update queries returning a plain int is baked into the JDBC spec, so none of the exposed Ignition query machinery will help here.
You might be able to convince a named query into running the statement and returning the UUID as the 'dataset' as if it was a select.

nminchin · September 21, 2023, 3:31pm

No luck with a named query well... that kills 10 hours of work.. back to the drawing board. Move back to MS SQL

Or I just convert the uuid to text instead. Oh wait.. you said it's locked in as an int. Wow, that's utterly useless then. Faaaaaaarrrr.... me

Ok, second wind of thought. Maybe I will have to generate the uuid from python and insert it in, then I have the key. Postgre JDBC, you suck

pturmel · September 21, 2023, 3:39pm

Are you saying that MS SQL's JDBC driver did return a UUID for getKey=True ?

nminchin · September 21, 2023, 3:41pm

Well, no I haven't tested it. Although I do much prefer SSMS to the postgre IDE's I've tried, so there's that too. Of course, working with timestamps in MS SQL is a bucket of turds though

pturmel · September 21, 2023, 3:43pm

Maybe you could take the money you'd spend on a SQL Server license and spend it on a JetBrains toolkit license instead? Hmm?

DataGrip for the win!

{ I'm pretty sure you'll have funds left over to cover libations for many friends.... }

bkarabinchak.psi · September 21, 2023, 3:52pm

If you're not married to using UUID as your primary key, using SERIAL PRIMARY KEY as your primary key works as expected with getKey=1.

CREATE TABLE test (
   id SERIAL PRIMARY KEY,
	someValue int
);

keyId = system.db.runPrepUpdate("INSERT INTO test (someValue) VALUES (1)",getKey=1,db="testPostgres")
print keyId  #1
keyId = system.db.runPrepUpdate("INSERT INTO test (someValue) VALUES (1)",getKey=1,db="testPostgres")
print keyId  #2

nminchin · September 21, 2023, 4:04pm

I need to use uuids unfortunately.

All good, I got it working by generating it in script and passing it in first Ignore the panic post. It's late here...

bkarabinchak.psi · September 21, 2023, 4:08pm

Ah gotcha. I know you said you were new to postgres so was just throwing that out there. Completely understand the panic post after 10 hours of work, I would be making one too lol.

nminchin · September 22, 2023, 12:38pm

This is another feature I sorely miss from SSMS:

Being able to edit a table that is a dependency of other object(s). I feel myself age a little each time I have to drop everything manually and then recreate them all when working with postgre

Unless someone knows of a solution?? (this topic is digressing...)

Also, is there any way to make DataGrip's popups non-modal?? E.g. the Modify Table popup. Very annoying that I can't look at anything else with one open

pturmel · September 22, 2023, 12:48pm

I right click on the view in DataGrip, go to scripting, pick creation script, into a console. Edit it to suit (will be "create or replace view ....". Run it.

If there are dependencies, I do the same for them, but defer running them--just pile into one console. Drop the dependencies. Run the whole creation stack.

nminchin · September 22, 2023, 12:55pm

That's what I have been doing, but it could be a LOT easier!

Also, this doesn't seem good... Lol, I'm having a fun week...

pturmel · September 22, 2023, 1:30pm

Recovery mode is a startup issue. You should check its logs.

nminchin · September 23, 2023, 3:48am

Ok, DataGrip has converted me. Writing queries with suggestions of tables and fields and syntax highlighting for bad field/table names is amazing

This is a bit... average... though:
Error in "position"

Goto and statusbar only show line:column

humphrey.daniel · September 23, 2023, 4:13am

Apparently F2 is your friend

nminchin · September 23, 2023, 5:34am

It's not a syntax error, so it's not recognised in DataGrip as an error prior to running the query, and it appears that F2 just cycles through the syntax errors in the query

pturmel · September 23, 2023, 2:05pm

Huh. Never noticed this. I must not be making any syntax errors.