Skip to main content
WolfShade
WolfShade
Legend
June 4, 2020
Question

Question about the result attribute of CFQUERY

  • June 4, 2020
  • 2 replies
  • 1818 views

Hello, all,

 

I've rarely used the 'result' attribute in a CFQUERY.  But I did some looking around, and on the support page for CFQUERY, looking at what is returned I see:

 

resultName.ROWID:  Oracle only.  The ID of an inserted row.  This is not the primary key of the row, although you can retrieve rows based on this ID.
 ...
resultName.GENERATEDKEY:  Supports all databases.  The ID of an inserted row.

 

 

With ROWID, am I to understand that it returns an ID that is _not_ the auto generated key from the database (assuming that the primary key is default uuid/guid), but can be used to get the auto generated key?

 

And with GENERATEDKEY, is this the auto generated key?  Or is it like ROWID?

 

V/r,

 

^ _ ^

This topic has been closed for replies.

2 replies

WolfShade
WolfShadeAuthor
Legend
June 4, 2020

Thank you for that information, Curtis.  Very insightful.

 

I'm not sure if Oracle supports returning ID.  I'll definitely look into that, though.

 

Currently, to my chagrin, we are using CF to generate the UUID/GUID and inserting that in the table, so it can be used for post-processing.  I'm not a fan of this method, because (for whatever reason) Adobe saw fit to use only upper-case letters, and most if not all dbs use upper- and lower-case when generating UUID/GUID.

 

V/r,

 

^ _ ^

D
Community Expert
Dave WattsCommunity Expert
Community Expert
June 4, 2020
If you can't solve it another way, just wrap a CFTRANSACTION around your INSERT and a SELECT to get the newest key. Dave Watts, Eidolon LLC
Dave Watts, Eidolon LLC
WolfShade
WolfShadeAuthor
Legend
June 4, 2020

Hi, Dave, and thanks for your reply.

 

IDK.. that somehow feels a bit hackish, and I think.  And even then, if CFLOCK isn't used, isn't there still a chance that another INSERT could happen before the SELECT?

 

V/r,

 

^ _ ^

CurtangWarrior
CurtangWarrior
Inspiring
June 4, 2020

Hey WolfShade,

 

I can tell you that since CF11 and as recently as CF2018 U9, that the result.generatedkey most certainly does not consistantly contain the auto generated key (though it claims it does).  In fact I just ran a test in case I had been just missing it with a simple table and dumped out the result and as you can see there is not even a GeneratedKey value returned:

FYI this is running CF2018 U9 with PostgreSQL 10.10

So instead when we need to retrieve the ID that was generated we execute a statement like:

insert into demo_u.site_audit_log (user_id, obj_id) values (1,-10) returning ID;

I know some DB's support this, so you may be able to go that route if your DB supports it.

 

Hope that helps,

-Curtis

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 16, 2020

    Hi Curtis_Baker_811,

    The result struct from your insert query should have contained the Generated-Key key. 

    Please report a bug. 

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices