17 Replies Latest reply on Nov 5, 2015 1:00 PM by BKBK

    How to use variable in cfQuery where?

    2Charlie Level 1

      I have this variable:

       

      strURL = siteURL not like '/training%' and siteURL not like '/webadmin%'

       

      Now in my cfquery:

       

      <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

        select *   from qryContent where #strURL#

      </cfquery>

       

      And it failed to execute. I put a log after the cfquery and it failed to generate anything too.

        • 1. Re: How to use variable in cfQuery where?
          vermasumit Level 1

          Is there any reason why you are using the variable? Ideally you should be doing this:

           

          <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

            select *   from qryContent

            where siteURL not like <cfqueryparam value="/training%" cfsqltype="cf_sql_varchar" />

            and siteURL not like <cfqueryparam value="/webadmin%" cfsqltype="cf_sql_varchar" />

          </cfquery>

          • 2. Re: How to use variable in cfQuery where?
            tribule Level 2

            Try:

             

            <cfset strURL = "siteURL not like '/training%' and siteURL not like '/webadmin%'">

            <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

              select * from qryContent where #strURL#

            </cfquery>

             

            You need to make the strUrl a complete string.

            • 3. Re: How to use variable in cfQuery where?
              2Charlie Level 1

              Yes. Here's larger view of the whole process. I was thinking about putting the <cfloop> inside of the cfquery in the where clause but also causes issue too.

               

              <cfset todayDate = DateFormat(Now(), "mm/dd/yyyy")>

              <cfset strURL = "">

              <cfscript>

                  // get data from the "My Element" custom element

                  data = application.ADF.ceData.getCEData(customElementName="Subsite Exclusion");

                </cfscript>

               

                <cfloop from="1" to="#arrayLen(data)#" index="itm">

                <!---// renders data from the uniqueID and Title fields from the element --->

                <cfif #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# gte #todayDate# || #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# is "">

                <cfif #itm# eq 1>

                <cfscript>

                  strURL = "subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

                  </cfscript>

                <cfelse>

                <cfscript>

                  strURL &= " and subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

                  </cfscript>

                </cfif>

                </cfif>

                </cfloop>

               

                <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                select *

                from qryContent

                where #strURL#

              </cfquery>

              • 4. Re: How to use variable in cfQuery where?
                tribule Level 2

                Building dynamic queries can be tricky - you just need to ensure that you start out with a base query and then add the AND conditions on to the end at each stage, thus ensuring the SQL is syntactically correct. Why not just output the SQL without running it in a CFQUERY to see if all looks good, first? Can't help you do that; that's just something you'll have to figure out in the coding

                 

                Btw, don't use CFSCRIPT if you don't need to. Going into CFSCRIPT mode just to set a variable instead of using CFSET is overkill. Also you don't need <cfif #itm# eq 1>, you just need <cfif itm eq 1> etc - the #'s are superfluous inside CF tags, as the values are already being evaluated.

                • 5. Re: How to use variable in cfQuery where?
                  2Charlie Level 1

                  Thanks for the tip.

                   

                  No debugging works. I've tried cftracing and cflog but nothing shows up.

                   

                  So, I tried this.

                   

                  <cfset strURL = "'/training%'">

                  <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                    select *

                    from qryContent

                    where subSiteUrl not like "#strURL#"

                  </cfquery>

                   

                  This does not work either. I actually have an internal server error. I have read this article and it seemed that using session variable should work but what I've tried is no working.

                   

                  Adam Cameron's Dev Blog: What one can and cannot do with <cfqueryparam>

                  • 6. Re: How to use variable in cfQuery where?
                    tribule Level 2

                    Your where subSiteUrl not like "#strURL#" is no good since you must use single quotes in the SQL, so you need:

                     

                    where subSiteUrl not like '#strURL#'

                     

                    and then strURL would just need to be literally the string (with no quotes): /training% which you would set with:

                     

                     

                    <cfset strURL = "/training%">

                     

                    Your CFSET has single quotes already in it. Keep the single quotes in the SQL query, but not in the variable. Get this working without cfqueryparam first is my advice, so that the concept/logic works first, and just print out the SQL without the CFQUERY so you can just see the SQL being generated to see if it is well-formed, first. Try running that SQL in your database manager as well, beforehand. This is how I have approached the same issue in the past and I have dynamic queries with many dozens of strings being joined together.

                    • 7. Re: How to use variable in cfQuery where?
                      2Charlie Level 1

                      Thank you, tribule. That is very helpful. Eventually I need to generate everything for the "where" clause of the SQL. Therefore, it will be something like this:

                       

                      <cfset strURL = "'">

                      <cfloop from="1" to="#arrayLen(data)#" index="itm">

                        <!---// renders data from the uniqueID and Title fields from the element --->

                        <cfif #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# gte #todayDate# || #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# is "">

                        <cfif itm eq 1>

                        <cfscript>

                          strURL = "subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

                          </cfscript>

                        <cfelse>

                        <cfscript>

                          strURL &= " and subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

                          </cfscript>

                        </cfif>

                        </cfif>

                      </cfloop>

                       

                      <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                        select *

                        from qryContent

                        where '#strURL#'

                      </cfquery>

                       

                      If the where clause requires a single quote like '#strURL#' to work then I have a problem because the "not like" needs a single quote; therefore, the "where" clause now have two single quotes in the sql query.

                      • 9. Re: How to use variable in cfQuery where?
                        2Charlie Level 1

                        Thanks, I will take a look at that as well. But I'm curious why the following is not working either.

                         

                        <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                          select *

                          from qryContent

                          where

                          <!---// loop over results (All the records from the Custom Element) --->

                          <cfloop from="1" to="#arrayLen(data)#" index="itm">

                            <!---// renders data from the uniqueID and Title fields from the element --->

                            <cfif #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# gte #todayDate# || #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# is "">

                            <cfif itm eq 1>

                            subSiteUrl not like '#data[itm].values.uniqueID# #data[itm].values.SubsiteURL#'

                            <cfelse>

                            and subSiteUrl not like '#data[itm].values.uniqueID# #data[itm].values.SubsiteURL#' 

                            </cfif>

                          </cfif>

                          </cfloop>

                        </cfquery>

                        • 10. Re: How to use variable in cfQuery where?
                          tribule Level 2

                          Please paste the exact SQL that is generated so we can see

                          • 11. Re: How to use variable in cfQuery where?
                            2Charlie Level 1

                            One more thing, how do I print out the SQL without the CFQUERY? Or how do I log the sql to the coldfusion logs folder? I have tried the following but it didn't log.

                             

                            <cflog text="SQL: #tmpResult.SQL# Number of Records: #tmpResult.RecordCount#" type="Information" file="queryOfQueries">

                            • 12. Re: How to use variable in cfQuery where?
                              2Charlie Level 1

                              I have tried login like this:

                               

                              <cflog text="SQL: #tmpResult.SQL# Number of Records: #tmpResult.RecordCount#" type="Information" file="queryOfQueries">

                               

                              However, it's not logging this. I'm not sure if this is due to because it's a .cfc file and not a .cfm file.

                              • 13. Re: How to use variable in cfQuery where?
                                tribule Level 2

                                Charlie, if you took out the cfquery tags then all you would see is SQL correct? You'd need to replace them with cfoutput so that you can see the value of the SQL you are generating. You don't need to log anything, just see the SQL commands you are creating. How can you see what SQL you are even creating?!

                                • 14. Re: How to use variable in cfQuery where?
                                  2Charlie Level 1

                                  I can't use cfoutput because this is a CommonSpoit's ADF app that I used to build a website's sitemap.xml file. I'm calling the app through an ajaxProxy.cfm file. I need a way to log this and then look at the file in coldfusion logs folder.

                                  • 15. Re: How to use variable in cfQuery where?
                                    2Charlie Level 1

                                    Okay, so I tried this:

                                     

                                    <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                                      select *

                                      from qryContent

                                      where

                                      <cfloop from="1" to="#arrayLen(data)#" index="itm">

                                        <cfif #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# gte #todayDate# || #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# is "">

                                        <cfif itm eq 1>

                                        subSiteUrl not like <cfparam value ="#data[itm].values.uniqueID# #data[itm].values.SubsiteURL#" type="string">

                                        <cfelse>

                                        and subSiteUrl not like <cfparam value ="#data[itm].values.uniqueID# #data[itm].values.SubsiteURL#" type="string">

                                        </cfif>

                                      </cfif>

                                      </cfloop>

                                    </cfquery>

                                     

                                    It error out with "Internal serve error." I still can't figure out a way to output the sql statement because I'm running something like this to generate the file.

                                     

                                    http://mysite.com/_cs_apps/ajaxProxy.cfm?appName=sitemap&bean=service&method=buildSitemaps &requestTimeout=5005l&returnFo…

                                    • 16. Re: How to use variable in cfQuery where?
                                      tribule Level 2

                                      Wow, sounds weird. You run ColdFusion templates yet can't see the output. Doesn't sound right to me Good luck with it.

                                      • 17. Re: How to use variable in cfQuery where?
                                        BKBK Adobe Community Professional & MVP

                                        2Charlie wrote:

                                         

                                        Therefore, it will be something like this:

                                         

                                        <cfset strURL = "'">

                                        <cfloop from="1" to="#arrayLen(data)#" index="itm">

                                          <!---// renders data from the uniqueID and Title fields from the element --->

                                          <cfif #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# gte #todayDate# || #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# is "">

                                          <cfif itm eq 1>

                                          <cfscript>

                                            strURL = "subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

                                            </cfscript>

                                          <cfelse>

                                          <cfscript>

                                            strURL &= " and subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

                                            </cfscript>

                                          </cfif>

                                          </cfif>

                                        </cfloop>

                                         

                                        <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                                          select *

                                          from qryContent

                                          where '#strURL#'

                                        </cfquery>

                                         

                                         

                                        Simplifying the logic and the code,

                                         

                                        <cfset todayDate = DateFormat(Now(), "mm/dd/yyyy")>

                                        <cfset strURL = "">

                                        <!--- get data from the "My Element" custom element--->

                                        <cfset data = application.ADF.ceData.getCEData(customElementName="Subsite Exclusion")>

                                         

                                        <cfloop from="1" to="#arrayLen(data)#" index="itm">

                                            <!--- renders data from the uniqueID and Title fields from the element --->

                                            <cfif DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy") gte todayDate or DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy") is "">

                                            <cfset strURL = strURL & " and subSiteUrl not like '" & data[itm].values.SubsiteURL & "'">

                                            </cfif>

                                        </cfloop>

                                         

                                        <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

                                            select *

                                            from qryContent

                                            where 0=0

                                            #preserveSingleQuotes(strURL)#

                                        </cfquery>