13 Replies Latest reply on Apr 15, 2009 1:06 PM by joshfrese

    Nested CFQuery Uses Wrong Datasource

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

        • 1. Re: Nested CFQuery Uses Wrong Datasource
          Dan Bracuk Level 5

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

          • 2. Re: Nested CFQuery Uses Wrong Datasource
            craigkaminsky Level 3

            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.

            • 3. Re: Nested CFQuery Uses Wrong Datasource
              Level 1

              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.

              • 4. Re: Nested CFQuery Uses Wrong Datasource
                craigkaminsky Level 3

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

                • 5. Re: Nested CFQuery Uses Wrong Datasource
                  Level 1

                  Right. Do you think this is a problem?

                  • 6. Re: Nested CFQuery Uses Wrong Datasource
                    -==cfSearching==- Level 4

                    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.

                    • 7. Re: Nested CFQuery Uses Wrong Datasource
                      Dan Bracuk Level 5

                      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.

                      • 8. Re: Nested CFQuery Uses Wrong Datasource
                        -==cfSearching==- Level 4

                        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.

                        • 9. Re: Nested CFQuery Uses Wrong Datasource
                          Level 1

                          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!

                          • 10. Re: Nested CFQuery Uses Wrong Datasource
                            -==cfSearching==- Level 4

                            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.

                            • 11. Re: Nested CFQuery Uses Wrong Datasource
                              craigkaminsky Level 3

                              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

                              • 12. Re: Nested CFQuery Uses Wrong Datasource
                                -==cfSearching==- Level 4

                                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.

                                • 13. Re: Nested CFQuery Uses Wrong Datasource
                                  Level 1

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