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

6 Likes

Well, not really, or not any more, as it turns out.

But this is true. However, an alternative for the OP using UUIDs in PostgreSQL is now available (of no help with UUIDs in MS SQL Server, unfortunately).