[BUG] Postgre getKey error returning uuid key

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)

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.

1 Like

No luck with a named query :frowning: 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

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

Well, no :laughing: 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 :confused:

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.... }

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

I need to use uuids unfortunately.

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

3 Likes

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.

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 :weary:

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

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.

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...
image

1 Like

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

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

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

Goto and statusbar only show line:column :confused:
image

image

Apparently F2 is your friend

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 :frowning:

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

5 Likes