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.
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.
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.
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.
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;
}