• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Nested CFQuery Uses Wrong Datasource

New Here ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques

Views

1.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

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).

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

I can see your point.  While my coding style is a bit more methodical than what you described, if I were to attempt something that you described, I would expect it to work.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 15, 2009 Apr 15, 2009

Copy link to clipboard

Copied

Viewing it as a black box, I could 'kind of' understand the expection.  But using the original illustration, I would not expect this to work

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

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

       select * from table

     </cfquery>

     insert into test values('blah')

</cfquery>

Nor would I expect to be able to open a database connection, from within another connection.  Opening and manipulating two connections separately, yes. But opening one from within another? No.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

Right. Do you think this is a problem?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 14, 2009 Apr 14, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 15, 2009 Apr 15, 2009

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 15, 2009 Apr 15, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 15, 2009 Apr 15, 2009

Copy link to clipboard

Copied

Josh,

I don't know if we're discarding the behavior you're seeing; rather, I believe we've acknowledged that the behavior is a 'limitation' (of sorts) with the CF server. I can't say that CF should be able to handle simultaneous connections to multiple datasources but, as cfSearching notes, if you feel it's a bug, do submit it. Can't hurt!

At least the workaround is fairly simple and requires little extra typing or code. When I tested it yesterday, just adding a cfset to hold the converted value as a string to pass into the insert statement would take care of the problem (and I can absolutely see why this behavior would be frustrating!).

Best,

Craig

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 15, 2009 Apr 15, 2009

Copy link to clipboard

Copied

craigkaminsky wrote:

I don't know if we're discarding the behavior you're seeing; rather, I believe we've acknowledged that the behavior is a 'limitation' (of sorts) with the CF server. I can't say that CF should be able to handle simultaneous connections to multiple datasources but, as cfSearching notes, if you feel it's a bug, do submit it. Can't hurt!


Yes, it is not about dismissing the behavior.  That was confirmed and an alternative suggested.  The OP asked for opinions, and they were given.  They are certainly free to disagree with them. But given that the behavior cannot be changed, there is not much else to do except use the alternative and/or submit a bug report.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 15, 2009 Apr 15, 2009

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation