Does queryExecute() return an identity column on inserts?

dev support
1

I’ve never had luck getting queryExecute() to return an identity column on an insert, so I always use the q = new query(); pattern when I need to insert a record. I know queryExecute() has had several enhancements, and maybe it’s always been possible… so… is it? If so, how do you do it?

2

This works for me using the MySQL driver:

options = { datasource: "tests", result: "insertResult" };
params = { testvalue: "test" };
sql = "INSERT INTO mytable ( mycolumn ) VALUES ( :testvalue )";
QueryExecute( sql, params, options );
Dump( insertResult.generatedKey );
Dump( insertResult.GENERATED_KEY );

The important part is specifying the result option.

1 Like
3

Awesome… thanks. I assume you would also need to declare the variable insertResult inside the calling function prior to calling queryExecute().

4

If you were executing this inside a function, you could just scope the result variable name to ensure it’s local only:

var options = { datasource: "tests", result: "local.insertResult" };

Simpler than declaring the variable separately.

2 Likes
5

Yup… that would work too! :slight_smile:

6

:+1:

But if you have more than one variable then you can declare the function to search for unknown variables in the Local scope instead of the Variables scope (CFML default). That is done using the localMode attribute:

function someFunction(arg1) localMode=true {
  x = 1;  // x is a Local variable;
  ...
};
2 Likes
7

I didn’t know you could do that… I’m not sure how I feel about that! :slight_smile:

8

If your code doesn’t reference the Variables scope implicitly, i.e. you don’t have Bad implicit Variables scope, and have only used the Good one (Bad and Good examples below):

function someFunc(){
  // Bad - relying on x being in Variables, i.e. Variables.x:
  echo(x);

  // Good - explicitly referencing the Variables scope:  
  echo(variables.x);
}

Then you should feel joy.

If your code is relying on the implicit Variables scope, then you shouldn’t use this.

9

I actually just realized that I have this turned on globally with this.localMode="modern"; which I set in application.cfc. Generally, I like it because it protects my code from race conditions if I forget to declare a variable inside a function. On the downside, I’ve had to fix a few places where a function inside a .cfm file was relying on a variable that was declared in the page somewhere.

That said, I always declare my variables and scope them if necessary. It’s just a habit I guess, but one that I developed for a reason… I’ve run into way too many difficult to find bugs that were associated with either not declaring a variable, or not properly scoping one.

10

Right. The only issue with turning it globally is if you use 3rd party library code that relies on that behavior, or if you are writing a library to be used by others and their Application setting might be different.

But if you can turn it on globally, go for it!

11

Just thought I would point out that doing an INSERT on a PostgreSQL database requires that you add the RETURNING idColumn clause to the end of the query, otherwise, you’ll get your entire inserted row cast as a string.

See: [LDEV-1929] - Lucee

And for some reason, the generated key comes back as a string even if the field is numeric, so you might have to add javaCast("integer", result.generatedKey) if you are needing an actual numeric data type.