Making use of cached queries
Hi. I have a cached query, as below, which runs on a number of pages: cfquery cachedwithin=#createtimespan(1,0,0,0)# name=findcats datasource=#application.DSN# SELECT sub_name,cat_name FROM categories, sub_cats WHERE categories.ID = sub_cats.cat_id ORDER by cat_name /cfquery The data in the query will only be changed monthly, at most. If I had a var such as cfset LeadCat = 100 (which would correspond to categories.ID), could I grab categories.cat_name from the cached query without altering it, somehow? I could probably have worded it better, but I hope it's enough for someone to understand! Thanks Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Making use of cached queries
Hi Will, If I understand what you're asking you should be able to accomplish that by using QueryOfQuery functionality which lets u use regular SQL to query an existing recordset in memory. http://livedocs.macromedia.com/cfmxdocs/Developing_ColdFusion_MX_Application s_with_CFML/using_recordsets.jsp Stace -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 6:12 PM To: CF-Talk Subject: Making use of cached queries Hi. I have a cached query, as below, which runs on a number of pages: cfquery cachedwithin=#createtimespan(1,0,0,0)# name=findcats datasource=#application.DSN# SELECT sub_name,cat_name FROM categories, sub_cats WHERE categories.ID = sub_cats.cat_id ORDER by cat_name /cfquery The data in the query will only be changed monthly, at most. If I had a var such as cfset LeadCat = 100 (which would correspond to categories.ID), could I grab categories.cat_name from the cached query without altering it, somehow? I could probably have worded it better, but I hope it's enough for someone to understand! Thanks Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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
Re: Making use of cached queries
W Luke mailto:[EMAIL PROTECTED] wrote: If I understand what you're asking you should be able to accomplish that by using QueryOfQuery functionality which lets u use regular SQL to query an existing recordset in memory. Completely forgot about QoQ - been salivating over that for years, and only now (having moved to MX) do I have a chance to use it! Does it have a problem in calling TOP 2 records? For instance: Cfquery name=mytest dbtype=query SELECT TOP 2 ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY That causes an error with me. Any ideas? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Making use of cached queries
Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Ken -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 4:05 PM To: CF-Talk Subject: Re: Making use of cached queries W Luke mailto:[EMAIL PROTECTED] wrote: If I understand what you're asking you should be able to accomplish that by using QueryOfQuery functionality which lets u use regular SQL to query an existing recordset in memory. Completely forgot about QoQ - been salivating over that for years, and only now (having moved to MX) do I have a chance to use it! Does it have a problem in calling TOP 2 records? For instance: Cfquery name=mytest dbtype=query SELECT TOP 2 ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY That causes an error with me. Any ideas? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Making use of cached queries
Ken Brocx [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does it have a problem in calling TOP 2 records? For instance: Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Thanks Ken. And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: Making use of cached queries
replace the double quotes around Yes with single quotes 'Yes' and it should run OK. Marius Milosav www.scorpiosoft.com It's not about technology, it's about people. Virtual Company (VICO) Application Demo www.scorpiosoft.com/vicodemo/login.cfm - Original Message - From: W Luke [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, October 04, 2002 7:20 PM Subject: Re: Making use of cached queries Ken Brocx [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does it have a problem in calling TOP 2 records? For instance: Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Thanks Ken. And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: Making use of cached queries
Marius Milosav [EMAIL PROTECTED] wrote in message news:000d01c26bf5$17838510$[EMAIL PROTECTED]... replace the double quotes around Yes with single quotes 'Yes' and it should run OK. There shouldn't be any quotes, single or double - no need for them. I tried single quotes anyway, and it caused a new (and apparently more common) error: Unsupported type comparison. I've read lots from people having problems with these QoQs migrating from 5-to-MX - has anyone got any advice? Seems a great shame I won't be able to make use of QoQ's Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Making use of cached queries
W Luke wrote: Marius Milosav [EMAIL PROTECTED] wrote in message news:000d01c26bf5$17838510$[EMAIL PROTECTED]... replace the double quotes around Yes with single quotes 'Yes' and it should run OK. There shouldn't be any quotes, single or double - no need for them. I tried single quotes anyway, and it caused a new (and apparently more common) error: Unsupported type comparison. I've read lots from people having problems with these QoQs migrating from 5-to-MX - has anyone got any advice? Lots of people get the same advice: cfqueryparam :) Actually, I have never tried it with a boolean field before, and boolean is not a supported datatype for cfqueryparam (although you might try bit). Also, I would imagine it depends quite a bit on what your database returns *exactly*. Does it return YES/NO, TRUE/FALSE, 't'/'f' or 1/0? All of these are options to try, but based on the error I would go for either: Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 AND approved = 1 /CFQUERY or: Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 AND approved = cfqueryparam cfsqltype=cf_sql_bit' value=TRUE /CFQUERY Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Making use of cached queries
And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? I always use queryparam lately just solves a lot of problems. It depends on your database. Which are you using? And what type of field is it? Is it an actual yes/no field or a bit field with 1/0? I haven't had any problems with bit fields or boolean fields in a QoQ but I'm using SQLServer2k. I'd try either using cfqueryparam value=1 cfsqltype=CF_SQL_BIT Or cfqueryparam value=Yes cfsqltype=CF_SQL_VARCHAR Although IMHO all yes/no fields should just be bit fields Good luck have a great weekend! Ken -Original Message- From: W Luke [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 4:21 PM To: CF-Talk Subject: Re: Making use of cached queries Ken Brocx [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Does it have a problem in calling TOP 2 records? For instance: Try Cfquery name=mytest dbtype=query maxrows=2 SELECT ProductName FROM AllPaid WHERE ProductID = 5 /CFQUERY Thanks Ken. And last but not least, how should I treat Yes/No fields in a QoQ? For example something like WHERE ProductID = 2 AND approved = Yes causes a The select column reference [Yes] is not a column in any of the tables of the FROM table list. error. Is this a task for cfqueryparam? Will ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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
