Missing GENERATEDKEY from Query Result

Question

I have some code which was using GENERATEDKEY from result attribute of cfquery tag from last few years. I recently noticed that it is no more the part of structure returned by Result.

<cfquery name="qryTest" datasource="#DSN#" result="sResult">
  INSERT INTO users(fName, lName, City) VALUES(
  'Test1', 'Test2', 'Test3')
</cfquery>
<cfdump var="#sResult#">

I only get CACHED, EXECUTIONTIME, RECORDCOUNT & SQL. Environment is CF2016, SQL Server 2014

There is an option in the datasource settings `Disable Autogenerated Keys` to `Disable retrieval of autogenerated keys`. Is that checked? — rrk, Nov 18 '20 at 21:19
Did you recently switch JDBC driver or datasource? Because CF is relying on the [`java.sql.Statement` interface method `getGeneratedKeys`](https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()), which has to be implemented accordingly. — Alex, Nov 18 '20 at 23:15
@CFML_Developer Could you please cross check the you have get IDENTITYCOL in the structure ? — Kannan.P, Nov 19 '20 at 14:27
Serious question - have you turned it off and on again? :-) Asking because I had a similar problem a couple of years ago, on production on an online shop, where some part of CF/JDBC-driver/MSSQL stopped returning `result.GENERATEDKEY` where it had been working for years previously, and there were no recent changes. I put in some fallback code to grab the identity another way, as I could not justify restart during peak times, but eventually after a CF service restart + SQL Server restart, the original problem went away and never returned, so I never discovered what caused it in the first place! — Sev Roberts, Nov 19 '20 at 16:25
@SevRoberts, I am not sure. Server is administered by a different person. I saw this behavior on 2 servers and both of them get rebooted periodically for maintenance. I will try to see if I can get a restart on them. — CFML_Developer, Nov 19 '20 at 17:54

score 1 · Answer 1 · answered Nov 18 '20 at 22:30

1

Are you trying to do something like this?

<cfquery name="qryTest" datasource="#DSN#">
  INSERT 
  INTO users(fName, lName, City) 
  OUTPUT inserted.id 
  VALUES('Test1', 'Test2', 'Test3')
</cfquery>

answered Nov 18 '20 at 22:30

James A Mohler

11,060
15
46
72

Actually the real query is an upsert. So based on if it was either an Update or Insert, it used to rely on generatedkey. If that exists, it meant it was an insert and proceeded accordingly. I am aware of OUTPUT clause and can modify the code. But I am just trying to determine why current code stopped working. I believed that generatedkey will always be returned for sql server insert. That is not true, I guess. – CFML_Developer Nov 19 '20 at 04:33

score 1 · Answer 2 · answered Nov 19 '20 at 14:46

1

I just go through you issue. I believe that you table ( users ) does not have any Constraints - PK ( Primary Key ) and IDENTITYCOL ( Auto increment ) options. So that it's not return any IDENTITYCOL & GENERATEDKEY keys in your result structure. Here,

 <cfquery name="InsertData1" datasource="testmssql" result="test" >
     INSERT INTO loginDetails VALUES( 'xxx','yyy') 
 </cfquery>

I've insert the two column data and my table loginDetails have PK & Identitycol options. So while dump result it's should return the structure with key & values for CACHED,EXECUTIONTIME,GENERATEDKEY,IDENTITYCOL,RECORDCOUNT and SQL Like below image.

<cfquery name="InsertData1" datasource="testmssql" result="test" >
   INSERT INTO test2 VALUES( 'xxx','yyy')
</cfquery>

Here my table test2 I didn't set the Autoincrements ( Constraints ) options. So it's not return GENERATEDKEY,IDENTITYCOL in result structure. Like my below image.

So I suggest please check you DB side about that table have proper Constraints or not.

answered Nov 19 '20 at 14:46

Kannan.P

1,263
7
14

Given the first paragraph of the question, I disagree. – Dan Bracuk Nov 19 '20 at 14:52
Why,? May be they are OFF the IDENTITYCOL options. That time we should face the same issue. Because his result key didn't return GENERATEDKEY,IDENTITYCOL. Which is entire related with autoIncrement options only. – Kannan.P Nov 19 '20 at 14:54
Nah. None of your assumption is true. Not only I have a PK but I also have an Identity column. And matter of fact is it used to work on same environment earlier (till 4 months ago for sure, but could have stopped working anytime after that.) – CFML_Developer Nov 19 '20 at 15:02
Thank you. It turns out I didn't have `PK` and `Auto increment` on my table. I enabled it and I can get my `generated_key`. Thanks. – sg552 Oct 05 '21 at 14:50

Missing GENERATEDKEY from Query Result

2 Answers2