The GENERATEDKEY Value is Missing from my CFQUERY Result Structure

Question

I have two different CF11 applications with two different datasources each with its own back-end schema on the same Oracle 12g database. In one datasource dsA, when I use CFQUERY to insert a new record, the result structure contains a GENERATEDKEY value. In datasource dsB, when I run exactly the same code, there is no GENERATEDKEY value in the result structure.

This is the test code I'm running...

<cftry>
    <cfset ds = "dsA"/>

    <cfquery name="insertTest" datasource="#ds#" result="testResult">
        INSERT INTO categories(cat_name)
        VALUES ('testing')  
    </cfquery>

    <cfdump var="#testResult#" label="#ds#">

    <cfcatch>
        <cfdump var="#cfcatch#" label="#ds#"><cfabort>
    </cfcatch>
</cftry>

When I set the datasource to dsA, I get this output. Notice both the GENERATEDKEY and the ROWID values.

When I set the datasource to dsB, I get this output, with no GENERATEDKEY and no ROWID.

As far as I can tell, both Oracle schemas are set up the same way, and both datasources are configured identically. Does anyone have any idea what could cause one query to return the GENERATEDKEY and the other not to? I'm pulling my hair out trying to find a cause for this.

Thanks in advance for any advice.

Answer 1

I had the same problem as you. GENERATEDKEY is missing from the <cfdump> struct. I checked my table again and it turns out I forgot to set my id field as Primary Key and Auto Increment. I altered my table and I can get my GENERATEDKEY again.