One of the nicer features in ColdFusion 11 was the addition of queryExecute. It lets you run a query from cfscript easier than using the component based system that was added in the past. However, a StackOverflow user discovered an interesting bug with it.
Scott J. posted a question to StackOverflow about something odd he saw in ColdFusion's debugging output when he used queryExecute. Imagine the following simple call:
data = queryExecute("select * from tblblogentries limit 0,1", {}, {datasource:"myblog"});
If you turn on ColdFusion Debugging, you would expect to see "data" as a query. Instead he saw this:
It looks like ColdFusion is assigning a temporary variable to the query before returning it to the variable you specify. If you run multiple queryExecute calls they use the same variable. If you use a tag-based query it works correctly. If you remember, the debug templates are simply ColdFusion files. I checked in there to ensure there wasn't a bug, but as far as I can see, it is working right.
Also, for fun, I did: _queryname_var0 = 9; and yep - it gets overwritten. Oddly it won't exist in the Variables scope, but if you use it as a variable and then run queryExecute, it gets removed completely. I wouldn't go so far as to say queryExecute is unsafe to use, but, you may want to ensure you are not using a variable with the same name. It also makes debugging a bit more difficult as you will have multiple items in the report with the same name.
I've filed a bug report: https://bugbase.adobe.com/index.cfm?event=bug&id=3836702
Archived Comments
What if you pass a NAME value in the options struct (third arg)?
No change, and doesn't seem to do anything.
As I recall ACF does the same/simular thing when you use "new query()" in cfscript as well, althought at least there you can specify a name. The name becomes real important if you are using query caching if I remember correctly and where I discovered this behavior.
The more I think about this, the more it bugs me. The person who wrote the code *knew* they were doing something bad, hence the attempt at a name that wouldn't conflict. But why in the heck was it even necessary? queryExecute should run Java code to do whatever and simply return the result to the variable, like *every other* CF function does.
I would think that Adam is correct, that by putting the name in the last argument struct (queryOptions) would clear it up:
qryTest = queryExecute('SELECT * FROM art', {}, {datasource="cfartgallery", name="myresult"});
As you noted it doesn't work!
My line of thinking is that the query is executed in the queryExecute() function before the results get assigned back to qryTest, the debug output wouldn't know about qryTest yet. I would think that there should be something in the queryOptions argument that "names" the query for debug output.
BTW, Adam your blog post on queryExecute() saved me a lot of time getting started with script coding vs tag coding.
Scott: you're dead right. Ray seems to be positioning this as
qryTest = queryExecute()
being analogous to
[cfquery name="qryTest"]
But it's not. The inner workings of queryExecute() haven't (and shouldn't have) any idea about what happens on the other side of that assignment statement, and accordingly what variable its result ultimately gets assigned to. That makes no sense. Would one expect the inner workings of listAppend() to know that ultimately its result gets assigned to a variable "myList"? Why is that actually relevant to anything?
With cfquery, the name attribute value is both a label for the query debug output, plus the name of the variable to assign in the "caller scope" (to use custom tag vernacular). But that is a coincidence of convenience as much as anything else.
This is why I thought providing the function an option "name" might mitigate the situation (if Adobe were thinking about how debugging ought to work), but no. And I think it's not unreasonable for them to have overlooked this.
Ray is perhaps thinking that when the CFML is parsed by the java compiler, then the "qryTest = queryExecute()" statement can be rejigged to indicate that qryTest needs to be the "name" of the query as well, but that would mean a special parsing except for queryExecute() to do that, and building exceptions in like that are a bit grim. Not the way to resolve this.
Having thought about it, I'm not actually sure there is an issue here. I have not decompiled the code, but I would imagine the label being seen in the debug output is simply that: a label. Not a variable which might collide with something else, or cause thread-safety issues?
The one issue then is it's convenient to look through debug for a specifically-named query debug output, so having the ability to provide queryExecute() a label makes some sense, I guess.
Thoughts?
--
Adam
"Why is that actually relevant to anything?"
My point was this, Adam. We have multiple BIFs, right? In no other case do we have a problem where CF needs to make a temporary variable in the Variables scope.
When I do: x = now(); CF doesn't need to get the now value, assign it temporarily, and *then* copy it to Variables. To Scott's point, since the query is synchronous, execution has stopped and the value of my left hand variable is waiting for the query to end. That's the way I see it anyway. I honestly don't see why you think now() is any different than queryExecute()? (Outside of one being more complex of course. ;)
The "name" of the query is just a convenience for me. It is pretty quick to find the debug output for a query if it can be unique.
I suspect that having internal queries that all have the same name could cause some threading issues as you've discovered.
If the "name" attribute of the query can't be set, wouldn't it have made sense from Adobe's perspective to create a variable that included a CreateUUID()-type unique id?