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

How to use variable in cfQuery where?

Enthusiast ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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.

TOPICS
Database access

Views

3.2K

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 ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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>

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
Enthusiast ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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>

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
Enthusiast ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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.

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
Enthusiast ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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>

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
Enthusiast ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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.

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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.

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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>

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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

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
Community Expert ,
Nov 05, 2015 Nov 05, 2015

Copy link to clipboard

Copied

LATEST

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>

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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.

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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?!

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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.

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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

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
Enthusiast ,
Nov 03, 2015 Nov 03, 2015

Copy link to clipboard

Copied

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

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
Enthusiast ,
Nov 02, 2015 Nov 02, 2015

Copy link to clipboard

Copied

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.

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