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.

Sanctuary

This is usually fault of JDBC driver. Please check if both of your datasources use same driver. – Subclavian

Tushar, I am told by the server admins that both datasources use the same JDBC driver. I'll ask them to double check, though. – Sanctuary

What about the tables? Any differences (structure, data types, sequences, triggers, etcetera)? – Soriano

Leigh, none that the DBAs have been able to identify. – Sanctuary

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)? – Soriano

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. – Sanctuary

It doesn't sound like you have DBA access to the database(s). You could try running <cfdbinfo> on both datasource/tables to see if you can identify differences. – Titoism

<cfdbinfo datasource="#ds#" name="qryCols" table="categories" type="columns"> <cfdump var="#qryCols#" label="qryCols for #ds#"> – Titoism

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? – Sanctuary

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. – Dotterel

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. – Sanctuary

You would do the INSERT and SELECT in the same query. – Dotterel

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. – Sanctuary

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. – Adjunction

Answer 1

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.

Sanctuary

Answer 2

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.

Boatload

Thanks for sharing. I don't recall if that is something we looked at or not. – Sanctuary