And just to be clear, when I say "feature" I actually mean "completely broken behaviour," though for a change of pace it's not with the compiler. Probably. Hopefully.
Consider this innocent-seeming code:
insert into test (s) values ( 'dsn_A - record 1' )
Every CF app has this (or equivalent) in a million places. Now let's change it slightly to make the string we're inserting dynamic (I'll show the 'getMsg' UDF in a moment):
insert into test (s) values ( 'dsn_A - record 2 #getMsg()#' )
So far so good. Now here's the UDF:
select count(*) as c from test
What would you expect this to do? Return a string, of course, containing the recordcount of the 'test' table in "dsn_A", and it does this perfectly. It also has the glorious side effect of changing the outer CFQUERY's datasource to "dsn_A". To put that another way, a given query does NOT execute with the datasource passed to the opening CFQUERY tag. Rather, it executes with the datasource passed to the last CFQUERY tag before the closing CFQUERY tag. Don't believe me? Here's a test case: Play the best friv games web-site online. The most popular collection of friv games are presented on this mega portal.
select count(*) as c from test insert into test (s) values ( 'dsn_A - record 1' ) insert into test (s) values ( 'dsn_B - record 1' ) select * from test select * from test insert into test (s) values ( 'dsn_A - record 2 #getMsg()#' ) insert into test (s) values ( 'dsn_B - record 2 #getMsg()#' ) select * from test select * from test
In order to run it, you'll need to two DSNs configured ("dsn_A" and "dsn_B"), pointed at two distinct databases, each with a 'test' table containing a single varchar column named 's'.
This was tested on CF8 and CF9, with and without CFQUERYPARAM. I also tested with implicit and explicit datasources on CF9. Same behaviour in all cases. Wheeee…
Did you file a bug?
http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html
-Adam
Not really sure. I went and filled out the form, but it never gave me any indication it submitted (success or error). I assume that means I didn't, but hard to say. Any chance we'll get an HTML interface so we can use normal controls (like SELECTs that respond to keyboard input) and see what's happening with submissions without remembering to preconfigure a packet sniffer?
Actually, I reloaded and found it. Bug 81968. With HTML I'd have been able to copy & paste that number rather than having to rekey it too. ;)
Thanks. I'll keep an eye on it and see if we can get it fixed for 9.01.
As far an HTML-based bug tracker goes… no problem. We'll get to work on it.
-Adam
There is a bug filed for this. I filed it a few months ago.
Took me a while, but I found it:
http://www.coldfusionjedi.com/index.cfm/2009/8/11/Interesting-CFQUERY-Bug
Reported back in August. :)
I was confused at first because you have a mistake in your example code. Your second and third example code blocks both use 'dsn_A'. I think you meant one of those to be 'dsn_B'.
Ray, you (and David) win the prize. And no, Adam, I didn't search the bugbase first. Bad Developer. :)
Jake, I intentionally used dsn_A for all the snippets; the test case uses both DSNs and you'll see the side effects of it being reset in the dumps. I was trying to just set the state with the involved statements first – no weirdness, no complexity – but obviously didn't do an exemplary job at it.
Ok, well I guess I'm still confused then. You said, "it executes with the datasource passed to the last CFQUERY tag before the closing CFQUERY tag" but looking at your snippets before that statement, they all use the same datasource. So I don't see a problem, your inner query is updating the outer query's datasource, but it was the same to begin with. Or am I just being thick?
No, you're correct. With the initial three snippets, the code will behave as desired, because the DSN is overwritten with the same value. But if you look at the full test case, you'll see where it'll fail (on the fourth INSERT query).
The first time I saw someone run a CFQuery tag *inside* another CFQuery tag, I was blown away that it worked AT ALL. Keep in mind, that was *inline* nested CFQuery tags. The use of a function makes it a bit more natural feeling.
@Ben -
Sorry to go off another tangent.
I'm curious, why would someone want to run an inline nested CFQuery for anything?
I mean it makes sense to me, in modularizing code to put queries in their own functions and then you'd
need to do soemthing like this.
But still I'm interested in what the old-school example you ran into. I can't ever see a reason for nesting
a CFQuery inside another. Because at that point you can do whatever you need to in almost T-SQL syntax.
@Ali,
I think the person was doing something like:
select id from users where …..
select * from orders where u.id = #users.id#
There's no reason that the nested CFQuery couldn't just be moved outside the parent CFQuery. Just struct me as very odd the first time I saw this.
Does anyone know if this bug was fixed? If so, do you have the bug ID? I can't seem to find it.
CF10 is in public beta – someone test it. :)
I can confirm this bug was not fixed in ColdFusion 10.