Thanks, and thanks also for the blog link. The first time I "see" the date in this project is when I poll it from what should be, in final implementation, someone else's valid RSS, so when I get it it should already be in the left-field manner that RSS demands. Although in the test pages I posted I actually am also the creator of the original XML, I am working on the theory that I will be parsing valid RSS feeds from varying sources. So, I need my fix to work at the level that I parse the XML into queries that are later concatenated in a QofQ union.
Where things are hanging up is that at some step (at different points, depending on whether I try to createodbcdate before the QofQ, etc.) the database reads all the pubDates as dates...EXCEPT those that have Tue or Thur starting up in that darned "left field!" I may even give a run at first parsing the XML and operating on it before it is parsed into the Query (even if I have to re-pack it into XML before the Query). In the larger context, the reason I am going through all this is to develop a way for performing arts groups to share and combine listings, which could be used on city, state or region wide sites that would combine the listings and have them viewable by date. My first cut at it is to get a few initial sites outputting listings in RSS, since that is a fairly common format and has readily available validators. That's why I am trying to make this work reasonably well in RSS format, for all its left-field awkwardness. This is also why the listings in the sample pages have the dates also as part of the <description>field, so that a regular RSS aggregator will see the dates. The idea was to get things moving in RSS, and in the meanwhile begin developing another format that I have for the moment dubbed Performing Arts Event Syndication (PAES), which would have a collection of children to the <item> (or we might just call it <event)) that are useful to that application, such as <eventtitle> <eventdate> <eventtime> <city> <state> <genre> as well as links to sponsoring organization's web site and ticket link if one exists. YOU CAN BET that the date and time fields will not be so "left-handed!!!!' Eventually, it's the PAES that I will want to combine, so maybe the RSS can be left to lie. It would be easy for anyone setting up either feed to simply create both. In any event, thanks for your thoughts, and I would welcome any other comments, insights etc. you might wish to share!! Best, James Edmunds [EMAIL PROTECTED] wrote: >When you got the error converting to ODBC Date were you removing the day-of-week >(Tues / Thurs) and the time-zone prior to applying createodbcdatetime()? ... It's >unfortunate that the date format required by RSS is so horribly "left-field" -- but >you don't need to store that date format in your db (unless you just _want_ to store >it as a string in a separate column for convenience). Just store the time-zone in a >separate CHAR(3) column and then remove everything but the date before you apply >createodbcdatetime() to insert it into a datetime column in your database. When you >pull the data back out afterward, you can then display the proper date format for the >RSS field using dateformat() and timeformat(). I was struggling a bit with this >myself just the other day and posted some relevant(?) info on my blog at: > >http://www.turnkey.to/ontap/docs/blog.cfm?netaction=view&blogdate=2003-08-20 > >Anyway, I hope that's at all helfpul. :) > >Isaac > >------ Original Message ------ >From: James Edmunds <[EMAIL PROTECTED]> >To: CF-Talk <[EMAIL PROTECTED]> >Sent: Aug 21, 2003 09:31 AM >Subject: Re: What's wrong with Tuesday and Thursdays??? > > > >>Isaac, >> >>Thanks for those good suggestions... however, I've fished in that end of >>the pond quite a bit and can't seem to make it work. >> >>I've tried putting createodbcdatetime in the QuerySetCell parameter but >>that doesn't work. I've tried a cfloop after the creation of MyQuery >>with CFSCRIPT to convert the pubDate variable with createodbcdate >>operating on the puDate variable, and get a "could not be converted to a >>date" expression exception. However, this only occurs when the dates >>are Tue or Thur! >> >>As you can see from the original post with "working" version, the one >>that works is different only in that the source XML has no Tue or Thur >>dates. BTW, this is the date format for RSS, and the RSS won't validate >>if not in this format. I have done this with non-validatalbe XML that >>stores the date in a different format, and it works then, but I am >>trying to build a page that will concatenate and display multiple >>validated-RSS feeds. >> >>For some reason, the database can convert the strings to dates unless >>they are Tue or Thur. CFQUERYPARAM doesn't help because the problem is >>not with parameters passed into the query, but the data itself. I think >>that what happens in the Query of queries I have further down the page >>is that the types mismatch because some of the dates have been converted >>into odbcdates and others (with Tue or Thur) were not. >> >>In any case, thanks for your help, and I shall continue to plumb the >>waters in hopes of finding a solution. >> >>Best regards, >> >>James Edmunds >> >> >>[EMAIL PROTECTED] wrote: >> >> >> >>>When you insert your dates into the query, convert them to odbcdate time values >>>first by stripping the day of week and the time zone like this (if you need the >>>time zone, put it in a separate column): >>> >>>createodbcdatetime(rereplace(thedate,"^[^,]*,[[:space:]]*(.*)[[:space:]]*[[:alpha:]]{2,2}T[[:space:]]*$","\1")) >>> >>>If that doesn't work, specify individual column names and use a filter on the date >>>with cfqueryparam like: >>> >>>where pubdate < <cfqueryparam value="9900-01-01" cfsqltype="cf_sql_datetime"> >>> >>>hth >>> >>>Isaac >>> >>>------ Original Message ------ >>>From: James Edmunds <[EMAIL PROTECTED]> >>>To: CF-Talk <[EMAIL PROTECTED]> >>>Sent: Aug 20, 2003 02:24 PM >>>Subject: Re: What's wrong with Tuesday and Thursdays??? >>> >>> >>> >>> >>> >>>>I have two templates of code that concatenate and display two XML files >>>>in RSS 2.0 format, identical except that one of them calls an RSS XML >>>>file that has dates whose day of the week begin with a "T", and chokes! >>>>It stops with a database exception, with this error message: >>>>Can't convert the string Tue, 23 Sep 2003 00:00:00 CDT to java type DATE >>>>(In earlier tests, I have had this same stop at Thu dates) >>>> >>>>You can navigate to each of these with these URLs, and you'll note the >>>>CFDUMPs and Error reporting: >>>> >>>>The one that works: >>>> >>>>http://www.jamesedmunds.com/rssgetYES.cfm >>>> >>>>The one that doesn't: >>>> >>>>http://www.jamesedmunds.com/rssgetNO.cfm >>>> >>>>Below my signature, the code for each. Thanks in advance for anyone with >>>>insight into this issue, which seems to have to do with the way the >>>>dates are read in the database engine. I am getting these errors in CFMX >>>>(what we would now call 6.0) through shared hosting on CrystalTech, as >>>>well as locally testing on CFMX 6.1. >>>> >>>>Best to all, >>>> >>>>James Edmunds >>>>--------------------------------------------- >>>>CODE FOR RSSGETYES.CFM: >>>> >>>><CFTRY> >>>><cfhttp url="http://lapresenters.org/rss/lajazdanz.xml"; method="get"> >>>><cfset myQuery=QueryNew('Title, Link, Description,pubDate')> >>>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>>><cfset Items=myDoc.rss.channel.XMLChildren> >>>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>>> >>>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>>> >>>><cfset QueryAddRow(myQuery,1)> >>>><cfset >>>>QuerySetCell(myQuery,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery,'pubDate',myDoc.rss.channel.item[idx].pubDate.XMLText)> >>>> >>>></cfloop> >>>> >>>><CFOUTPUT> >>>><CFDUMP var="#myQuery#"> >>>></CFOUTPUT> >>>> >>>><cfhttp url="http://www.lapresenters.org/rss/PrincessTheaterInc.xml"; >>>>method="get"> >>>><cfset myQuery2=QueryNew('Title, Link, Description,pubDate')> >>>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>>><cfset Items=myDoc.rss.channel.XMLChildren> >>>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>>><cfset QueryAddRow(myQuery2,1)> >>>><cfset >>>>QuerySetCell(myQuery2,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery2,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery2,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>>> >>>><cfset >>>>QuerySetCell(myQuery2,'pubDate',myDoc.rss.channel.item[idx].pubdate.XMLText)> >>>> >>>></cfloop> >>>> >>>><CFOUTPUT> >>>><CFDUMP var="#myQuery2#"> >>>></CFOUTPUT> >>>> >>>><cfquery dbtype="query" name="GetBoth"> >>>>SELECT * FROM MyQuery >>>>UNION >>>>SELECT * FROM MyQuery2 >>>>ORDER BY pubDate >>>></cfquery> >>>><table width="550" border="0" cellspacing="0" cellpadding="2"> >>>><TR> >>>> <TD colspan=2 valign=top><font size="2" face="Georgia, Times New >>>>Roman, Times, serif">XML >>>> Parsed and combined listings >>>> from two different web site sources<br> >>>> </font></TD> >>>></TR> >>>><cfoutput query="GetBoth"> >>>><tr> >>>> <td valign=top width=100 align=right> >>>> >>>></td> >>>><td valign=top width=450 align=left> >>>> <font size="1" face="Arial, Helvetica, sans-serif"> <a >>>>href="#link#"><strong>#title#</strong></a> >>>> #description#</font> >>>> </td> >>>> >>>></tr> >>>></cfoutput> >>>></table> >>>><CFCATCH type="any"> >>>><CFOUTPUT>Didn't happen. A #cfcatch.type# exception occurred. >>>>#cfcatch.message# #cfcatch.Detail#<br> >>>><br> >>>><CFDUMP var="#cfcatch.tagcontext#"> >>>></CFOUTPUT> >>>></CFCATCH> >>>> >>>></CFTRY> >>>> >>>>====END OF CODE FOR RESSGETYES.CFM============== >>>>+++++++++++++++++++++++++++++++++++++++++++++ >>>>CODE FOR RSSGETNO.CFM: >>>> >>>><CFTRY> >>>><cfhttp url="http://pasa-online.org/rss/pasa.xml"; method="get"> >>>><cfset myQuery=QueryNew('Title, Link, Description,pubDate')> >>>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>>><cfset Items=myDoc.rss.channel.XMLChildren> >>>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>>> >>>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>>> >>>><cfset QueryAddRow(myQuery,1)> >>>><cfset >>>>QuerySetCell(myQuery,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery,'pubDate',myDoc.rss.channel.item[idx].pubDate.XMLText)> >>>> >>>></cfloop> >>>> >>>><CFOUTPUT> >>>><CFDUMP var="#myQuery#"> >>>></CFOUTPUT> >>>> >>>><cfhttp url="http://www.lapresenters.org/rss/PrincessTheaterInc.xml"; >>>>method="get"> >>>><cfset myQuery2=QueryNew('Title, Link, Description,pubDate')> >>>><cfset myDoc=XMLParse(CFHTTP.FileContent)> >>>><cfset Items=myDoc.rss.channel.XMLChildren> >>>><cfset myItemsArrayLength=ArrayLen(Items)-8> >>>><cfloop from="1" to="#myItemsArrayLength#" index="idx"> >>>><cfset QueryAddRow(myQuery2,1)> >>>><cfset >>>>QuerySetCell(myQuery2,'Title',myDoc.rss.channel.item[idx].Title.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery2,'Link',myDoc.rss.channel.item[idx].Link.XMLText)> >>>><cfset >>>>QuerySetCell(myQuery2,'Description',myDoc.rss.channel.item[idx].Description.XMLText)> >>>> >>>><cfset >>>>QuerySetCell(myQuery2,'pubDate',myDoc.rss.channel.item[idx].pubdate.XMLText)> >>>> >>>></cfloop> >>>> >>>><CFOUTPUT> >>>><CFDUMP var="#myQuery2#"> >>>></CFOUTPUT> >>>> >>>><cfquery dbtype="query" name="GetBoth"> >>>>SELECT * FROM MyQuery >>>>UNION >>>>SELECT * FROM MyQuery2 >>>>ORDER BY pubDate >>>></cfquery> >>>><table width="550" border="0" cellspacing="0" cellpadding="2"> >>>><TR> >>>> <TD colspan=2 valign=top><font size="2" face="Georgia, Times New >>>>Roman, Times, serif">XML >>>> Parsed and combined listings >>>> from two different web site sources<br> >>>> </font></TD> >>>></TR> >>>><cfoutput query="GetBoth"> >>>><tr> >>>> <td valign=top width=100 align=right> >>>> >>>></td> >>>><td valign=top width=450 align=left> >>>> <font size="1" face="Arial, Helvetica, sans-serif"> <a >>>>href="#link#"><strong>#title#</strong></a> >>>> #description#</font> >>>> </td> >>>> >>>></tr> >>>></cfoutput> >>>></table> >>>><CFCATCH type="any"> >>>><CFOUTPUT>Didn't happen. A #cfcatch.type# exception occurred. >>>>#cfcatch.message# #cfcatch.Detail#<br> >>>><br> >>>><CFDUMP var="#cfcatch.tagcontext#"> >>>></CFOUTPUT> >>>></CFCATCH> >>>> >>>></CFTRY> >>>> >>>> >>>> >>>> >>>> >>>> >>>> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
