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.

This is usually fault of JDBC driver. Please check if both of your datasources use same driver. — Tushar Bhaware, Oct 27 '16 at 09:27
Tushar, I am told by the server admins that both datasources use the same JDBC driver. I'll ask them to double check, though. — M.Shute, Oct 27 '16 at 11:44
What about the tables? Any differences (structure, data types, sequences, triggers, etcetera)? — Leigh, Oct 27 '16 at 12:39
Does the same thing happen with other INSERT's or just for that table? Did the admins check all of the DSN settings for differences (not just the driver)? — Leigh, Oct 27 '16 at 13:48
No, it's not just the categories tables. That test code was set up after we noticed bugs in one application that we tracked to the GENERATEDKEY. The other application that also uses the GENERATEDKEY value was not experiencing the same problem, so we set up matching test code in each application to see if the problem was in the code. I am told that the server admins checked all of the settings. I'm hoping someone can point me to something specific I can ask them to look at. — M.Shute, Oct 27 '16 at 14:26
It doesn't sound like you have DBA access to the database(s). You could try running on both datasource/tables to see if you can identify differences. — Scott Jibben, Oct 27 '16 at 17:27
Scott, I gave it a try, and other than the fact that they have different tables and data, the two schemas appear to be configured identically. Is there a specific difference I should be looking for? — M.Shute, Oct 27 '16 at 17:41
In SQL Server, after you do an `INSERT`, you can use `SELECT scope_identity() as NEW_ID` to return the new ID of the inserted row. That's what `GENERATEDKEY` is trying to do, but instead of relying on that, find out how to do it in Oracle and just update your query to natively return the new ID after the `INSERT`. — Adrian J. Moreno, Oct 27 '16 at 20:02
Adrian, would the `SELECT scope_identity() as NEW_ID` statement be in the same cfquery tag as the `INSERT` statement? Or would that have to be in another cfquery tag? As much as I want to fix the actual problem, this might be a short-term work-around. — M.Shute, Oct 28 '16 at 13:25
It looks like `scope_identity()` is only for MS SQL Server and won't work with my Oracle database. I can't seem to find an Oracle substitute that works. — M.Shute, Oct 28 '16 at 20:04
Did you find a way to resolve this issue or find a workaround @M.Shute? I am writing a new application using ColdFusion 11 and Oracle 12c. GENERATEDKEY is empty. This is a new install of both products. — clrho, Nov 08 '16 at 18:43

score 1 · Answer 1 · edited Sep 28 '22 at 12:25

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.

edited Sep 28 '22 at 12:25

Nick Petrie

5,364
11
41
50

answered Oct 22 '19 at 12:01

sg552

1,521
6
32
59

Thanks for sharing. I don't recall if that is something we looked at or not. – M.Shute Oct 23 '19 at 13:12

score 0 · Accepted Answer · answered Nov 09 '16 at 13:57

Well, we never figured out why this one datasource was behaving differently from all of the others. They were all using the same driver, but with no other options, the server admins finally agreed to change the dsB datasource driver to Oracle JDBC Thin drivers instead of the Macromedia drivers. That has solved the problem.

It does not really answer the underlying question, but it's a work-around that gets the application working again, so we'll live with it for now.

The GENERATEDKEY Value is Missing from my CFQUERY Result Structure

2 Answers2