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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
