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?
^ _ ^
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,
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.
^ _ ^
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?
^ _ ^
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.
^ _ ^
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
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.
^ _ ^
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;
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.
^ _ ^