Copy link to clipboard
Copied
I have the following function and in the WHERE clause, the s.subsiteURL no like '/training%' is working or something. It's still selected a sub site with URL starting with /training. Is there a way to output the result of this function to the browser? I have tried the <cfdump var="#qryCSPages#"> but it's not dumping anything on the screen.
<cffunction name="getCommonSpotPages" access="public" returnType="query">
<cfset var qryCSPages = queryNew("")>
<cftry>
<!--- // get CommonSpot site URLs --->
<cfquery name="qryCSPages" datasource="#application.sitemap.sDSN#">
select p.ID, s.subsiteURL, p.filename, p.DateContentLastModified, u.publicFileName, p.uploaded as bIsUploaded, s.uploadURL,
(select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = #application.sitemap.nPriorityFieldID# and dfv.versionState = 2 ) as nPriority,
(select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = #application.sitemap.sFrequencyFieldID# and dfv.versionState = 2 ) as sFrequency
from SitePages p
join SubSites s
on p.SubSiteID = s.id
left join UploadedDocs u
on p.id = u.pageID
where (p.expDate is null
OR p.expDate > getdate())
and p.pageType = 0 <!--- uploaded documents and content pages only --->
and p.approvalStatus <> 1 <!--- // exclude inactive pages --->
and s.siteState <> 0 <!--- // exclude inactive subsites --->
and s.subsiteURL not like '/training%' <!--- specify all pages inside of the subsite to be exlcuded from the sitemap --->
and s.ID not in (<cfqueryparam value="#application.sitemap.lstExcludeSubsites#" cfsqltype="CF_SQL_INTEGER" list="yes" /> ) <!--- // exclude particular subsites --->
order by p.ID desc
</cfquery>
<cfcatch><cfdump var="#cfcatch#"></cfcatch>
</cftry>
<cfdump var="#qryCSPages#">
<cfreturn qryCsPages>
</cffunction>
Copy link to clipboard
Copied
As a troubleshooting step, run the query in a query utility that comes with your database engine, for example SQL Server Management Studio for SQL Server, or Workbench for MySQL.
Naturally you will need to replace any ColdFusion markup with valid SQL.
Let us know the result.
Cheers
Eddie
Copy link to clipboard
Copied
Thanks! Is there a way to see the valid SQL markup after ColdFusion as converted?
Copy link to clipboard
Copied
It is possible, but that's going about the problem the wrong way.
You should create a working query first and then use that query in ColdFusion.
Cheers
Eddie
Copy link to clipboard
Copied
Actually this was part of an ADF apps (CommonSpot CMS) created by a third party and I want to add another functionality to the function. This function has parameters that I am not sure where it came from; thus, I was wondering if I can dump the query on the browser to analyze the query to see if it make sense.
Copy link to clipboard
Copied
2Charlie wrote:
This function has parameters that I am not sure where it came from;
That's all well and good, but it's best to troubleshoot one thing at a time. Let's first make sure the query is sane before troubleshooting parameters.
Remove all the ColdFusion comments from the query text, replace the two application variables with integers and replace the cfqueryParam tag with a small list of integers. That's all you need to do to get a syntactically correct query. Does that query succeed in your SQL query tool without error? Note that a zero record count is still a success.
Cheers
Eddie
Copy link to clipboard
Copied
Here's my attempted query.
select p.ID, s.subsiteURL, p.filename, p.DateContentLastModified, u.publicFileName, p.uploaded as bIsUploaded, s.uploadURL,
(select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = '4,6,41,262,39,241,165,26,28,166,37' and dfv.versionState = 2 ) as nPriority,
(select fieldValue from data_fieldValue dfv where p.ID = dfv.pageID and dfv.fieldID = '4,6,41,262,39,241,165,26,28,166,37' and dfv.versionState = 2 ) as sFrequency
from SitePages p
join SubSites s
on p.SubSiteID = s.id
left join UploadedDocs u
on p.id = u.pageID
where (p.expDate is null
OR p.expDate > getdate())
and p.pageType = 0
and p.approvalStatus <> 1
and s.siteState <> 0
and s.subsiteURL NOT LIKE '/training%'
and s.ID not in ('4','6','41','262','39','241','165','26','28','166','37')
order by p.ID desc
And I kept getting this error when I run in MS SQL Server Management Studio.
Msg 245, Level 16, State 1, Line 18
Conversion failed when converting the varchar value '4,6,41,262,39,241,165,26,28,166,37' to data type int.
Copy link to clipboard
Copied
I'm guessing the dfv.fieldID is a single value, so put one number without quotes.
In the number list, remove the quotes around the numbers.
Cheers
Eddie
Copy link to clipboard
Copied
Or simply remove the single quotes in the list of numbers:
and s.ID not in (4,6,41,262,39,241,165,26,28,166,37)
(sorry, didn't read - I only saw the "one number" portion)
Copy link to clipboard
Copied
I got the error below when I triedd: s.ID not in (4,6,41,262,39,241,165,26,28,166,37)
Msg 512, Level 16, State 1, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Copy link to clipboard
Copied
Well you're proving Eddies point, there are multiple issues with your SQL statement and that should have been where to start. You're fighting with SQL coding issues here, not CF coding issues. The where clauses in the subqueries need to be adjusted to reference a single value fieldId value, most likely referencing the current row value that you are on -- but I'm not sure this is what your expecting in the results.
Copy link to clipboard
Copied
I used a single value and the query executes fine. It gave me the expected result. However, when it builds the sitemap.xml, it's not. I'll diagnosed more on the CommonSpot ADF app. Thank you all for your help.
Copy link to clipboard
Copied
There in an optional result parameter in the cfquery tag. This will contain the SQL statement that was executed.
Copy link to clipboard
Copied
Steve Sommers wrote:
There in an optional result parameter in the cfquery tag. This will contain the SQL statement that was executed.
That's only helpful if the query succeeds. It is also possible to see the text of the query that threw an exception, but I cannot stress enough that if one is not starting with a valid query the problems are just compounded.
Cheers
Eddie