Skip to main content
J
joshfrese
Participant
April 14, 2009
Question

Nested CFQuery Uses Wrong Datasource

  • April 14, 2009
  • 6 replies
  • 1502 views

I'm using CF MX 7.0.2, and it's come to my attention that there is a problem with how CF handles nested queries. To illustrate it simply:

<cfquery name="insert" datasource="one">

     <cfquery name="select" datasource="two">

       select * from table

     </cfquery>

     insert into test values('blah')

</cfquery>

This set of code will attempt to insert the 'blah' value into datasource "two"! Not the expected "one". While this seems like a terrible way to make things, consider a more common scenario:

<cfquery name="insert" datasource="one">

   insert into test values('#myfunction()#')

</cfquery>

<cffunction name="myfunction">

  ....

  <cfquery name="select" datasource="two">

    select * from table

  </cfquery>

  ....

  <cfreturn "blah">

</cffunction>

Again, this will attempt to insert into datasource "two". Please tell me there's a hotfix I can't find for this...

This topic has been closed for replies.

6 replies

J
joshfreseAuthor
Participant
April 15, 2009

Submitted as a bug ticket. (tossed into a black hole for all eternity)

J
joshfreseAuthor
Participant
April 15, 2009

My real world example is more like this:

<cfquery name="insert" datasource="one">

  insert into table(one,two,three) values('1','#convert_unit(value,"gallons")#','3')

</cfquery>

<cffunction name="convert_unit">

    ..

    <cfquery name="units" datasource="two" cachedwithin="1">

       select * from units

    </cfquery>

    ...

    <!--- using the data do the conversion --->

    ...

    <cfreturn final_value>

</cffunction>

It also happens to be that datasources "one" and "two" have all the same tables, they are replicated using MySQL... "two" is a slave, so, by CF inserting to "two" instead of "one", it screwed up the replicated data between the two servers. I find this to be nonsensical behavior from a "programming language". I'm suprised to see responses that discard it as a problem!

-
-__cfSearching__-
Inspiring
April 15, 2009

I agree with the opinion that it should not do this without some sort of warning or exception,  but still would not expect this to work.  However, opinions really do not matter one way or the other here, as they do not change the existing behavior.  If you feel it is a bug, submit a bug report.

J
joshfreseAuthor
Participant
April 14, 2009

Right. Do you think this is a problem?

-
-__cfSearching__-
Inspiring
April 14, 2009

Honestly trying to open another query, while already _within_ a query strikes me as very wrong and I would not have expected it to work. Though I would have expected some sort of exception to occur.  Some databases (ms sql, et. al.) have the ability to mix communications with two database servers in a single statement, so you might consider that option. But afaik basic datasources do not have that ability.

J
joshfreseAuthor
Participant
April 14, 2009

Thanks for your response. It doesn't sound like either of you view this as a problem. I know how to avoid it. Isn't it reasonable for me to be able to use my own functions within a query without it potentially messing up my data? It doesn't matter what my function does.

Is this the intended behavior of Coldfusion?

Is this the way anyone would expect or want it to behave?

If the answer is no to either of the questions, I think it should be changed.

C
craigkaminsky
Inspiring
April 14, 2009

My sense is that the issue arises when you have an open connection to a datasource while you are trying to access another datasource. When you use the same datasource in the inner cfquery and the cffunction, it all works without a hitch. Along those same lines, my examples' cfqueries open, run and close before another cfquery runs. In both your examples, it would seem like this is why you're seeing the behavior (trying to open a connection to new datasource while another is running).

C
craigkaminsky
Inspiring
April 14, 2009

I'm not sure what you gain by nesting the queries and not keeping them separate but thought you'd might like to know that the two scenarios you described function the same in CF8 as you note for CF7.

The following code works fine for me on both CF7 and 8:

Opt 1:

<cfquery name="q1" datasource="dsn1">

select role from end_users

</cfquery>

<cfquery name="q2" datasource="dsn2">

insert into role (id) values('#q1.role#')

</cfquery>

Opt 2:

<cfscript>

myStr = testFunc();

</cfscript>

<cfquery name="q1" datasource="dsn1">

insert into role (id) values('#myStr#')

</cfquery>

<cffunction name="testFunc" returntype="string">

<cfquery name="q2" datasource="dsn2">

select role from end_users

</cfquery>

<cfreturn q2.role />

</cffunction>

Doesn't seem like either one would be a significant rewrite or movement of your code.

Anyway, I thought you'd might like to know that both your scenarios work the same in 8 as 7 and, as a result, I don't think there would be a hotfix for CF7.

D
Dan_Bracuk
Inspiring
April 14, 2009

This more common scenario you mention, exactly what would that function be returning?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices