How do I get the generated key for an MSSQL "insert" query in Coldfusion 2021 script?

Question

Problem

I want to get the id of the inserted row from a SQL query:

INSERT INTO NameList (Name) VALUES('John')

I need to do this in Coldfusion 2021 script, like in the following:

var sqlQuery = "INSERT INTO NameList (Name) VALUES(?)";
var params = [{ value: "John", cfsqltype: "cf_sql_varchar" }];

try {
    var executedQuery = queryExecute(sqlQuery, params, {/* ...other options... */});
    
    //This is where it fails, because getResult() returns NULL.
    var insertedId = executedQuery.getResult().GENERATEDKEY;
}
catch (any ex) {
    //...report the error, read ex.message
}

But this does not work because getResult() returns NULL.

Research

I've read that the proper way to get the data is not through getResult(), but with getPrefix() from this StackOverflow answer: getting result metadata from coldfusion newQuery() in cfscript

But this does not work, getPrefix() also returns NULL. I cannot find documentation on this function on CFDocs.org or Adobe's documentation (though Adobe's documentation is tough for me to navigate).

The getPrefix() function does not work on cffiddle.org either, so I can't test it otherwise.

Temporary solution

I have been able to get the id of the inserted row using a style I am trying to avoid: passing in a named result variable through queryExecute's option parameter.

//...clipped...
try {
    //The options.result parameter is set to 'queryResult'.
    var executedQuery = queryExecute(sqlQuery, params, { result = "queryResult" });
    
    //This works, though I'm not sure where 'queryResult' exists in scope.
    var insertedId = queryResult.GENERATEDKEY;
}
//...clipped...

I don't know what scope queryResult is in (is it global?), but this works. I would prefer passing in a local variable, but passing in { result = "someLocalVar" } led to bugs.

Is there a way to pass in a local variable? I would find this acceptable.

Conclusion

I want to get the id of the inserted row using Coldfusion 2021 script. I would prefer not using the options.result parameter of queryExecute(), but instead using getResult() or a similar mechanism.

If that's not possible, then I would prefer being able to pass in a local variable to the options.result parameter of queryExecute().

If that's not possible, then I'd like to know at least what scope the variable referenced by options.result is.

Answer 1

var executedQuery = queryExecute(sqlQuery, params, { result = "queryResult" });

Take a look at the parameters, you are specifying the result to be queryResult. Therefor that has the generated key.

The docs indicate that you do not need to specify the result ( https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryexecute.html ) and that re result/return of queryExecute result the resul but this seams to not work as per docs in ACF2021.

I would suggest below.

try {
  var executedQuery = ''; 
  queryExecute(sqlQuery, params, { result = "executedQuery" });
  var insertedId = executedQuery.GENERATEDKEY;
}