[BUG] Postgre getKey error returning uuid key

[nminchin]

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]

@nminchin

Or am I screwed...

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]

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]

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

[nminchin]

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]

@nminchin

do much prefer SSMS to the postgre IDE's I've tried

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]

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]

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]

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]

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]

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]

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]

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

[nminchin]

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]

Apparently F2 is your friend

In DataGrip error messages, what does "position" mean?

[nminchin]

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]

@nminchin

Error in "position"

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

[pturmel]

@PGriffith

Update queries returning a plain int is baked into the JDBC spec

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

@PGriffith

none of the exposed Ignition query machinery will help

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

Automation Professionals' Integration Toolkit Module

Automation Professionals is pleased to announce a new release of the Integration Toolkit module. This release adds one new scripting function, system.db.runPrepInsert(). It is very similar to IA's system.db.runPrepUpdate(), intepreting the query, args, database, and tx arguments in the same fashion. The getKey argument is omitted as moot, as this function always retrieves generated keys. The skipAudit optional argument is unimplemented. For Ignition v8.1+: v2.0.15.240431913 Do note the docu…