Error migrating cffeed from CF9 to CF 2018

Report · Jun 15, 2020

I'm finally updating a site from CF9 to CF2018 and am having problems with my cffeed. Can anyone see any obvious errors in my code? This has worked for years in CF9, but in CF2018 I'm getting the error:

"There is a problem in the column mappings specified in the columnMap structure.
The cffeed query does not contain any column by the name of title."

<!--- Map the orders column names to the feed query column names. ---> 
<cfset columnMapStruct = StructNew()> 
<!--- 2020.06.15
<cfset columnMapStruct.publisheddate = "PUBLISHDATE">  
--->
<cfset columnMapStruct.pubdate = "PUBLISHDATE">  
<cfset columnMapStruct.content = "SUMMARY">  
<cfset columnMapStruct.title = "TITLE">  
<cfset columnMapStruct.rsslink = "URL"> 
<cfset columnMapStruct.id = "GUID">


<!--- Set the feed metadata. ---> 
<cfset meta.title = "My Domain News"> 
<cfset meta.link = "http://mydomain.com/"> 
<cfset meta.description = "News from My Domain">  
<cfset meta.version = "rss_2.0"> 
  
<!--- Create the feed. ---> 
<cffeed action="create"  
    query="#qRss#"  
    properties="#meta#" 
    columnMap="#columnMapStruct#"  
    outputFile = "#output_directory_fullpath#myfeed.xml"
    overwrite = "yes"
    xmlvar="rssXML">

My query selects:

id
guid
title
fknewstype
fknewscategory
summary
publishdate
url
imagetopstory
sortorder

Thanks,
John.

Report · Jun 16, 2020

Let's start with you doing a cfdump of the input vars: qRss, meta, and columnMapStruct. Things may not be what you expect, for some OTHER reason.

/Charlie (troubleshooter, carehart.org)

Report · Jun 16, 2020

Hi Charlie, thank you for the reply. I've included the full code, dump, and the beginning of the error below. I appreciate your help.

Thanks,

John

<cfquery name="qRss" datasource="#application.DataSourceDS#" cachedWithin="#createTimeSpan(0,0,00,0)#">
SELECT
  news.id,
  news.title,
  news.summary,
  news.publishdate,
  if(char_length(trim(ifnull(news.url,''))) <> 0,news.url, concat('http://MYDOMAIN.com/story.cfm?id=',news.id)) as url


  FROM
    news
    left Join newscategories as categories ON news.fknewscategory = categories.Id
  WHERE
    news.deleted =  '0'
  AND 
  publishdate BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
  AND fknewstype = 1  
  AND (FIND_IN_SET("1", onsites))  ## ...if the siteid is in onsites
  ORDER BY publishdate DESC
                                                     
LIMIT 3
</cfquery>


<!--- Map the orders column names to the feed query column names. ---> 
<cfset columnMapStruct = StructNew()> 
<!--- 2020.06.15
<cfset columnMapStruct.publisheddate = "PUBLISHDATE">  
--->
<cfset columnMapStruct.pubdate = "PUBLISHDATE">  
<cfset columnMapStruct.content = "SUMMARY">  
<cfset columnMapStruct.title = "TITLE">  
<cfset columnMapStruct.rsslink = "URL"> 
<!--- 2020.06.15
<cfset columnMapStruct.id = "GUID">
--->
 
<!--- Set the feed metadata. ---> 
<cfset meta.title = "MY DOMAIN News"> 
<cfset meta.link = "http://MYDOMAIN.com/"> 
<cfset meta.description = "News from MY DOMAIN">  
<cfset meta.version = "rss_2.0"> 


  
<cfdump var="#qRss#" />
<cfdump var="#meta#" />
<cfdump var="#columnMapStruct#" />
  
  
 
<!--- Create the feed. ---> 
<cffeed action="create"  
    query="#qRss#"  
    properties="#meta#" 
    columnMap="#columnMapStruct#"  
    outputFile = "c:\myfeed.xml"
    overwrite = "yes"
    xmlvar="rssXML"> 


<!---
<cfdump var="#XMLParse(rssXML)#">
<cfoutput>#rssXML#</cfoutput>
--->

Report · Jun 17, 2020

That is indeed odd. From all appeareances, things should work as is.

So first, you say you are running on CF2018, but you don't confirm what update level. You can see that in the CF Admin, or from outputting #server.coldfusion.productversion#. If it does not report a string starting with 2018,0,09, then you are not on the latest update. Let us know what you are on. There could be a bug about cffeed that was fixed in some intermediate cf update. I could easily run a test against whatever version you say you are on.

Indeed, at the bottom of my note here I've offered code that does work (it's from the docs). And it shows how it looks mich like yours, which again should work. It might even be worth running on your server as a sanity check, just to make sure it works for you (it does leverage the built-in cfartgallery db, which is implemented by default in all CF installations. But if someone has removed that from your CF Admin, obviously it won't work as-in.)

And if I change one of the values in the columnmapstruct to not match the name of a column in the query, I get the error you do, naming whatever columnname I change it to. So clearly, your execution of your code somehow thinks that there IS no title column in the query, though clearly your dump shows that there is.

Here's an out of the box thing to try: can you please put "variables." in fronf of both the cfquery name (so name="variables.qrss", and then in the cffeed reference to it, so query="#variables.qrss#")? No, it shouldn't be needed, but let's just FORCE CF to have a specific scope in which to create and find that query. And yes, I realize that the current cfdump you have names the same query as the cffeed query attribute. Let's not worry about that and just try this. For now, I can't think of anything else.

Let's here what you find on the above.

And here is the working sample code.

<!--- Get the feed data as a query from the orders table. --->
<cfquery name="getOrders" datasource="cfartgallery">
SELECT ORDERDATE,ADDRESS,CUSTOMERFIRSTNAME,ORDERID FROM orders
</cfquery>

<!--- Map the orders column names to the feed query column names. --->
<cfset columnMapStruct = StructNew()>
<cfset columnMapStruct.publisheddate = "ORDERDATE">
<cfset columnMapStruct.content = "ADDRESS">
<cfset columnMapStruct.title = "CUSTOMERFIRSTNAME">
<cfset columnMapStruct.rsslink = "ORDERID">

<!--- Set the feed metadata. --->
<cfset meta.title = "Art Orders">
<cfset meta.link = "http://feedlink">
<cfset meta.description = "Orders at the art gallery">
<cfset meta.version = "rss_2.0">

<!--- Create the feed. --->
<cffeed action="create"
query="#getOrders#"
properties="#meta#"
columnMap="#columnMapStruct#"
xmlvar="rssXML">

<cfdump var="#meta#">
<cfdump var="#columnmapstruct#">
<cfdump var="#getOrders#">
<cfdump var="#XMLParse(rssXML)#">

/Charlie (troubleshooter, carehart.org)

Report · Jun 17, 2020

Hi Charlie -

Unfortunately, the cfartgallery demo isn't installed on my server. As for the version, I'm running server.coldfusion.productversion: [2018,0,09,318650].

I also tried scoping the query name with "variables." and got the same results.

I accidentally cleared my columnMapStruct (added a second CFSET columnMapStruct = StructNew() ) and did get XML containing the correct title and GUID (which I had added back in to the columnMapStruct).

I'm not sure if the resulting XML dump offers any clues or not.

Thanks again for looking!

John.

<!--- Map the orders column names to the feed query column names. ---> 
<cfset columnMapStruct = #StructNew()# />
<cfset columnMapStruct.title = "TITLE" />
<cfset columnMapStruct.pubdate = "PUBLISHDATE" />
<cfset columnMapStruct.content = "SUMMARY" />
<cfset columnMapStruct.rsslink = "URL" />
<cfset columnMapStruct.id = "GUID" />

<cfset columnMapStruct = #StructNew()# />

  
<!--- Set the feed metadata. ---> 
<cfset meta.title = "MY DOMAIN News" /> 
<cfset meta.link = "http://MYDOMAIN.com/" /> 
<cfset meta.description = "News from MY DOMAIN" />  
<cfset meta.version = "rss_2.0" /> 

<cfdump var="#qrss#" />
<cfdump var="#meta#" />
<cfdump var="#columnMapStruct#" />

<p>
<cfoutput>server.coldfusion.productversion: [#server.coldfusion.productversion#]</cfoutput>
</p>
  
<!--- Create the feed. ---> 
<cffeed action =  "create"  
    query       = "#qrss#"  
    properties  = "#meta#" 
    columnMap   = "#columnMapStruct#"  
		outputFile  = "#output_directory_fullpath#myfeed.xml"
		overwrite   = "yes"
    xmlvar      = "rssXML"> 

<cfdump var="#XMLParse(rssXML)#">
<cfoutput>#rssXML#</cfoutput>

Report · Jun 17, 2020

It looks like I can just change my query and SELECT to the appriopriate field names (title, content, pubdate, rsslink, and id). Then, omit the references to columnMapStruct and everything will work.

Though I'm curious why CFFEED won't work, it might be my best option at this point.

John

Report · Jun 18, 2020

Yep, if you mean creating aliases for the column names in the SQL (such as by using the AS keyword), that would indeed alleviate need of the columnmap. But as you say, it should work--and it does for me, and should for anyone who would run that sample code I shared.

So the question is indeed why YOUR code doesn't work on your server. If you ever had time to try it, I would wonder what would happen if you took my code and replaced it with your sql and column names, but I realize you may just move on once you fix the aliases. 🙂

/Charlie (troubleshooter, carehart.org)

Report · Jun 18, 2020

So, I copied & pasted your code and changed the query to pull from my database (since I don't have the cfartgallery demo installed). I get the same results as with my code. Note that I changed publisheddate to pubdate, otherwise the error is The cffeed query does not contain any column by the name of publisheddate instead of The cffeed query does not contain any column by the name of title.

It's definitely strange. I thought maybe I was missing something in the documenation or a change between CF9 - CF2018; but, my code is pretty basic (still using CFML) and I can't figure out the problem here. My only guess is that maybe there's something different in the RSS 2.0 spec that CFFEED is aware of but the columnMapStruct isn't taking into account. But, I've looked at what I can find on the RSS spec and nothing is jumping out at me. Or, maybe there is something in the Coldfusion configuration that might prevent this from working correctly?

I *really* appreciate the time you took to look and reply; but, I think it's time to bury this and see if I can just move on with SQL aliases. Maybe if time allows sometime in the future, I'll try to setup a new Coldfusion installation and see if I can replicate it there.

Thanks,

John

<cfquery name="getOrders" datasource="#application.DataSourceDS#" cachedWithin="#createTimeSpan(0,0,00,0)#">
SELECT
  concat("news.title ", news.id) as CUSTOMERFIRSTNAME,
  concat("news.summary ", news.id) as ADDRESS,
  news.publishdate as ORDERDATE,
  concat('http://MYDOMAIN.com/news/story.cfm?id=',news.id) as ORDERID
FROM
  news
WHERE
  news.deleted =  '0'
AND publishdate BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
AND fknewstype = 1  
AND (FIND_IN_SET("1", onsites))  ## ...if the siteid is in onsites
ORDER BY publishdate DESC

LIMIT 3
</cfquery>



  <!--- Get the feed data as a query from the orders table. --->
<!---
<cfquery name="getOrders" datasource="cfartgallery">
SELECT ORDERDATE,ADDRESS,CUSTOMERFIRSTNAME,ORDERID FROM orders
</cfquery>
--->
  
<!--- Map the orders column names to the feed query column names. --->
<cfset columnMapStruct = StructNew()>
<cfset columnMapStruct.pubdate = "ORDERDATE"> <!--- change to pubdate from publisheddate --->
<cfset columnMapStruct.content = "ADDRESS">
<cfset columnMapStruct.title = "CUSTOMERFIRSTNAME">
<cfset columnMapStruct.rsslink = "ORDERID">

<!--- Set the feed metadata. --->
<cfset meta.title = "Art Orders">
<cfset meta.link = "http://feedlink">
<cfset meta.description = "Orders at the art gallery">
<cfset meta.version = "rss_2.0">

<cfdump var="#meta#">
<cfdump var="#columnmapstruct#">
<cfdump var="#getOrders#">

  
  
<!--- Create the feed. --->
<cffeed action="create"
query="#getOrders#"
properties="#meta#"
columnMap="#columnMapStruct#"
xmlvar="rssXML">

<cfdump var="#meta#">
<cfdump var="#columnmapstruct#">
<cfdump var="#getOrders#">
<cfdump var="#XMLParse(rssXML)#">

Report · Jun 20, 2020

I'll understand your being about done, but I'll note that example uses publisheddate because the CF docs do show using that (and another example uses pubdate). And fwiw, if I LEAVE in the use of pubdate as you had it instead, I get an error saying that THAT is not valid. 🙂

So can you confirm that if you change it to use publisheddate (as the key name in the stuct, on the left side of the =), and REMOVE your line creating a key called pubdate, does it work then? Or does the error change?

If it works, that would be great. The question then would be what changed between your use in cf9 and now in cf2018. If it does not, again I'll hope the alias approach works for you instead.

/Charlie (troubleshooter, carehart.org)

Report · Jun 21, 2020

Hi Charlie -

No, "publisheddate" doesn't work for me. All of the documentation and examples I've seen indicate that example code should work; but, it just doesn't for some reason. And, neither does my old code. I don't know if this is a CF issue or an issue with my installation. I haven't found any other weird errors in my site that couldn't be explained, just this one.

Is there anything in CF Admin that could cause an issue like this? I went through all of the screens but nothing jumped out at me.

John.

<!--- Get the feed data as a query from the orders table. --->
<!---
<cfquery name="getOrders" datasource="cfartgallery">
SELECT ORDERDATE,ADDRESS,CUSTOMERFIRSTNAME,ORDERID FROM orders
</cfquery>
--->

<cfquery name="getOrders" datasource="#application.DataSourceDS#" cachedWithin="#createTimeSpan(0,0,00,0)#">
SELECT
  concat("mytitle ", news.id) as CUSTOMERFIRSTNAME,
  concat("mysummary ", news.id) as ADDRESS,
  news.publishdate as ORDERDATE,
  concat('http://MYDOMAIN.com/news/story.cfm?id=',news.id) as ORDERID
FROM
  news
WHERE news.deleted =  '0'
AND publishdate BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
AND fknewstype = 1  
AND (FIND_IN_SET("1", onsites))  ## ...if the siteid is in onsites
ORDER BY publishdate DESC

LIMIT 3
</cfquery>

  
<!--- Map the orders column names to the feed query column names. --->
<cfset columnMapStruct = StructNew()>
<cfset columnMapStruct.publisheddate = "ORDERDATE">
<cfset columnMapStruct.content = "ADDRESS">
<cfset columnMapStruct.title = "CUSTOMERFIRSTNAME">
<cfset columnMapStruct.rsslink = "ORDERID">

<!--- Set the feed metadata. --->
<cfset meta.title = "Art Orders">
<cfset meta.link = "http://feedlink">
<cfset meta.description = "Orders at the art gallery">
<cfset meta.version = "rss_2.0">

<cfdump var="#meta#">
<cfdump var="#columnmapstruct#">
<cfdump var="#getOrders#">

  
<!--- Create the feed. --->
<cffeed action="create"
query="#getOrders#"
properties="#meta#"
columnMap="#columnMapStruct#"
xmlvar="rssXML">

<cfdump var="#meta#">
<cfdump var="#columnmapstruct#">
<cfdump var="#getOrders#">
<cfdump var="#XMLParse(rssXML)#">

Report · Jun 22, 2020

Hi John,

First things first: delete the attribute

cachedWithin="#createTimeSpan(0,0,00,0)#"

and see if that helps.

If it does, then you might have inadvertently been dealing with a null somewhere. The way ColdFusion handles nulls has changed since CF9.

Report · Jun 22, 2020

That will be very interesting if it somehow solves it. Since the cfdump of the query earlier in the thread looked fine, it never crossed my mind to raise a concern about the cachedwithin (hadn't even noticed it), let alone about nulls. Let's see what John says.

If that doesn't help, John, you asked if there's anything in the CF Admin that could affect the cffed processing, and I can't think of any, no.

As for your experiencing an error that no one else does, there IS the possibility that you (or whoever updated your CF2018 to the update 9 you reported) had experienced an error in the CF update that perhaps they did not notice. I talk about that (how to confirm, and how to resolve it) in a post:
https://coldfusion.adobe.com/2019/03/problems-applying-cf-update-check-first/

Check that out, and let us know what you find.

/Charlie (troubleshooter, carehart.org)

Report · Jun 24, 2020

Thanks Charlie, I'll check out your article.

As I mentioned in my reply to BKBK, removing the cachedWithin attribute didn't change anything.

This is a newly created server/VPS at Hostek, I assume/hope they know what they're doing. The old server was on its last legs at a different host. I also have CF9 running locally, but not 2018 (I need to get a 64-bit Windows VM up and running first). So, I'm running a Prod and Dev site on the same install at Hostek. Not ideal; but, this was an "unscheduled escalated timeframe" 🙂 (The date had to be moved up a month or two due to failing hardware. Admittedly loooonnnggg overdue).

The environment is Windows (Server 2016) / CF2018 / MySQL 5.7.10. The old environment was Windows Server 2008 / CF9 32-bit / MySQL 5.1.57. And, I've confirmed that it works with CF9 hitting the new MySQL 5.7.10 database.

So far, this is the only real issue I've had. I've been able to resolve everything else I've come across.

I think at this point it's time to throw in the towel and just go with the field aliases in the query.

Thanks,

John

Report · Jun 25, 2020

Try the approach where you ensure the structkeys match the query's column names:

SELECT
news.id as id,
news.title as title,
news.summary as content,
news.publishdate as publisheddate,
if(char_length(trim(ifnull(news.url,''))) <> 0,news.url, concat('http://MYDOMAIN.com/story.cfm?id=',news.id)) as rsslink

Report · Jun 24, 2020

Hi BKBK - Thanks for the reply. I've confirmed that the symptoms are the same after removing the cachedWithin attribute.

John.

Report · Feb 09, 2021

If it's any consolation, I am having the same exact 'The cffeed query does not contain any column by the name of publisheddate' error when migrating the cfbloggers codebase over to a hosted ColdFusion 2016 server.

Both of the following approaches fail:

Query:
<cfquery name="q" datasource="#variables.dsn#">
select SQL_CALC_FOUND_ROWS e.url, e.title, e.posted as posted, e.created, e.content, e.id, b.name as blog, e.categories, b.description as blogdescription, b.url as blogurl
from entries e, blogs b
where e.blogidfk = b.id

</cfquery>

<cfset columnMapStruct = StructNew()>
<cfset columnMapStruct.publisheddate = "POSTED">
<cfset columnMapStruct.rsslink = "URL">
<cfset columnMapStruct.comments = "BLOGURL">
<cfset columnMapStruct.categorylabel = "CATEGORIES">
<cffeed action="create" properties="#props#" columnMap="#columnMapStruct#" query="#items#" xmlVar="result">

OR:

Both approaches return the following error: "The cffeed query does not contain any column by the name of publisheddate'"

I am just going to alias the column names in the query, there's something funny going on here.

Report · Feb 09, 2021

John,

What database are you using? I am using mariaDb (mySql). I wonder if the database has anything to do with this problem that we both seem to be having?

Gregory

Report · Feb 10, 2021

That's a good thought, Gregory. When I shared code that worked, it was indeed using the sample artgallery that comes with cf. That runs on Derby - an alternative opensource db that never really took off).

One other thought/question is what driver type you guys are using for the cf admin dsn you created. Is it mysql or "other"? I suspect the latter, since that's needed to run the community edition of mysql, which is what most folks seem to run.

Then the next question is what mysql driver version are you using? It's generally on the name of the mysql jar that you or someone would have put in the cfusion/lib folder.

These all may be clues, or may be red herrings. Interesting challenge, nonetheless.

/Charlie (troubleshooter, carehart.org)

Adobe Community

Error migrating cffeed from CF9 to CF 2018