Question about the result attribute of CFQUERY

[WolfShade]

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,

^ _ ^

[CurtangWarrior]

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]

Hi Curtis_Baker_811,

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

Please report a bug. 

[WolfShade]

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,

^ _ ^

[Dave Watts]

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

[WolfShade]

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,

^ _ ^

[Dave Watts]

No, that's specifically what CFTRANSACTION is for. It locks database access. Obviously, you want to be careful about doing this as it creates bottlenecks in your database just like CFLOCK does in your CFML code. There are four different transaction levels, but the default one, "read committed", will prevent anything from happening between your INSERT and your SELECT. https://www.google.com/search?q=database+transaction+levels If you think about it, without transactions, you could have the same problem with a single query. Something has to happen to prevent concurrent queries using the same tables from interfering with your query. And that thing is transaction isolation. Dave Watts, Eidolon LLC

[WolfShade]

Understood.  But I'm super paranoid, sometimes, and I keep thinking of one of the CF DEV 2020 presentations that I attended that pointed out some unintended (or just plain thoughtless) consequences that can happen while using CFTRANSACTION.  🙂

Speaking of which, thank you.  I attended your presentation and enjoyed it.

V/r,

^ _ ^

[Dave Watts]

Programming can be dangerous! When CFLOCK came out, I saw all kinds of egregious overuse of it that was causing bottlenecks like you wouldn't believe. The same is true for CFTRANSACTION, although the locks are in the database instead of in CFML operations. So, it's important to not overuse it, but it can also be important not to underuse it. And it can be difficult for programmers to think about concurrency in general.

Thank you for your kind words! I'm going to try to put the whole thing together in a YouTube format and let Adobe/Carahsoft give it out, but that's going to take a while.

Dave Watts, Eidolon LLC

[WolfShade]

Awesome!  Looking forward to that link, when it's available.  I mean, I've got the email that was sent out with the links to the recorded versions on Carahsoft's servers, but much prefer the YT versions.

V/r,

^ _ ^

[CurtangWarrior]

I'm pretty sure Oracle does.  I believe you use insert (somethign) values (something) returing <column> into :somevar. then you can select :somevar from dual;

I don't have an oracle instance to test with (and in all fairness havent used oracle in 5 years) but I believe something like the following should work:

<cfquery name="qName" datasource="whatever">

insert into someTable (someColumn) values (someValue) returning IDColumn into :returnID;

select :returnID as newID from dual;

</cfquery>

<cfoutput>#qName.newID#</cfquery>

[WolfShade]

Curtis,

Good to know.  I don't currently have any projects that I can try that on.  But will be sure to ask our DBA to use Oracle generated UUID/GUID on the next project.

V/r,

^ _ ^