SQL getKey Returning Wrong Column

Ignition
, , ,
1

Hello,

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,

Chris

2

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

3

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.

4

The JDBC spec leaves it up to the driver, apparently:
https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#getGeneratedKeys()

5

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.

6

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.

2 Likes
7

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

8

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