# SQL getKey Returning Wrong Column **URL:** \ **Category:** Ignition\ **Tags:** ignition80, database, scripting, ignition81\ **Created:** [February 13, 2023, 9:37pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795 "2023-02-13T21:37:48Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Chris.Hawkey](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Chris.Hawkey](https://forum.inductiveautomation.com/u/Chris.Hawkey)\ **Post date:** [February 13, 2023, 9:37pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/1 "2023-02-13T21:37:48Z")
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 🙂 Thanks all, Chris ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [February 13, 2023, 9:43pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/2 "2023-02-13T21:43:10Z")
I could be wrong (very possible), but I believe that getKey only works if the key is auto generated. ---
**Author:** ![Chris.Hawkey](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Chris.Hawkey](https://forum.inductiveautomation.com/u/Chris.Hawkey)\ **Post date:** [February 13, 2023, 10:00pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/3 "2023-02-13T22:00:43Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [February 13, 2023, 10:40pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/4 "2023-02-13T22:40:22Z")
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()](https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#getGeneratedKeys()) ---
**Author:** ![Chris.Hawkey](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Chris.Hawkey](https://forum.inductiveautomation.com/u/Chris.Hawkey)\ **Post date:** [February 13, 2023, 10:47pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/5 "2023-02-13T22:47:56Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [February 13, 2023, 11:04pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/6 "2023-02-13T23:04:49Z")
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 )](https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#executeUpdate(java.lang.String,int)) 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. ---
**Author:** ![jespinmartin1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jespinmartin1/32/57175_2.png) [@jespinmartin1](https://forum.inductiveautomation.com/u/jespinmartin1)\ **Post date:** [February 14, 2023, 2:19am UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/7 "2023-02-14T02:19:27Z")
I those situations I use a SP but is a bit overkilled. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 12, 2024, 7:52pm UTC](https://forum.inductiveautomation.com/t/sql-getkey-returning-wrong-column/69795/8 "2024-02-12T19:52:37Z")
The true result set from a JDBC driver's `getGeneratedKeys()` implementation is now available via `system.db.runPrepInsert()`. (Yeah, no, not built into Ignition.) > [@Automation Professionals' Integration Toolkit Module](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/89): > > Automation Professionals is pleased to announce a new release of the Integration Toolkit module. This release adds one new scripting function, [system.db.runPrepInsert()](https://www.automation-pros.com/toolkit/doc/utility.html#runprepinsert). It is very similar to IA's [system.db.runPrepUpdate()](https://docs.inductiveautomation.com/display/DOC81/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](https://www.automation-pros.com/ignition/Integration%20Toolkit-2.0.15.240431913-v81.modl) Do note the docu…