SQL getKey Returning Wrong Column


I am attempting to use the system.db.runPrepUpdate() function with the getKey parameter set to True.

However I notice the SQL server is returning a column that is not actually the primary key.

This could be due to the fact that the primary key is a non-integer datatype, or due to the fact that there are triggers on the table generating the key... not sure.

I think my solution will lie somewhere in the query / sql db configuration.
But just curious if anyone knew a way around this.
I've tried the OUTPUT INSERTED in the query as seen on stackoverflow but Ignition tells me: "A result set was generated for update"

Any help would be appreciated :slight_smile:

Thanks all,


I could be wrong (very possible), but I believe that getKey only works if the key is auto generated.

That could definitely be it. Just curious why (and how) it decides to send back the next integer column instead, which is an Identity column, but works.

The JDBC spec leaves it up to the driver, apparently:

Thank you for that, do you have any idea how ignition specifies the key? From the looks of the function it does not appear to be something I can change.

We're not specifying the key. This is all abstracted away by JDBC.
We tell the statement "here's an execute query to run, give me a result set with generated keys": Statement (Java SE 11 & JDK 11 )

After that's been executed, JDBC is documented to return the generated keys in the result set from calling getGeneratedKeys(); the actual mechanism for doing that is entirely up to the JDBC driver.


I those situations I use a SP but is a bit overkilled.

The true result set from a JDBC driver's getGeneratedKeys() implementation is now available via system.db.runPrepInsert(). (Yeah, no, not built into Ignition.)