cfquery multiple column search
All - I am working on a search functionality for multiple columns of data. I using 'OR' condition for searching on a single search input. For example on front end i have a search input box, and I can enter either numeric or alphabets or alphanumeric characters. How can I handle this using cfqueryparam? if the database column is a numeric, and I enter alphabets, I get errors like Invalid data %0% for CFSQLTYPE CF_SQL_INTEGER. I am first retreiving the full result set and performing query of queries to filter on the search criteria. my query of query looks like below. The above error happens on col5 search when I search string characters like 'abc'. Are there any functions in coldfusion that help with this kind of scenarios? cfquery name=getinfo dbtype=query SELECT col1, col2, col3, col4, col5 FROM getinfo WHERE (lower(col2) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col3) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col4) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col5) like lower(cfqueryparam cfsqltype=cf_sql_integer value=%#val(Arguments.search)#%) ) /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357647 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery multiple column search
You need to verify that the search terms you are providing for that column are numeric. The cfqueryparam does that but returns an error when it finds a problem. It's up to you to make sure that you are providing the proper type for the columns you are querying. In this case if the search terms are not numeric then you want to skip the column all together. You need to check that it is both numeric and an integer. cfquery name=getinfo dbtype=query SELECT col1, col2, col3, col4, col5 FROM getinfo WHERE (lower(col2) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col3) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col4) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) cfif IsNumeric(arguments.search) and (Int(arguments.search) is arguments.search) OR lower(col5) like lower(cfqueryparam cfsqltype=cf_sql_integer value=%#val(Arguments.search)#%) /cfif ) /cfquery David Phelan Web Developer IT Security Web Technologies Emerging Health Montefiore Information Technology 3 Odell Plaza, Yonkers, NY 10701 914-457-6465 Office 862-234-9109 Cell dphe...@emerginghealthit.com www.emerginghealthit.com www.montefiore.org -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: Monday, February 10, 2014 10:04 AM To: cf-talk Subject: cfquery multiple column search All - I am working on a search functionality for multiple columns of data. I using 'OR' condition for searching on a single search input. For example on front end i have a search input box, and I can enter either numeric or alphabets or alphanumeric characters. How can I handle this using cfqueryparam? if the database column is a numeric, and I enter alphabets, I get errors like Invalid data %0% for CFSQLTYPE CF_SQL_INTEGER. I am first retreiving the full result set and performing query of queries to filter on the search criteria. my query of query looks like below. The above error happens on col5 search when I search string characters like 'abc'. Are there any functions in coldfusion that help with this kind of scenarios? cfquery name=getinfo dbtype=query SELECT col1, col2, col3, col4, col5 FROM getinfo WHERE (lower(col2) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col3) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col4) like lower(cfqueryparam cfsqltype=cf_sql_varchar value=%#Arguments.search#%) OR lower(col5) like lower(cfqueryparam cfsqltype=cf_sql_integer value=%#val(Arguments.search)#%) ) /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357652 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
Dave wasn't wrong, he was differently right. --rt On 8/19/13 10:08 AM, Raymond Camden wrote: I'm going to go print this email out right now. For the next time Dave is wrong. Sometime in 2019. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356532 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
I like the Mega mind response... you were right, I was less right On Wed, Aug 21, 2013 at 5:28 PM, Ron Thigpen r...@fuzzsonic.com wrote: Dave wasn't wrong, he was differently right. --rt On 8/19/13 10:08 AM, Raymond Camden wrote: I'm going to go print this email out right now. For the next time Dave is wrong. Sometime in 2019. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356533 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Using IN() within a cfquery statement
I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356483 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Use the quotedValueList function to wrap single quotes around them. SELECT ... FROM ... WHERE STUFF IN (#quotedValueList(form.stuff)#) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356484 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Using IN() within a cfquery statement
Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Steve -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, August 19, 2013 9:42 AM To: cf-talk Subject: Re: Using IN() within a cfquery statement I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Use the quotedValueList function to wrap single quotes around them. SELECT ... FROM ... WHERE STUFF IN (#quotedValueList(form.stuff)#) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356485 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Using IN() within a cfquery statement
Dave is allowed one weak answer per year... he's waited til August so I say we give him a break :) -Mark -Original Message- From: DURETTE, STEVEN J [mailto:sd1...@att.com] Sent: Monday, August 19, 2013 8:46 AM To: cf-talk Subject: RE: Using IN() within a cfquery statement Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Steve -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, August 19, 2013 9:42 AM To: cf-talk Subject: Re: Using IN() within a cfquery statement I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Use the quotedValueList function to wrap single quotes around them. SELECT ... FROM ... WHERE STUFF IN (#quotedValueList(form.stuff)#) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356486 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Yes, that would absolutely be a better way, of course! I didn't even pay attention to the fact that this was in the form scope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356487 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
I'm going to go print this email out right now. For the next time Dave is wrong. Sometime in 2019. On Mon, Aug 19, 2013 at 8:53 AM, Dave Watts dwa...@figleaf.com wrote: Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Yes, that would absolutely be a better way, of course! I didn't even pay attention to the fact that this was in the form scope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356489 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
I take an entirely different viewpoint on Dave's answer. I read the original post and declined to answer because it seemed pretty obvious that ZERO effort was put in place by the questioner to find an answer. My bet is Dave had a similar feeling regarding the effort, but instead of declining to answer gave the quickest and easiest answer that could/would be found by a quick search or perusal of the documentation. Sometimes RTFM *is* the appropriate response. ;-) On Mon, Aug 19, 2013 at 9:50 AM, Mark A Kruger mkru...@cfwebtools.comwrote: Dave is allowed one weak answer per year... he's waited til August so I say we give him a break :) -Mark -Original Message- From: DURETTE, STEVEN J [mailto:sd1...@att.com] Sent: Monday, August 19, 2013 8:46 AM To: cf-talk Subject: RE: Using IN() within a cfquery statement Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Steve -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, August 19, 2013 9:42 AM To: cf-talk Subject: Re: Using IN() within a cfquery statement I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Use the quotedValueList function to wrap single quotes around them. SELECT ... FROM ... WHERE STUFF IN (#quotedValueList(form.stuff)#) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356488 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Using IN() within a cfquery statement
Matt, I was just teasing Dave. I actually have a lot of respect for Dave for all the information he gives and his ability to put it in a security context. In regards to your response about the quickest answer because someone didn't RTFM and coming from a security background, I always believe that it is better to give a security focused answer if possible because for too long security hasn't even been a consideration in programming even though it should be one of the first things taught. Also, we also need to consider that RTFM might not be a totally viable option in some situations. I know from trying to look something up last week that I couldn't get to cfquickdocs and cfgloss took over 15 minutes to load before I could search. This also could be a situation where the original poster has access to email but their web browsers are restricted to internal sites only. Not trying to start a fight, just giving another perspective. We've all been there, super short deadline and the mind goes blank. Have a great day! Steve -Original Message- From: Matt Quackenbush [mailto:quackfu...@gmail.com] Sent: Monday, August 19, 2013 10:05 AM To: cf-talk Subject: Re: Using IN() within a cfquery statement I take an entirely different viewpoint on Dave's answer. I read the original post and declined to answer because it seemed pretty obvious that ZERO effort was put in place by the questioner to find an answer. My bet is Dave had a similar feeling regarding the effort, but instead of declining to answer gave the quickest and easiest answer that could/would be found by a quick search or perusal of the documentation. Sometimes RTFM *is* the appropriate response. ;-) On Mon, Aug 19, 2013 at 9:50 AM, Mark A Kruger mkru...@cfwebtools.comwrote: Dave is allowed one weak answer per year... he's waited til August so I say we give him a break :) -Mark -Original Message- From: DURETTE, STEVEN J [mailto:sd1...@att.com] Sent: Monday, August 19, 2013 8:46 AM To: cf-talk Subject: RE: Using IN() within a cfquery statement Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Steve -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, August 19, 2013 9:42 AM To: cf-talk Subject: Re: Using IN() within a cfquery statement I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Use the quotedValueList function to wrap single quotes around them. SELECT ... FROM ... WHERE STUFF IN (#quotedValueList(form.stuff)#) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356490 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
While people are sometimes lazy, it is not fair to just jump to that conclusion, and it is that negative attitude that has driven so many people to leave this list. It is all well and good to say RTFM, but if you are a newbie and do not know what you are looking for, you could easily completely miss quotedValueList() On Mon, Aug 19, 2013 at 3:04 PM, Matt Quackenbush quackfu...@gmail.comwrote: I take an entirely different viewpoint on Dave's answer. I read the original post and declined to answer because it seemed pretty obvious that ZERO effort was put in place by the questioner to find an answer. My bet is Dave had a similar feeling regarding the effort, but instead of declining to answer gave the quickest and easiest answer that could/would be found by a quick search or perusal of the documentation. Sometimes RTFM *is* the appropriate response. ;-) On Mon, Aug 19, 2013 at 9:50 AM, Mark A Kruger mkru...@cfwebtools.com wrote: Dave is allowed one weak answer per year... he's waited til August so I say we give him a break :) -Mark -Original Message- From: DURETTE, STEVEN J [mailto:sd1...@att.com] Sent: Monday, August 19, 2013 8:46 AM To: cf-talk Subject: RE: Using IN() within a cfquery statement Dave... I'm surprised. Wouldn't you think that CFQueryParam would be the better way? Where stuff in (cfqueryparam value=#form.stuff# cfsqltype=appropriate type list=yes separator=, /) Steve -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: Monday, August 19, 2013 9:42 AM To: cf-talk Subject: Re: Using IN() within a cfquery statement I have a select name=stuff multiple in a form filled with results from a query. I get the form field value: stuff=selection1,selection5,selection12. How do I then build a cfquery using the stuff variable in the IN() statement? Use the quotedValueList function to wrap single quotes around them. SELECT ... FROM ... WHERE STUFF IN (#quotedValueList(form.stuff)#) Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
I take an entirely different viewpoint on Dave's answer. I read the original post and declined to answer because it seemed pretty obvious that ZERO effort was put in place by the questioner to find an answer. My bet is Dave had a similar feeling regarding the effort, but instead of declining to answer gave the quickest and easiest answer that could/would be found by a quick search or perusal of the documentation. No, not really. I just have a weak spot for valueList and quotedValueList. There's a long and not very interesting story behind that that I won't bother going into here. Had I put more thought into my response, I'd have followed Steven's advice. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356492 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
I wonder if the problems with cfquickdocs and cfgloss are related to the switchover of the ColdFusion docs to the new wiki format? Or is that only for CF10 docs? -Carl V. On 8/19/2013 7:15 AM, DURETTE, STEVEN J wrote: I know from trying to look something up last week that I couldn't get to cfquickdocs and cfgloss took over 15 minutes to load before I could search. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356497 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Using IN() within a cfquery statement
coldfusiondocs.com not working properly either, so seems likely. On Mon, Aug 19, 2013 at 4:26 PM, Carl Von Stetten vonner.li...@vonner.netwrote: I wonder if the problems with cfquickdocs and cfgloss are related to the switchover of the ColdFusion docs to the new wiki format? Or is that only for CF10 docs? -Carl V. On 8/19/2013 7:15 AM, DURETTE, STEVEN J wrote: I know from trying to look something up last week that I couldn't get to cfquickdocs and cfgloss took over 15 minutes to load before I could search. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356498 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY immediately throws blank CF error and HTTP 500 code when more than 50 records will be returned (CF6)
Not sure.. we don't handle much of the system software/hardware on the box. Do you know what that may affect or how to check when it was updated? Has anyone updated the java version by any chance Russ Michaels www.michaels.me.uk On 9 Jul 2013 23:11, Chris Johnson ejohn...@directalliance.com wrote: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356161 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY immediately throws blank CF error and HTTP 500 code when more than 50 records will be returned (CF6)
Do any other CF requests work on that server? Sounds like a web connector issue perhaps. Byron Mann Lead Engineer Architect HostMySite.com On Jul 9, 2013 6:11 PM, Chris Johnson ejohn...@directalliance.com wrote: Really odd issue we've been having.. We have a scheduled task that hits another database server to get a full list of employees. It then runs through the user accounts for this specific app and adds or updates accounts as needed. We weren't aware that it had been broken, so we don't know exactly what change on the server side could have done it (virtual instance). If the query looks like: select TOP 5 somecolumn from employees - the query runs If the query looks like: select somecolumn from employees - a blank CF error (no message or details) is thrown immediately without trying to run the query I've upped the TOP 5 to about ~50 and around that and above, it either throws the immediate CF error or attempts to run and then brings down ColdFusion. On one of our other CF boxes with less memory (4gb vs 512mb), the query runs and returns the 18,000 rows within seconds. Looking at Task Manager, I don't see any spikes in memory or CPU when the page is ran. The error is thrown immediately, almost like the server just refused to bother. It seems like ColdFusion is either trying to allocate resources and not liking the info it gets or is somehow otherwise sizing up what will be needed for the call. I can't think of any other reason why it wouldn't run and time out like other queries. The CF error returned is blank and thrown immediately. We've created a new DSN using the fully qualified domain name, IP address, and even changed the query to a stored proc that should have resulted in less overhead on CF and nothing seems to help. If we don't specify a TOP XX number or specify one that's too large (50+), we get this strange result. Otherwise it runs or at least attempts to run just fine. Being that we're using an long outdated version of CF, it's been hard to find support specific to this issue and I need to try to throw our server folks a bone as these issues tend to become IT hot potato games (not the server, it's CF... not CF, it's the database, etc.). Anyone experience a similar issue? I've created tons of queries in the last 10 years, crashed tons of CF instances, and written tons of bad SQL, but this issue is so odd that I've never encountered it in the wild. In the CF Admin settings, the CF version on the trouble server is showing as 6,1,0,83762. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356162 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY immediately throws blank CF error and HTTP 500 code when more than 50 records will be returned (CF6)
you should ask whoever manages the server, as updating to new java versions can have bizzare results like this where everything seems to work except one random thing. you can also see the java version being used on the info page in cfadmin. you should also try rebuilding your conenctors, as they sometimes get corrupted. The easy way to do this is to use the Coldfusion Web config tool to disable then re-enable CF, which will create a new connector. the other issue could be the number of connections, how busy is the server? when you do these tests is it always re-producible without fail ? it could also be your DSN settings, can you tell me what you have them set to currently, the advanced settings mainly. On Wed, Jul 10, 2013 at 5:45 PM, Chris Johnson ejohn...@directalliance.comwrote: Not sure.. we don't handle much of the system software/hardware on the box. Do you know what that may affect or how to check when it was updated? Has anyone updated the java version by any chance Russ Michaels www.michaels.me.uk On 9 Jul 2013 23:11, Chris Johnson ejohn...@directalliance.com wrote: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQUERY immediately throws blank CF error and HTTP 500 code when more than 50 records will be returned (CF6)
Really odd issue we've been having.. We have a scheduled task that hits another database server to get a full list of employees. It then runs through the user accounts for this specific app and adds or updates accounts as needed. We weren't aware that it had been broken, so we don't know exactly what change on the server side could have done it (virtual instance). If the query looks like: select TOP 5 somecolumn from employees - the query runs If the query looks like: select somecolumn from employees - a blank CF error (no message or details) is thrown immediately without trying to run the query I've upped the TOP 5 to about ~50 and around that and above, it either throws the immediate CF error or attempts to run and then brings down ColdFusion. On one of our other CF boxes with less memory (4gb vs 512mb), the query runs and returns the 18,000 rows within seconds. Looking at Task Manager, I don't see any spikes in memory or CPU when the page is ran. The error is thrown immediately, almost like the server just refused to bother. It seems like ColdFusion is either trying to allocate resources and not liking the info it gets or is somehow otherwise sizing up what will be needed for the call. I can't think of any other reason why it wouldn't run and time out like other queries. The CF error returned is blank and thrown immediately. We've created a new DSN using the fully qualified domain name, IP address, and even changed the query to a stored proc that should have resulted in less overhead on CF and nothing seems to help. If we don't specify a TOP XX number or specify one that's too large (50+), we get this strange result. Otherwise it runs or at least attempts to run just fine. Being that we're using an long outdated version of CF, it's been hard to find support specific to this issue and I need to try to throw our server folks a bone as these issues tend to become IT hot potato games (not the server, it's CF... not CF, it's the database, etc.). Anyone experience a similar issue? I've created tons of queries in the last 10 years, crashed tons of CF instances, and written tons of bad SQL, but this issue is so odd that I've never encountered it in the wild. In the CF Admin settings, the CF version on the trouble server is showing as 6,1,0,83762. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356143 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY immediately throws blank CF error and HTTP 500 code when more than 50 records will be returned (CF6)
Has anyone updated the java version by any chance Russ Michaels www.michaels.me.uk On 9 Jul 2013 23:11, Chris Johnson ejohn...@directalliance.com wrote: Really odd issue we've been having.. We have a scheduled task that hits another database server to get a full list of employees. It then runs through the user accounts for this specific app and adds or updates accounts as needed. We weren't aware that it had been broken, so we don't know exactly what change on the server side could have done it (virtual instance). If the query looks like: select TOP 5 somecolumn from employees - the query runs If the query looks like: select somecolumn from employees - a blank CF error (no message or details) is thrown immediately without trying to run the query I've upped the TOP 5 to about ~50 and around that and above, it either throws the immediate CF error or attempts to run and then brings down ColdFusion. On one of our other CF boxes with less memory (4gb vs 512mb), the query runs and returns the 18,000 rows within seconds. Looking at Task Manager, I don't see any spikes in memory or CPU when the page is ran. The error is thrown immediately, almost like the server just refused to bother. It seems like ColdFusion is either trying to allocate resources and not liking the info it gets or is somehow otherwise sizing up what will be needed for the call. I can't think of any other reason why it wouldn't run and time out like other queries. The CF error returned is blank and thrown immediately. We've created a new DSN using the fully qualified domain name, IP address, and even changed the query to a stored proc that should have resulted in less overhead on CF and nothing seems to help. If we don't specify a TOP XX number or specify one that's too large (50+), we get this strange result. Otherwise it runs or at least attempts to run just fine. Being that we're using an long outdated version of CF, it's been hard to find support specific to this issue and I need to try to throw our server folks a bone as these issues tend to become IT hot potato games (not the server, it's CF... not CF, it's the database, etc.). Anyone experience a similar issue? I've created tons of queries in the last 10 years, crashed tons of CF instances, and written tons of bad SQL, but this issue is so odd that I've never encountered it in the wild. In the CF Admin settings, the CF version on the trouble server is showing as 6,1,0,83762. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356144 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SSL on CFquery
Thanks Russ, ill take a look into this You don't, afaik ypu simply need the client cert in the java keystore. See this http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html There is a handu cfadmin extension on riaforge.org for managing your keystore. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Apr 16, 2013 5:33 PM, Richard White rich...@re-base.net wrote: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355509 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SSL on CFquery
Hi, We have a windows server for our CF application and a Linux server for our database. We are setting up a self-signed SSL between the two servers. Our hosting company have said we need to reference the SSL in the connection string but how can I do this in a cfquery? Many thanks Richard ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355424 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SSL on CFquery
Do the following to enable SSL connection: 1. In the ColdFusion Administrator, go to Data Services Data Sources. 2. Select the data source to enable SSL Connection. 3. In the data source page, click Show Advanced Settings. 4. In the Connection String text box, specify the connection properties as per the SSL requirements. you can find detail son the connection properties here http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WS50260aa90e50c24b-32f8955c122c2720693-8000.html On Tue, Apr 16, 2013 at 12:58 PM, Richard White rich...@re-base.net wrote: Hi, We have a windows server for our CF application and a Linux server for our database. We are setting up a self-signed SSL between the two servers. Our hosting company have said we need to reference the SSL in the connection string but how can I do this in a cfquery? Many thanks Richard ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355425 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SSL on CFquery
Perfect! Many thanks Russ :) Do the following to enable SSL connection: 1. In the ColdFusion Administrator, go to Data Services Data Sources. 2. Select the data source to enable SSL Connection. 3. In the data source page, click Show Advanced Settings. 4. In the Connection String text box, specify the connection properties as per the SSL requirements. you can find detail son the connection properties here http://help.adobe.com/en_US/ColdFusion/9. 0/Admin/WS50260aa90e50c24b-32f8955c122c2720693-8000.html On Tue, Apr 16, 2013 at 12:58 PM, Richard White rich...@re-base.net wrote: Hi, We have a windows server for our CF application and a Linux server for our database. We are setting up a self-signed SSL between the two servers. Our hosting company have said we need to reference the SSL in the connection string but how can I do this in a cfquery? Many thanks Richard ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355426 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SSL on CFquery
Hi, We have a further issue with this. The hosting company have installed the SSL certificate on the database and provided us with the details of where the certificate is stored. However, in the CF connection string it asks to provide the url of the certificate. I am confused about how to get this to work. How would i instruct coldfusion to use the certificate on the database server? Thanks, Richard Hi, We have a windows server for our CF application and a Linux server for our database. We are setting up a self-signed SSL between the two servers. Our hosting company have said we need to reference the SSL in the connection string but how can I do this in a cfquery? Many thanks Richard ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355442 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SSL on CFquery
You don't, afaik ypu simply need the client cert in the java keystore. See this http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html There is a handu cfadmin extension on riaforge.org for managing your keystore. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Apr 16, 2013 5:33 PM, Richard White rich...@re-base.net wrote: Hi, We have a further issue with this. The hosting company have installed the SSL certificate on the database and provided us with the details of where the certificate is stored. However, in the CF connection string it asks to provide the url of the certificate. I am confused about how to get this to work. How would i instruct coldfusion to use the certificate on the database server? Thanks, Richard Hi, We have a windows server for our CF application and a Linux server for our database. We are setting up a self-signed SSL between the two servers. Our hosting company have said we need to reference the SSL in the connection string but how can I do this in a cfquery? Many thanks Richard ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355450 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery results - related question
A related question: I have a table of towns that has ~20,000 records. The dataset I need has the record ID, town name, a memo field (usually empty), and fields holding an index into other tables (township, county, state, country). In my form I have a select field to pick the town. I don't want to populate that with 20,000 options so I have a way for the user to pare down the possibilities based on the first letter of the town name. I then make an ajax call and use jquery/javascript to populate the select field options. Which is better: run a query of the table at the top of the application and store the results in an APPLICATION variable, then do a query of queries for each subset, or just query the table directly for each subset? My initial thinking is the former but now I'm wondering. I do know the first option runs quickly, returning about 300 records for M and populating the select field in 1-2 seconds (I haven't actually timed it, and that's lightning fast for most users of the app). Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353795 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results - related question
list of towns is not really a huge query, just do it once and cache and then do a query of query for your auto complete. On Mon, Jan 7, 2013 at 2:06 PM, Stephens, Larry V steph...@iu.edu wrote: A related question: I have a table of towns that has ~20,000 records. The dataset I need has the record ID, town name, a memo field (usually empty), and fields holding an index into other tables (township, county, state, country). In my form I have a select field to pick the town. I don't want to populate that with 20,000 options so I have a way for the user to pare down the possibilities based on the first letter of the town name. I then make an ajax call and use jquery/javascript to populate the select field options. Which is better: run a query of the table at the top of the application and store the results in an APPLICATION variable, then do a query of queries for each subset, or just query the table directly for each subset? My initial thinking is the former but now I'm wondering. I do know the first option runs quickly, returning about 300 records for M and populating the select field in 1-2 seconds (I haven't actually timed it, and that's lightning fast for most users of the app). Larry Stephens ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353796 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
Very slick. Thanks guys! --Ben On 1/5/2013 3:39 PM, =?ISO-8859-1?Q?Claude_Schn=E9egans wrote: 2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys. This is a pretty good method. I tested it on a database containing about 45 records with a seach template. I give a very loose criterion on purpose, the query takes 53 sec and returns 44500 records. This is ridiculous since the user will only see ten records at a time, using startRow and maxRows in a CFOUTPUT. And for the next page, it takes another 50sec :-( A certain amount of time is also taken by CFX_highlight which highlights every occurence of the search string, again in the 44500 records! With this method, the query returns only the 10 records needed, and it takes about 5 sec the first time and 3 sec any subsequent times. And the CFX_highlight is applied on only 10 records at a time. A big difference. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353792 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
Another method, assuming you're using MS SQL Server (not sure how far this goes back compatibility-wise) is to toss in the criteria for the full search, but then to only pull back X rows: WITH Results_Full AS ( SELECT Field1, Field2, Field3, ROW_NUMBER() OVER (ORDER BY Field1) AS RowNum FROM tableName ) SELECT Field1, Field2, Field3 FROM Results_Full WHERE RowNum = #StartRowNum# AND RowNum = #EndRowNum# Where StartRowNum/EndRowNum are whatever you set. I've found this method to be superfast when querying large recordsets. Works great especially if you're doing a show X rows on a page thing with forward/back/etc. --Scott On 1/5/13 3:39 PM, =?ISO-8859-1?Q?Claude_Schn=E9egans wrote: 2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys. This is a pretty good method. I tested it on a database containing about 45 records with a seach template. I give a very loose criterion on purpose, the query takes 53 sec and returns 44500 records. This is ridiculous since the user will only see ten records at a time, using startRow and maxRows in a CFOUTPUT. And for the next page, it takes another 50sec :-( A certain amount of time is also taken by CFX_highlight which highlights every occurence of the search string, again in the 44500 records! With this method, the query returns only the 10 records needed, and it takes about 5 sec the first time and 3 sec any subsequent times. And the CFX_highlight is applied on only 10 records at a time. A big difference. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353793 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
On Sat, Jan 5, 2013 at 8:25 AM, Russ Michaels r...@michaels.me.uk wrote: 2 simple solutions are. 1. Dont query all the data at all, instead provide a search form to get at specific records, which is usually much simpler thsn paging through hundreds or thousands of records. 2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys. If you know you are going to have huge datasets shouldn't you also use blockfactor? my assumption is that while it might not speed things up, it's at least clearing things out of buffer while it works on more records. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353794 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
That's what I suspected. Much appreciated. --Ben On 1/4/2013 6:40 AM, Dave Watts wrote: A question came up recently with one of my client developers who is potentially returning a large # of rows from a query. The question was whether the result set is stored in memory or spooled to disk somewhere. I didn't know but assumed it was memory resident. Anyone know the answer to this? Can it be controlled and/or limited? (CF 9) All CF variables are stored in memory. You can limit the size of the resultset by writing your SQL accordingly, but that's it really. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353786 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
Actually there are ways to step through a result set using database functions so that you do bot store huge resultsets in memory, if you google it then you will find some examples. 2 simple solutions are. 1. Dont query all the data at all, instead provide a search form to get at specific records, which is usually much simpler thsn paging through hundreds or thousands of records. 2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 5, 2013 11:12 AM, Ben Conner b...@webworldinc.com wrote: That's what I suspected. Much appreciated. --Ben On 1/4/2013 6:40 AM, Dave Watts wrote: A question came up recently with one of my client developers who is potentially returning a large # of rows from a query. The question was whether the result set is stored in memory or spooled to disk somewhere. I didn't know but assumed it was memory resident. Anyone know the answer to this? Can it be controlled and/or limited? (CF 9) All CF variables are stored in memory. You can limit the size of the resultset by writing your SQL accordingly, but that's it really. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353787 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys. This is a pretty good method. I tested it on a database containing about 45 records with a seach template. I give a very loose criterion on purpose, the query takes 53 sec and returns 44500 records. This is ridiculous since the user will only see ten records at a time, using startRow and maxRows in a CFOUTPUT. And for the next page, it takes another 50sec :-( A certain amount of time is also taken by CFX_highlight which highlights every occurence of the search string, again in the 44500 records! With this method, the query returns only the 10 records needed, and it takes about 5 sec the first time and 3 sec any subsequent times. And the CFX_highlight is applied on only 10 records at a time. A big difference. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353790 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
Glad it helped. Also dont forget u.can cache the original primary key query too. Regards Russ Michaels www.michaels.me.uk www.cfmldeveloper.com - Free CFML hosting for developers www.cfsearch.com - CF search engine On Jan 5, 2013 10:39 PM, wrote: 2. Only query the primary keys, and then loop over that list grabbing x records at a time and doing a new query to get all rows for those keys. This is a pretty good method. I tested it on a database containing about 45 records with a seach template. I give a very loose criterion on purpose, the query takes 53 sec and returns 44500 records. This is ridiculous since the user will only see ten records at a time, using startRow and maxRows in a CFOUTPUT. And for the next page, it takes another 50sec :-( A certain amount of time is also taken by CFX_highlight which highlights every occurence of the search string, again in the 44500 records! With this method, the query returns only the 10 records needed, and it takes about 5 sec the first time and 3 sec any subsequent times. And the CFX_highlight is applied on only 10 records at a time. A big difference. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353791 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery results--memory resident?
A question came up recently with one of my client developers who is potentially returning a large # of rows from a query. The question was whether the result set is stored in memory or spooled to disk somewhere. I didn't know but assumed it was memory resident. Anyone know the answer to this? Can it be controlled and/or limited? (CF 9) All CF variables are stored in memory. You can limit the size of the resultset by writing your SQL accordingly, but that's it really. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353761 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery results--memory resident?
Hi, A question came up recently with one of my client developers who is potentially returning a large # of rows from a query. The question was whether the result set is stored in memory or spooled to disk somewhere. I didn't know but assumed it was memory resident. Anyone know the answer to this? Can it be controlled and/or limited? (CF 9) Thanks! --Ben -- Ben Conner b...@webworldinc.com Web World, Inc. 888-206-6486 or PO Box 1122 480-704-2000 Queen Creek, AZ 85142 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353753 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
searching between 2 date fields with where in cfquery
i have a table that has two date fields (dateinx and dateoutx) and i need to find all the results for todays date that both fall between and on that date for a given customer: cID. database is msSQL2005 cfserver is 7 if todays date is 12/2/2012examples of vialable entries: - dateinx=12/2/2012 and dateoutx = 12/5/2012 - dateinx=11/2/2012 and dateoutx = 12/2/2012 - dateinx=11/2/2012 and dateoutx = 12/25/2012 i am using smalldatetime in msSQL2005 and also would like to create a mask so that only date: 1/1/2012 would be saved. same for time: 1:30AM in a seperate field. msSQL is saving now as 1/1/2012 01:30:00AM or some such... cfquerey at this point looks like: SELECT * FROM schdl WHERE (cID = #cID#) AND tnx in advance andy ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: searching between 2 date fields with where in cfquery
i have a table that has two date fields (dateinx and dateoutx) and i need to find all the results for todays date that both fall between and on that date for a given customer: cID. This should be fairly simple to add to your existing query... AND GETDATE() BETWEEN dateinx AND dateoutx database is msSQL2005 ... i am using smalldatetime in msSQL2005 and also would like to create a mask so that only date: 1/1/2012 would be saved. same for time: 1:30AM in a seperate field. msSQL is saving now as 1/1/2012 01:30:00AM or some such... SQL Server has different data types for date and time which would store these values on their own (internally I believe they're still stored as a smalldatetime with static values for the unused portion. You can also look up the datepart() T-SQL function to break out a datetime into its component parts when needed. WHERE (cID = #cID#) AND Also make sure you put a CFQUERYPARAM tag around that cID variable as well to prevent SQL injection, among other benefits. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Date Truncation on CFquery Update
I have a client who is essentially entering a document of 50+ pages into a text area field (tiny MCE). The data is being saved to a data base via CFquery Update. The data base is MS SQL server and the field length is set to VARCHAR(MAX). Data is being truncated on update to data base (same place every time). I have checked and the data is not being lost by the text area. If I display the post field after the Update Query, all the data is there. It's definitely being truncated either by CFQuery or by the data base. I have (temporarily) removed cfqueryparam, so that is not the culprit. Any ideas what is causing the truncation? Thanks Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351564 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Date Truncation on CFquery Update
There's a post limit in the CF admin, I think the default is 100Mb but given that your post is so huge you might want to see if increasing that setting helps. -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: 13 June 2012 17:17 To: cf-talk Subject: Date Truncation on CFquery Update I have a client who is essentially entering a document of 50+ pages into a text area field (tiny MCE). The data is being saved to a data base via CFquery Update. The data base is MS SQL server and the field length is set to VARCHAR(MAX). Data is being truncated on update to data base (same place every time). I have checked and the data is not being lost by the text area. If I display the post field after the Update Query, all the data is there. It's definitely being truncated either by CFQuery or by the data base. I have (temporarily) removed cfqueryparam, so that is not the culprit. Any ideas what is causing the truncation? Thanks Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion- Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf- talk/message.cfm/messageid:351564 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubs ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351565 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Date Truncation on CFquery Update
Probably Max data length in cf admin. Look at the advanced DB connection settings. Will be set to default of 6 characters. I ran into something similar saving a wddx variable. Cheers, Rob On Jun 13, 2012 12:17 PM, Robert Harrison rob...@austin-williams.com wrote: I have a client who is essentially entering a document of 50+ pages into a text area field (tiny MCE). The data is being saved to a data base via CFquery Update. The data base is MS SQL server and the field length is set to VARCHAR(MAX). Data is being truncated on update to data base (same place every time). I have checked and the data is not being lost by the text area. If I display the post field after the Update Query, all the data is there. It's definitely being truncated either by CFQuery or by the data base. I have (temporarily) removed cfqueryparam, so that is not the culprit. Any ideas what is causing the truncation? Thanks Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351566 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Date Truncation on CFquery Update
He specifically said data was not list on the post. Check your dan for chat limits, you may also need to enable blob support. On Jun 13, 2012 5:19 PM, Edward Chanter firew...@cc.uk.com wrote: There's a post limit in the CF admin, I think the default is 100Mb but given that your post is so huge you might want to see if increasing that setting helps. -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: 13 June 2012 17:17 To: cf-talk Subject: Date Truncation on CFquery Update I have a client who is essentially entering a document of 50+ pages into a text area field (tiny MCE). The data is being saved to a data base via CFquery Update. The data base is MS SQL server and the field length is set to VARCHAR(MAX). Data is being truncated on update to data base (same place every time). I have checked and the data is not being lost by the text area. If I display the post field after the Update Query, all the data is there. It's definitely being truncated either by CFQuery or by the data base. I have (temporarily) removed cfqueryparam, so that is not the culprit. Any ideas what is causing the truncation? Thanks Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion- Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf- talk/message.cfm/messageid:351564 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubs ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351567 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Data Truncation on CFquery Update: SOLVED
Thanks Rob Parkhill. It was the Max data length in cf admin.It was set to 64,000 characters by default. Adjusting that solved the problem. Robert Harrison Director of Interactive Services Austin Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351568 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQuery to Database
if its just a few records then use query of query to get your subset of results and then insert the results into database B if this is a regular occurrence and happens often then then you may want to look at creating a DTS script on your SQL server and scheduling that to do the job. On Sat, Mar 10, 2012 at 10:32 PM, IT (Pradeep Viswanathan) prade...@emiratesnbd.com wrote: I have couple of records which are filtered from data source x which is oracle based and require to insert it into datasource y which is MS SQL server. The number of rows, columns is not fixed, however I have the records in a Query object, what would be the best way to insert get this data into the other database? Thanks in advance. DISCLAIMER: This e-mail message including any of its attachments is intended solely for the addressee(s) and may contain privileged information. If you are not the addressee or you have received this email message in error, please notify the sender who will remove your details from its database. You are not authorized to read, copy, disseminate, distribute or use this e-mail message or any attachment to it in any manner and must delete the email and destroy any hard copies of it. This e-mail message does not contain financial instructions or commitments of any kind. Any views expressed in this message are those of the individual sender and do not necessarily reflect the views of Emirates NBD PJSC, or any other related subsidiaries, entities or persons. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350349 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQuery to Database
Thanks Russ, Scott@fussionapps has been kind enough to give me a nice code.. The little challenge was that the columns were not fixed and query results are completely dyanmic... Just posting it here for reference CFLOOP query=qry_data CFQUERY name=qry_insert datasource=#request.dsn# INSERT INTO SomeTable ( CFLOOP from=1 to=#Listlen(qry_data.ColumnList)# index=i #qry_data.GetMetaData().GetColumnName(i)# CFIF !i EQ ListLen(qry_data.ColumnList), /CFIF /CFLOOP) VALUES ( CFLOOP from=1 to=#Listlen(qry_data.ColumnList)# index=i !--- Putting these into variables to make it easier for you to reuse later if needed --- CFSET variables.ColumnName = qry_data.GetMetaData().GetColumnName(i) CFSET variables.ColumnType = qry_data.GetMetaData().GetColumnTypeName(i) CFSET variables.ColumnData = qry_data[qry_data.GetMetaData().GetColumnName(i)][qry_data.currentrow] !--- Check Len so we can insert NULLs where needed --- CFIF Len(variables.ColumnData) #variables.ColumnType# CFSWITCH expression=#variables.ColumnType# !--- Things that need to be wrapped in single quotes(add as needed) --- CFCASE value=VARCHAR,NVARCHAR,DATETIME,SMALLDATETIME,UNIQUEIDENTIFIER '#variables.ColumnData#' /CFCASE !--- You can add other cases here if you want --- !--- Things that dont --- CFDEFAULTCASE #variables.ColumnData# /CFDEFAULTCASE /CFSWITCH CFELSE NULL /CFIF CFIF !i EQ ListLen(qry_data.ColumnList), /CFIF /CFLOOP) /CFQUERY /CFLOOP /CFOUTPUT Note: small corrections may be required to the code. -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Sunday, March 11, 2012 1:48 PM To: cf-talk Subject: Re: CFQuery to Database if its just a few records then use query of query to get your subset of results and then insert the results into database B if this is a regular occurrence and happens often then then you may want to look at creating a DTS script on your SQL server and scheduling that to do the job. On Sat, Mar 10, 2012 at 10:32 PM, IT (Pradeep Viswanathan) prade...@emiratesnbd.com wrote: I have couple of records which are filtered from data source x which is oracle based and require to insert it into datasource y which is MS SQL server. The number of rows, columns is not fixed, however I have the records in a Query object, what would be the best way to insert get this data into the other database? Thanks in advance. DISCLAIMER: This e-mail message including any of its attachments is intended solely for the addressee(s) and may contain privileged information. If you are not the addressee or you have received this email message in error, please notify the sender who will remove your details from its database. You are not authorized to read, copy, disseminate, distribute or use this e-mail message or any attachment to it in any manner and must delete the email and destroy any hard copies of it. This e-mail message does not contain financial instructions or commitments of any kind. Any views expressed in this message are those of the individual sender and do not necessarily reflect the views of Emirates NBD PJSC, or any other related subsidiaries, entities or persons. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350350 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQuery to Database
I have couple of records which are filtered from data source x which is oracle based and require to insert it into datasource y which is MS SQL server. The number of rows, columns is not fixed, however I have the records in a Query object, what would be the best way to insert get this data into the other database? Thanks in advance. DISCLAIMER: This e-mail message including any of its attachments is intended solely for the addressee(s) and may contain privileged information. If you are not the addressee or you have received this email message in error, please notify the sender who will remove your details from its database. You are not authorized to read, copy, disseminate, distribute or use this e-mail message or any attachment to it in any manner and must delete the email and destroy any hard copies of it. This e-mail message does not contain financial instructions or commitments of any kind. Any views expressed in this message are those of the individual sender and do not necessarily reflect the views of Emirates NBD PJSC, or any other related subsidiaries, entities or persons. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350341 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Cfquery dates help needed
Hello. I'm trying to get this cfquery to match a database field EXPIRATIONDATE(date) 2012-3-04 and do a recordcount but it is not working. cfset mydatetime= DateFormat(Now(), '-mm-dd') cfquery name=data datasource=mydata SELECT EXPIRATIONDATE FROM COUPONS WHERE EXPIRATIONDATE = cfqueryPARAM value = mydatetime CFSQLType = 'CF_SQL_VARCHAR' cfoutput#data.RecordCount#/cfoutput Thanks, barry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350224 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Cfquery dates help needed
WHERE EXPIRATIONDATE = cfqueryPARAM value = #mydatetime # CFSQLType = 'CF_SQL_VARCHAR' Sent from my Samsung Galaxy SII On Mar 4, 2012 7:54 AM, Barry Mcconaghey bmcconag...@gmail.com wrote: Hello. I'm trying to get this cfquery to match a database field EXPIRATIONDATE(date) 2012-3-04 and do a recordcount but it is not working. cfset mydatetime= DateFormat(Now(), '-mm-dd') cfquery name=data datasource=mydata SELECT EXPIRATIONDATE FROM COUPONS WHERE EXPIRATIONDATE = cfqueryPARAM value = mydatetime CFSQLType = 'CF_SQL_VARCHAR' cfoutput#data.RecordCount#/cfoutput Thanks, barry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350225 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Cfquery dates help needed
CFSQLType ='CF_SQL_VARCHAR' Also for date columns use a date/time type instead of varchar. If the column contains dates (only) you can use cf_sql_date, which truncates the time portion. WHERE EXPIRATIONDATE = cfqueryparam value = #now()# CFSQLType ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:350228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery and json
Hi I am new to coldfusion 9 and extjs 4 and like to know how to pass a cfquery to a mysql table, and then covert the data to json to populate a grid.. Would appreciate some help on this matter also if there are any tutorials for newbies. Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349369 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery and json
Start here and let us know if you have any trouble: http://www.cfscreencast.com/2007/12/6/retrieving-data-from-mysql-using-cfquery/ http://www.coldfusion-ria.com/Blog/index.cfm/2009/12/14/ExtJS-30-Editor-Grid-with-ColdFusion (I didn't see an ExtJS 4 example on Google. Perhaps 3 will work for you?) Tutorials for newbies: http://www.easycfm.com/ http://www.sitepoint.com/cold-fusion-tutorial/ http://www.tomkitta.com/guides/cf_101.cfm etc On Tue, Jan 10, 2012 at 4:57 AM, Tom Small t...@re-base.net wrote: Hi I am new to coldfusion 9 and extjs 4 and like to know how to pass a cfquery to a mysql table, and then covert the data to json to populate a grid.. Would appreciate some help on this matter also if there are any tutorials for newbies. Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349370 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery and json
Thanks for your assistance ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349371 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: adding custom variable to cfquery metadata
Why not just do this:
data = {};
data.query = somequery;
data.whatever = ice cream;
On Wed, Jul 20, 2011 at 7:40 AM, Richard White rich...@j7is.co.uk wrote:
Hi,
is there any way to add custom variables to cfquery metadata.
for example: i know i can add them as follows:
mt = getMetaData(query)
mt[1].newVar = value;
however, this query gets passed through various functions so instead of also
passing the metadata through, i would just like to call getMetaData(query) in
another function and it will also return the myVar variable that was
previously set
thanks
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346324
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
adding custom variable to cfquery metadata
Hi, is there any way to add custom variables to cfquery metadata. for example: i know i can add them as follows: mt = getMetaData(query) mt[1].newVar = value; however, this query gets passed through various functions so instead of also passing the metadata through, i would just like to call getMetaData(query) in another function and it will also return the myVar variable that was previously set thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346284 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQuery Select and single quotes in string
I'm doing a look up like this: cfquery datasource=#DSN# name=GetResults SELECT Formation, Play, BallCarrier, YardsGained, Result FROM PlaysCalled WHERE (((PlaysCalled.Formation)=#Form.Formation#) AND ((PlaysCalled.Play)=#Form.Play#) AND ((PlaysCalled.BallCarrier)=#Form.BallCarrier#)); /cfquery In some cases, the Ball Carrier's name has a single quote in it. In these cases, the search doesn't find them. So for instance, it finds Bob Jones but not D'Quan Black. Do I use cfqueryparam to resolve this? I'm using cfqueryparam when I insert the data into the database, but when I use the line: cfqueryparam value=#Trim(GetPlay.BallCarrier)# cfsqltype=CF_SQL_LONGVARCHAR it doesn't find anything. Thanks, Jim ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345144 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: CFQuery Select and single quotes in string
Yes, and if this is form input, you should be using cfqueryparam anyway. Please see the news for Sony's latest data hack, which was simple web-based SQL injection. cfsqltype will need to match column datatype in each case. cfquery datasource=#DSN# name=GetResults SELECT Formation, Play, BallCarrier, YardsGained, Result FROM PlaysCalled WHERE PlaysCalled.Formation = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#Form.Formation# / AND PlaysCalled.Play = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#Form.Play# / AND PlaysCalled.BallCarrier = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#Form.BallCarrier# / /cfquery From: Jim Brundle brundlefly...@gmail.com Sent: Friday, June 10, 2011 9:15 AM To: cf-talk cf-talk@houseoffusion.com Subject: CFQuery Select and single quotes in string I'm doing a look up like this: cfquery datasource=#DSN# name=GetResults SELECT Formation, Play, BallCarrier, YardsGained, Result FROM PlaysCalled WHERE (((PlaysCalled.Formation)=#Form.Formation#) AND ((PlaysCalled.Play)=#Form.Play#) AND ((PlaysCalled.BallCarrier)=#Form.BallCarrier#)); /cfquery In some cases, the Ball Carrier's name has a single quote in it. In these cases, the search doesn't find them. So for instance, it finds Bob Jones but not D'Quan Black. Do I use cfqueryparam to resolve this? I'm using cfqueryparam when I insert the data into the database, but when I use the line: cfqueryparam value=#Trim(GetPlay.BallCarrier)# cfsqltype=CF_SQL_LONGVARCHAR it doesn't find anything. Thanks, Jim ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345145 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQuery Select and single quotes in string
What DBMS are you using? MSSQL, MYSQL, Access (bleeackk!), other? MSSQL uses single quotes in the WHERE clause. However, if you use cfqueryparam for all of your where clause pieces, single quotes in your strings should pass through in the database request just fine. What is the data type of the BallCarrier column? Would cfsqltype=CF_SQL_VARCHAR be more appropriate? HTH, Carl On 6/10/2011 6:14 AM, Jim Brundle wrote: I'm doing a look up like this: cfquery datasource=#DSN# name=GetResults SELECT Formation, Play, BallCarrier, YardsGained, Result FROM PlaysCalled WHERE (((PlaysCalled.Formation)=#Form.Formation#) AND ((PlaysCalled.Play)=#Form.Play#) AND ((PlaysCalled.BallCarrier)=#Form.BallCarrier#)); /cfquery In some cases, the Ball Carrier's name has a single quote in it. In these cases, the search doesn't find them. So for instance, it finds Bob Jones but not D'Quan Black. Do I use cfqueryparam to resolve this? I'm using cfqueryparam when I insert the data into the database, but when I use the line: cfqueryparam value=#Trim(GetPlay.BallCarrier)# cfsqltype=CF_SQL_LONGVARCHAR it doesn't find anything. Thanks, Jim ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:345147 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery select question
Mark, there is an excellent SQL tutorial here: http://www.w3schools.com/sql/ If you figure out exactly how joins work, it will make writing SQL so much easier for you. Sub-selects like Greg used are also very useful. A few hours spent on this site will save you hours of struggle later. On Thu, May 26, 2011 at 11:18 AM, Mark Atkinson ma...@aocs.org wrote: Greg and John, Many thanks to you both. John's SQL produced exactly the same results as my tortuous attempt, and went a long way toward showing how to write better SQL. Greg, to broaden my SQL experience even further, I will have to take a little time to understand yours and get it to produce the same results, which I will do. Thanks again, Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344952 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery select question
Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344915 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery select question
SELECT DISTINCT table3.ID, table3.Company FROM table3 INNER JOIN table2 ON table3.ID = table2.t2_ID INNER JOIN table1 ON table2.ordernumber = table1.ordernumber WHERE (table1.productcode LIKE '%a certain string%') On Thu, May 26, 2011 at 11:41 AM, Mark Atkinson ma...@aocs.org wrote: Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344917 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery select question
If they all return 1 row use this.. select * from table3 t3 where t3.ID = ( select t2.ID from table2 t2 where t2.productcode = ( select t1.productcode from table1 t1 where t1.ordernumber = [whatever] ) ) If they return multiple rows you can change the = to IN () And if they return many rows, may want to use WHERE EXISTS.. Let us know On Thu, May 26, 2011 at 11:41 AM, Mark Atkinson ma...@aocs.org wrote: Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344918 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery select question
Greg and John, Many thanks to you both. John's SQL produced exactly the same results as my tortuous attempt, and went a long way toward showing how to write better SQL. Greg, to broaden my SQL experience even further, I will have to take a little time to understand yours and get it to produce the same results, which I will do. Thanks again, Mark -Original Message- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Thursday, May 26, 2011 11:48 AM To: cf-talk Subject: Re: cfquery select question If they all return 1 row use this.. select * from table3 t3 where t3.ID = ( select t2.ID from table2 t2 where t2.productcode = ( select t1.productcode from table1 t1 where t1.ordernumber = [whatever] ) ) If they return multiple rows you can change the = to IN () And if they return many rows, may want to use WHERE EXISTS.. Let us know On Thu, May 26, 2011 at 11:41 AM, Mark Atkinson ma...@aocs.org wrote: Hallo all, Forgive my SQL injection into this list - perhaps an answer to my question might also prove useful to others. I'm using three tables. There is no relationship between table1 and table3. I first query table1 for ordernumber and productcode, based on the productcode containing a certain string. Outputting the results, I query table2 for ordernumber and t2_ID based on table2.ordernumber matching table1.ordernumber. Looping those results, I query table3 for ID and Company based on table2.t2_ID. This, rather painfully I do admit, finds the table3.Company(ies) that has(have) purchased table1.productcode. Have Googled and read about how a single select with JOIN and/or UNION would achieve these results, but they also mention how I should essentially start at the end and work my way backwards, so to speak. Can't quite get my head around this. TIA for any advice. Mark ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344931 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY update Firefox wierdness
I checked and it's turned off. This is happening from multiple computers with Firefox. Any other thoughts? -Original Message- From: Matt Quackenbush Sent: Tuesday, April 19, 2011 12:20 AM To: cf-talk Subject: Re: CFQUERY update Firefox wierdness Wild guess: Firebug is turned on and is adding a second click to the flow. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343840 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY update Firefox wierdness
On 19/04/2011 8:46 PM, Dan Blickensderfer wrote: I checked and it's turned off. This is happening from multiple computers with Firefox. Any other thoughts? in that case turn it on and look at what the browser is sending... -- Yours, Kym Kovan mbcomms.net.au ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343842 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY update Firefox wierdness
Kym, I figured out what was causing the page to load twice. Within my page I had an abandon background-image:url(); within one of my style tags. Once I removed it everything is working correctly with Firefox on all machines. I guess Firefox tries to fetch the url() and since it's not referring to a url it reloads the same page. This was driving me crazy. Dan -Original Message- From: Kym Kovan Sent: Tuesday, April 19, 2011 7:23 AM To: cf-talk Subject: Re: CFQUERY update Firefox wierdness On 19/04/2011 8:46 PM, Dan Blickensderfer wrote: I checked and it's turned off. This is happening from multiple computers with Firefox. Any other thoughts? in that case turn it on and look at what the browser is sending... -- Yours, Kym Kovan mbcomms.net.au ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343858 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQUERY update Firefox wierdness
Hi Everyone,
I have a really strange issue that I can not figure out how to fix it. I
have a simple shopping cart and everything works exactly the way it should
in ie7 ie8 but firefox not so much.
This is the issue. When customer clicks on the item to add to cart it first
checks to see if the item is already in the cart. If not, insert new
record. If found, update the record and add 1 to the qty. Within IE this
works just as it should, Firefox runs the update query regardless of new
insert or not. example. item ABC new to cart with qty 1 if I look at the
database table I see the qty is 2. If the item is already in database with
qty 1 and item is added to cart the qty is now 3. I've turned on debug and
looked at all queries ran and the the correct queries do show.
It's like there is another update query someplace but there isn't. I tested
this by removing the query named updatecart and tried the process again
and new records do get added and the qty is correct with 1 not 2. With it
being removed and item is updated to cart. it doesn't get updated like it
should be.
This is very very weird!
The code that I'm having strange is the following. I just copied the bit
that I need to show.
Anybody seen this before?
Thanks,
Dan Blickensderfer
cfquery name=lookupcart datasource=#application.datasource#
username=#application.username# password=#application.password#
select rec_id
from order_items
where rec_status = 'T' and session_uuid = cfqueryparam
cfsqltype=cf_sql_varchar maxlength=36 value=#session.cartid# and
products_rec_id = cfqueryparam cfsqltype=cf_sql_integer
value=#variables.recid#
/cfquery
cfif lookupcart.recordcount is 0
cfquery name=addcart datasource=#application.datasource#
username=#application.username# password=#application.password#
insert into order_items (rec_status, session_uuid, products_rec_id,
product_name, qty, orders_rec_id, ip_address)
values ('T', cfqueryparam cfsqltype=cf_sql_varchar maxlength=36
value=#session.cartid#,
cfqueryparam cfsqltype=cf_sql_integer value=#variables.recid#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#getproduct.product_name# maxlength=150,
cfqueryparam cfsqltype=cf_sql_integer value=1,
cfqueryparam cfsqltype=cf_sql_integer
value=#lookuporder.rec_id#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#trim(cgi.REMOTE_ADDR)# maxlength=17)
/cfquery
cfelse
cfquery name=updatecart datasource=#application.datasource#
username=#application.username# password=#application.password#
update order_items
set qty = qty + 1
where rec_status = 'T' and rec_id = cfqueryparam
cfsqltype=cf_sql_integer value=#lookupcart.rec_id#
/cfquery
/cfif
Here is the debug info
lookupcart (Datasource=XYX, Time=0ms, Records=0) in
templates\includes\cart-add.cfm @ 18:50:44.044
select *
from order_items
where rec_status = 'T' and session_uuid = ? and
products_rec_id = ? Query
Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 69B9132F-943D-2A28-E56878FCFEA4A5B6
Parameter #2(cf_sql_integer) = 17
addcart (Datasource=XYX, Time=0ms, Records=1) in
templates\includes\cart-add.cfm @ 18:50:44.044
insert into order_items (rec_status,
session_uuid, products_rec_id,
product_name, qty, orders_rec_id, ip_address)
values ('T', ?,
?,
?,
?,
?,
?)
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 69B9132F-943D-2A28-E56878FCFEA4A5B6
Parameter #2(cf_sql_integer) = 17
Parameter #3(cf_sql_varchar) = Green, Yellow Orange
Parameter #4(cf_sql_integer) = 1
Parameter #5(cf_sql_integer) = 5014
Parameter #6(cf_sql_varchar) = 192.168.10.159
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343833
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY update Firefox wierdness
Hi
I never seen that.. but the first thing I would look at is to set
up a way to see how many times that action page is run. Perhaps insert a
GetTickCount counter into a log file when the action page is
run.. First look to see how it works in IE, then try firefox.
At 06:55 PM 4/18/2011, you wrote:
Hi Everyone,
I have a really strange issue that I can not figure out how to fix it. I
have a simple shopping cart and everything works exactly the way it should
in ie7 ie8 but firefox not so much.
This is the issue. When customer clicks on the item to add to cart it first
checks to see if the item is already in the cart. If not, insert new
record. If found, update the record and add 1 to the qty. Within IE this
works just as it should, Firefox runs the update query regardless of new
insert or not. example. item ABC new to cart with qty 1 if I look at the
database table I see the qty is 2. If the item is already in database with
qty 1 and item is added to cart the qty is now 3. I've turned on debug and
looked at all queries ran and the the correct queries do show.
It's like there is another update query someplace but there isn't. I tested
this by removing the query named updatecart and tried the process again
and new records do get added and the qty is correct with 1 not 2. With it
being removed and item is updated to cart. it doesn't get updated like it
should be.
This is very very weird!
The code that I'm having strange is the following. I just copied the bit
that I need to show.
Anybody seen this before?
Thanks,
Dan Blickensderfer
cfquery name=lookupcart datasource=#application.datasource#
username=#application.username# password=#application.password#
select rec_id
from order_items
where rec_status = 'T' and session_uuid = cfqueryparam
cfsqltype=cf_sql_varchar maxlength=36 value=#session.cartid# and
products_rec_id = cfqueryparam cfsqltype=cf_sql_integer
value=#variables.recid#
/cfquery
cfif lookupcart.recordcount is 0
cfquery name=addcart datasource=#application.datasource#
username=#application.username# password=#application.password#
insert into order_items (rec_status, session_uuid, products_rec_id,
product_name, qty, orders_rec_id, ip_address)
values ('T', cfqueryparam cfsqltype=cf_sql_varchar maxlength=36
value=#session.cartid#,
cfqueryparam cfsqltype=cf_sql_integer value=#variables.recid#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#getproduct.product_name# maxlength=150,
cfqueryparam cfsqltype=cf_sql_integer value=1,
cfqueryparam cfsqltype=cf_sql_integer
value=#lookuporder.rec_id#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#trim(cgi.REMOTE_ADDR)# maxlength=17)
/cfquery
cfelse
cfquery name=updatecart datasource=#application.datasource#
username=#application.username# password=#application.password#
update order_items
set qty = qty + 1
where rec_status = 'T' and rec_id = cfqueryparam
cfsqltype=cf_sql_integer value=#lookupcart.rec_id#
/cfquery
/cfif
Here is the debug info
lookupcart (Datasource=XYX, Time=0ms, Records=0) in
templates\includes\cart-add.cfm @ 18:50:44.044
select *
from order_items
where rec_status = 'T' and session_uuid = ?
and products_rec_id = ? Query
Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 69B9132F-943D-2A28-E56878FCFEA4A5B6
Parameter #2(cf_sql_integer) = 17
addcart (Datasource=XYX, Time=0ms, Records=1) in
templates\includes\cart-add.cfm @ 18:50:44.044
insert into order_items
(rec_status, session_uuid, products_rec_id,
product_name, qty, orders_rec_id, ip_address)
values ('T', ?,
?,
?,
?,
?,
?)
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 69B9132F-943D-2A28-E56878FCFEA4A5B6
Parameter #2(cf_sql_integer) = 17
Parameter #3(cf_sql_varchar) = Green, Yellow Orange
Parameter #4(cf_sql_integer) = 1
Parameter #5(cf_sql_integer) = 5014
Parameter #6(cf_sql_varchar) = 192.168.10.159
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343834
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY update Firefox wierdness
cfquery tag has absolutely nothing to do with any browser - it is
executed by cf on the server side.
i would check your page's html and javascript instead - that's where
browser differences can come into play.
how do you submit your form to add item to cart - ajax or regular form
submit?
how do you display updated cart info on the page - ajax or page refresh?
is your html correctly formed and does it validate properly? ff is
pickier than ie when it comes to malformed html and this may cause it to
submit other data to your cart update script than what you expect.
use firebug to check what form data really gets submitted in ff.
Azadi
On 19/04/2011 06:55 , Dan Blickensderfer wrote:
Hi Everyone,
I have a really strange issue that I can not figure out how to fix it. I
have a simple shopping cart and everything works exactly the way it should
in ie7 ie8 but firefox not so much.
This is the issue. When customer clicks on the item to add to cart it first
checks to see if the item is already in the cart. If not, insert new
record. If found, update the record and add 1 to the qty. Within IE this
works just as it should, Firefox runs the update query regardless of new
insert or not. example. item ABC new to cart with qty 1 if I look at the
database table I see the qty is 2. If the item is already in database with
qty 1 and item is added to cart the qty is now 3. I've turned on debug and
looked at all queries ran and the the correct queries do show.
It's like there is another update query someplace but there isn't. I tested
this by removing the query named updatecart and tried the process again
and new records do get added and the qty is correct with 1 not 2. With it
being removed and item is updated to cart. it doesn't get updated like it
should be.
This is very very weird!
The code that I'm having strange is the following. I just copied the bit
that I need to show.
Anybody seen this before?
Thanks,
Dan Blickensderfer
cfquery name=lookupcart datasource=#application.datasource#
username=#application.username# password=#application.password#
select rec_id
from order_items
where rec_status = 'T' and session_uuid =cfqueryparam
cfsqltype=cf_sql_varchar maxlength=36 value=#session.cartid# and
products_rec_id =cfqueryparam cfsqltype=cf_sql_integer
value=#variables.recid#
/cfquery
cfif lookupcart.recordcount is 0
cfquery name=addcart datasource=#application.datasource#
username=#application.username# password=#application.password#
insert into order_items (rec_status, session_uuid, products_rec_id,
product_name, qty, orders_rec_id, ip_address)
values ('T',cfqueryparam cfsqltype=cf_sql_varchar maxlength=36
value=#session.cartid#,
cfqueryparam cfsqltype=cf_sql_integer value=#variables.recid#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#getproduct.product_name# maxlength=150,
cfqueryparam cfsqltype=cf_sql_integer value=1,
cfqueryparam cfsqltype=cf_sql_integer
value=#lookuporder.rec_id#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#trim(cgi.REMOTE_ADDR)# maxlength=17)
/cfquery
cfelse
cfquery name=updatecart datasource=#application.datasource#
username=#application.username# password=#application.password#
update order_items
set qty = qty + 1
where rec_status = 'T' and rec_id =cfqueryparam
cfsqltype=cf_sql_integer value=#lookupcart.rec_id#
/cfquery
/cfif
Here is the debug info
lookupcart (Datasource=XYX, Time=0ms, Records=0) in
templates\includes\cart-add.cfm @ 18:50:44.044
select *
from order_items
where rec_status = 'T' and session_uuid = ? and
products_rec_id = ? Query
Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 69B9132F-943D-2A28-E56878FCFEA4A5B6
Parameter #2(cf_sql_integer) = 17
addcart (Datasource=XYX, Time=0ms, Records=1) in
templates\includes\cart-add.cfm @ 18:50:44.044
insert into order_items (rec_status,
session_uuid, products_rec_id,
product_name, qty, orders_rec_id, ip_address)
values ('T', ?,
?,
?,
?,
?,
?)
Query Parameter Value(s) -
Parameter #1(cf_sql_varchar) = 69B9132F-943D-2A28-E56878FCFEA4A5B6
Parameter #2(cf_sql_integer) = 17
Parameter #3(cf_sql_varchar) = Green, Yellow Orange
Parameter #4(cf_sql_integer) = 1
Parameter #5(cf_sql_integer) = 5014
Parameter #6(cf_sql_varchar) = 192.168.10.159
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe
Re: CFQUERY update Firefox wierdness
Wild guess: Firebug is turned on and is adding a second click to the flow. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343836 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
ColdFusion cfquery and Oracle Temporary Tables
I am running ColdFusion 8 and Oracle 10g. I am running three stored procedures to insert data into three separate temporary tables. From there, I run a union statement to against the three temporary tables and output out the results. However, I am running into an unexpected error: Stored Procedure A writes to Temporary Table A, Stored Procedure B writes to Temporary Table B, Stored Procedure C writes to Temporary Table C. Query 1 performs a select union on TTA, TTB, and TTC. Depending on some logic, Stored Procedure A, B, or C may or may not run and therefore, TTA, TTB, or TTC SHOULD be empty. However, it is not. I am sure that these tables are temporary and are created as a global temporary table. Any help is greatly appreciated. Please let me know if more information needed. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343111 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
The behavior change might be due to updated drivers, which is the most common source of modified database behavior. You can probably fix this either in CF, using something like NumberFormat, or cfqueryparam, or using casting in SQL Server. If this is a lot of work for you, it might be worth it to contact Adobe tech support to confirm the behavior change. One alternative is to pick a different JDBC driver than the one that comes with CF. Microsoft has their own free JDBC driver that you can use fairly easily. The Microsoft-provider JDBC driver behaves differently. I have had to use the Microsoft one in certain situations where the one that comes bundled with CF did not work properly. Check if any of the three columns present in your SQL are float, real, or money data types. If you find one of these data types in your database you can change it to a numeric as another way to fix the problem. -Mike Chabot On Wed, Feb 16, 2011 at 6:44 PM, James Skemp jsk...@wisbar.org wrote: Looks like you might need to revise the procedure to CAST the calculated value back to Numeric(10,2) before returning it? Carl Yeah, which is what we're doing for this one to resolve the issue. But since this is a legacy, third-party, application ... it worries me the number of places this may occur. And of course, as we all know, there's usually one place that gets missed in these types of situations ... :D I was hoping it was a configuration option that we missed, but ... I don't suppose someone with a similar environment could test against CF 9, could they? Thanks Carl! ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342376 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery returning 0 in scientific notation in CF 8 but not CF 7
Greetings. In one of our systems we're upgrading from ColdFusion 7 to CF 8. While testing a piece of functionality we were getting 0 returned as 0E-8 for a query using a stored procedure. If the value is greater than 0, there's no issue. Updating the procedure to cast the returned value as a numeric resolved the issue, but since this functionality is working fine against CF 7 ... We're using MS SQL, and the data source settings in ColdFusion Administrator are consistent between 7 and 8. Google and searching these forums isn't helping much, other than suggesting it's coming out of MSSQL, but ... does anyone have any additional background on why we're seeing this? Do we need to evaluate each of our procedures/calls to verify that values are coming back correctly and cast if not, or ...? Thanks! ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
What is the data type of the field in SQL Server? If you run the query in Management Studio, what do you see for that value? How are you outputting the value in CF to see the 0E-8 number? What tag are you using to call the stored procedure in ColdFusion? Which version of MS SQL Server are you using? -Mike Chabot On Wed, Feb 16, 2011 at 11:20 AM, James Skemp jsk...@wisbar.org wrote: Greetings. In one of our systems we're upgrading from ColdFusion 7 to CF 8. While testing a piece of functionality we were getting 0 returned as 0E-8 for a query using a stored procedure. If the value is greater than 0, there's no issue. Updating the procedure to cast the returned value as a numeric resolved the issue, but since this functionality is working fine against CF 7 ... We're using MS SQL, and the data source settings in ColdFusion Administrator are consistent between 7 and 8. Google and searching these forums isn't helping much, other than suggesting it's coming out of MSSQL, but ... does anyone have any additional background on why we're seeing this? Do we need to evaluate each of our procedures/calls to verify that values are coming back correctly and cast if not, or ...? Thanks! ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Thanks for the clarification questions, Mike. What is the data type of the field in SQL Server? numeric(10,2) If you run the query in Management Studio, what do you see for that value? 0.00 How are you outputting the value in CF to see the 0E-8 number? In a tossed error it was displayed in the stack trace and a cfdump of the returned information from the initial cfquery call against the sp showed it there as well. When it works, it's stored to a variable before being passed to SQL via another query. What tag are you using to call the stored procedure in ColdFusion? cfquery Which version of MS SQL Server are you using? 64 bit, 2005 Standard edition SP3 ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342337 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
It occurred to me that I should first ask if this issue is causing any problems, or if it is only an unexpected way to format a number? For example 0.00 = 0E-8. They are both the same number regardless of how they are displayed. For clarification, which number has the unexpected formatting, the value pulled directly out of a database table and placed in the cfquery record set, the variable that is being assigned the value, or both? -Mike Chabot On Wed, Feb 16, 2011 at 12:29 PM, James Skemp jsk...@wisbar.org wrote: Thanks for the clarification questions, Mike. What is the data type of the field in SQL Server? numeric(10,2) If you run the query in Management Studio, what do you see for that value? 0.00 How are you outputting the value in CF to see the 0E-8 number? In a tossed error it was displayed in the stack trace and a cfdump of the returned information from the initial cfquery call against the sp showed it there as well. When it works, it's stored to a variable before being passed to SQL via another query. What tag are you using to call the stored procedure in ColdFusion? cfquery Which version of MS SQL Server are you using? 64 bit, 2005 Standard edition SP3 ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342339 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
It occurred to me that I should first ask if this issue is causing any problems, or if it is only an unexpected way to format a number? For example 0.00 = 0E-8. They are both the same number regardless of how they are displayed. Unfortunately, it is causing an issue in this particular instance (and is in fact why we had a stack trace to see that 0E-8 was being returned) :) It's legacy code, so with some work we could certainly resolve the issue, but, as a legacy *third-party* solution, my concern is it's going to cause issues in other parts of the system. Assuming this is expected behavior in CF8/we haven't flipped the appropriate 'switch.' For clarification, which number has the unexpected formatting, the value pulled directly out of a database table and placed in the cfquery record set, the variable that is being assigned the value, or both? Probably the former - ColdFusion's interpretation of the value from SQL / the value in the cfquery record set, since the resolution in this particular case is to cast the returned value to a particular type in the procedure. But, another way to resolve the issue is to check the formatting when setting the variable, so I suppose one could argue both. The immediate problem is because of the latter, but the root cause is the former. In short, 0E-8 is populated in the proc's cfquery result set, a variable is being set with 0E-8, and another query is failing because it doesn't care for 0E-8 being passed. Hopefully that last bit didn't muddy the waters ... :) ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342341 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Why would a SQL cast fix the issue? Casting a numeric(10,2) to a numeric(10,2) would not solve anything, which means that the value you are casting to numeric must be of a different data type, likely float or varchar. Floating point numbers often display in scientific notation. The numeric value is possibly being automatically converted into a floating point number by its use in a math function. It might help to see the SQL with the cast function that is fixing the issue. SELECT numeric_col FROM table should not require any further casting and you would not see the problem you are seeing. However, if you do something like this SELECT '000' + numeric_col + 1.2E-20 / 1.234 + 'cat' - 'dog' as newval FROM table then you are no longer dealing with a numeric, even though the source number might have started out as one. I'm still not clear on what the problem is. As a reminder, try to create a simplified example that isolates the problem being seen, away from any surrounding code. During the exercise of creating the simplified example, testing each increasingly complex iteration of the code, the source of the error will likely be revealed. If you were hoping for a quick answer to your question, I don't have one. Perhaps someone else has seen this issue when upgrading CF. -Mike Chabot On Wed, Feb 16, 2011 at 1:43 PM, James Skemp jsk...@wisbar.org wrote: It occurred to me that I should first ask if this issue is causing any problems, or if it is only an unexpected way to format a number? For example 0.00 = 0E-8. They are both the same number regardless of how they are displayed. Unfortunately, it is causing an issue in this particular instance (and is in fact why we had a stack trace to see that 0E-8 was being returned) :) It's legacy code, so with some work we could certainly resolve the issue, but, as a legacy *third-party* solution, my concern is it's going to cause issues in other parts of the system. Assuming this is expected behavior in CF8/we haven't flipped the appropriate 'switch.' For clarification, which number has the unexpected formatting, the value pulled directly out of a database table and placed in the cfquery record set, the variable that is being assigned the value, or both? Probably the former - ColdFusion's interpretation of the value from SQL / the value in the cfquery record set, since the resolution in this particular case is to cast the returned value to a particular type in the procedure. But, another way to resolve the issue is to check the formatting when setting the variable, so I suppose one could argue both. The immediate problem is because of the latter, but the root cause is the former. In short, 0E-8 is populated in the proc's cfquery result set, a variable is being set with 0E-8, and another query is failing because it doesn't care for 0E-8 being passed. Hopefully that last bit didn't muddy the waters ... :) ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342348 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Thanks Mike.
Opened up the procedure and gave it a look. It looks like there's some
multiplication and division going on, which is resulting in a change. Relevant
part for one of the returned values:
MemberPrice =
(case when qp.productid is null then p.MemberPrice
else
case when qp.MemberPrice is null then
p.MemberPrice*((100-qp.memberdiscountpercent)/100)
else qp.MemberPrice
end
end)
Running this query on ColdFusion 7.('current') against MS SQL returns (again,
cfdump of the cfquery, and the same thing as SQL Studio returns):
0.
On CF 8, we instead get:
0E-8
So, updated CF 8 SQL Driver perhaps? Either way, unless there's an
Administrator change, this does mean we'll have to evaluate code since, right
or wrong, CF 7 doesn't make this change.
Thanks!
~James
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342349
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Looks like you might need to revise the procedure to CAST the calculated
value back to Numeric(10,2) before returning it?
Carl
On 2/16/2011 2:30 PM, James Skemp wrote:
Thanks Mike.
Opened up the procedure and gave it a look. It looks like there's some
multiplication and division going on, which is resulting in a change.
Relevant part for one of the returned values:
MemberPrice =
(case when qp.productid is null then p.MemberPrice
else
case when qp.MemberPrice is null then
p.MemberPrice*((100-qp.memberdiscountpercent)/100)
else qp.MemberPrice
end
end)
Running this query on ColdFusion 7.('current') against MS SQL returns (again,
cfdump of the cfquery, and the same thing as SQL Studio returns):
0.
On CF 8, we instead get:
0E-8
So, updated CF 8 SQL Driver perhaps? Either way, unless there's an
Administrator change, this does mean we'll have to evaluate code since, right
or wrong, CF 7 doesn't make this change.
Thanks!
~James
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342350
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery returning 0 in scientific notation in CF 8 but not CF 7
Looks like you might need to revise the procedure to CAST the calculated value back to Numeric(10,2) before returning it? Carl Yeah, which is what we're doing for this one to resolve the issue. But since this is a legacy, third-party, application ... it worries me the number of places this may occur. And of course, as we all know, there's usually one place that gets missed in these types of situations ... :D I was hoping it was a configuration option that we missed, but ... I don't suppose someone with a similar environment could test against CF 9, could they? Thanks Carl! ~James ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342354 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Getting information from a cfquery/cfloop to display in a table or div
Hello, I'm trying to get information from our database to display in horizontal form (table or div). I'm able to pull information from our database with the following code. Everytime I try to incorporate a table the same information will display in on each row; different information will not display in each cell. Any ideas? cfquery name=member datasource=ASAGOV SELECT fname, lname, Starship, Rank FROM Star_Trek where rank 'Captain' AND rank 'Science Officer' /cfquery cfloop query=membercfoutput #fname# #lname#, #starship# /cfoutput/cfloop ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341985 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Getting information from a cfquery/cfloop to display in a table or div
Try leaving out the cfoutput/cfoutput in the your cfloop. Rick -Original Message- From: Michelle Dupray [mailto:mdup...@gmail.com] Sent: Tuesday, February 08, 2011 12:24 PM To: cf-talk Subject: Getting information from a cfquery/cfloop to display in a table or div Hello, I'm trying to get information from our database to display in horizontal form (table or div). I'm able to pull information from our database with the following code. Everytime I try to incorporate a table the same information will display in on each row; different information will not display in each cell. Any ideas? cfquery name=member datasource=ASAGOV SELECT fname, lname, Starship, Rank FROM Star_Trek where rank 'Captain' AND rank 'Science Officer' /cfquery cfloop query=membercfoutput #fname# #lname#, #starship# /cfoutput/cfloop ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341988 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Getting information from a cfquery/cfloop to display in a table or div
Michelle, Try the following: table trtdFirst Name/tdtdLast Name/tdtdStarship/td/tr cfoutput query=member trtd#fname#/tdtd#lname#/tdtd#starship#/td/tr /cfoutput /table Cheers, Rob On Tue, Feb 8, 2011 at 1:01 PM, Rick Faircloth r...@whitestonemedia.comwrote: Try leaving out the cfoutput/cfoutput in the your cfloop. Rick -Original Message- From: Michelle Dupray [mailto:mdup...@gmail.com] Sent: Tuesday, February 08, 2011 12:24 PM To: cf-talk Subject: Getting information from a cfquery/cfloop to display in a table or div Hello, I'm trying to get information from our database to display in horizontal form (table or div). I'm able to pull information from our database with the following code. Everytime I try to incorporate a table the same information will display in on each row; different information will not display in each cell. Any ideas? cfquery name=member datasource=ASAGOV SELECT fname, lname, Starship, Rank FROM Star_Trek where rank 'Captain' AND rank 'Science Officer' /cfquery cfloop query=membercfoutput #fname# #lname#, #starship# /cfoutput/cfloop ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341990 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfquery mysql timer
hi, we have a table that has 15000 rows. When we insert data into it, each row is taking approx 300ms to insert by using the cfquery tag. If we paste the insert code directly into mysql its taking only 0.09 ms. We thought this may be due to cfquery having to connect to mysql, or possibly due to the cfqueryparams. so in the cf administrator we set allowMultiQueries=true in the conncetion string and pasted 5 query strings into one cfquery tag, each with no cfqueryparam tags (shown below). The query took 500ms, yet if we run the same 5 statements in mysql, they took 0.45ms Would anyone have any idea why this would happen? Thanks INSERT INTO questions_1_100(primaryid,q_1)VALUES (15037,1) ON DUPLICATE KEY UPDATE q_1 = 1; INSERT INTO questions_1_100(primaryid,q_4)VALUES (15037,93) ON DUPLICATE KEY UPDATE q_4 = 93; INSERT INTO questions_1_100(primaryid,q_1)VALUES (15038,1) ON DUPLICATE KEY UPDATE q_1 = 1; INSERT INTO questions_1_100(primaryid,q_4)VALUES (15038,93) ON DUPLICATE KEY UPDATE q_4 = 93; INSERT INTO questions_701_800(primaryid,q_777)VALUES (15038,264) ON DUPLICATE KEY UPDATE q_777 = 264; ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340616 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQuery to mdb
But why does it need to be a full Access database? Providing a delimited file users could import is simpler and more flexible. As it could be imported into both Excel and Access. I asked the same question... It's political and we's IT people don't not know what were talkin bout... One of the groups we are providing the data to developed their system with an old Access Database... Not only do I have to provide it in Access I have to make sure the each table has the fields in the same order, the same data types and even if the column name is in CAPS I have to do so to... For the record I took over the project... Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340506 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQuery to mdb
Thanks! Looks like I am kind of on the right track... I will read up on this link... Rick -Original Message- From: Mark A. Kruger [mailto:mkru...@cfwebtools.com] Sent: Wednesday, January 05, 2011 9:23 PM To: cf-talk Subject: RE: CFQuery to mdb You can seed a database with the schema then access it through passthrough file syntax... .a single prepared temp DB could do the trick. Basically you have a dummy access DSN setup, then you copy your prepped access file to a temp location, load it with data using the passthrough method, and then allow the user to download it (I usually zip it first). Here's a link to a post on the passthrough method. http://www.coldfusionmuse.com/index.cfm/2005/6/3/dsn_cfmx While I agree with you about a CSV file in many cases, Leigh, sometimes this is useful for exactly the reasons you are suggesting - that extra access stuff allows you to create form interfaces, premade queries etc - enhancing off line data mining for the user. -mark Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 Skype: markakruger www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Wednesday, January 05, 2011 7:17 PM To: cf-talk Subject: Re: CFQuery to mdb just seems like more than I need to do. I do not think so. It is not like exporting html/excel. An Access database consists of more than just the data itself (system tables, etcetera). So I am not sure there is a simpler way. Maybe using ms sql's export tools .. But why does it need to be a full Access database? Providing a delimited file users could import is simpler and more flexible. As it could be imported into both Excel and Access. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340507 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQuery to mdb
Rick Eidson wrote: It's political and we's IT people don't not know what were talkin bout... Oh. One of those deals. (How familiar ;-) Well it looks like Mark has you on the right path. Mark wrote: While I agree with you about a CSV file in many cases, Leigh, sometimes this is useful for exactly the reasons you are suggesting - that extra access stuff allows you to create form interfaces, premade queries etc - enhancing off line data mining for the user. True enough. I just figured I would ask, in case a simpler method would meet the requirements. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340514 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CFQuery to mdb
I want to output the results to a MSAccess database file. I can output the results to a xls file pretty easily but I need to create a database and save the results to a table. I thought about creating a temp blank database and then creating the table, writing the data to it then copy the database down, deleting the database and writing a blank one just seems like more than I need to do. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340489 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQuery to mdb
just seems like more than I need to do. I do not think so. It is not like exporting html/excel. An Access database consists of more than just the data itself (system tables, etcetera). So I am not sure there is a simpler way. Maybe using ms sql's export tools .. But why does it need to be a full Access database? Providing a delimited file users could import is simpler and more flexible. As it could be imported into both Excel and Access. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQuery to mdb
You can seed a database with the schema then access it through passthrough file syntax... .a single prepared temp DB could do the trick. Basically you have a dummy access DSN setup, then you copy your prepped access file to a temp location, load it with data using the passthrough method, and then allow the user to download it (I usually zip it first). Here's a link to a post on the passthrough method. http://www.coldfusionmuse.com/index.cfm/2005/6/3/dsn_cfmx While I agree with you about a CSV file in many cases, Leigh, sometimes this is useful for exactly the reasons you are suggesting - that extra access stuff allows you to create form interfaces, premade queries etc - enhancing off line data mining for the user. -mark Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 Skype: markakruger www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Wednesday, January 05, 2011 7:17 PM To: cf-talk Subject: Re: CFQuery to mdb just seems like more than I need to do. I do not think so. It is not like exporting html/excel. An Access database consists of more than just the data itself (system tables, etcetera). So I am not sure there is a simpler way. Maybe using ms sql's export tools .. But why does it need to be a full Access database? Providing a delimited file users could import is simpler and more flexible. As it could be imported into both Excel and Access. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340494 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
thanks, this solution worked great and is now processing at a steady 2 seconds Hey, maybe try something like this? I haven't tested this, but it should theoretically be faster cfset sqlstring = / cfset questionIDLen = arrayLen(questionIDArray)!---put this in a variable so it doesn't re-evaluate 2200 times--- cfset questionIDCheck = 0 cfloop index=i from=2 to=#questionIDLen# !---remove if statement so it doesn't evaluate 2200 times either. --- cfset questionIDCheck = isNumeric(questionIDArray[i] parentQuestionIDArray[i] parentLevelArray[i])!---since all your params were checking for a numeric value, lump together and be sure they are all numeric. probably could use a regEx or other cffunctions to accomplish the same thing if this does not fit the bill--- cfif questionIDCheck!---only add to sql string if it passes the numeric check. --- cfset sqlstring = '#sqlstring#,(#questionIDArray[i]#, #parentQuestionIDArray[i]#,#parentLevelArray[i]#)' /cfif /cfloop !---run query. manually add first line since we didn't loop over it. --- cfquery name=insertData datasource=dbname INSERT INTO parentquestions VALUES (cfqueryparam value=#questionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentQuestionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentLevelArray[1]# cfsqltype=cf_sql_bigint maxlength=20) #sqlstring# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339108 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
thanks for providing the details as to what is happening behind the scenes On Wed, Nov 10, 2010 at 12:40 AM, Richard White wrote: [query with 6600 cfqueryparams taking 9 seconds vs. 1 for plain SQL] In understand that adding the cfqueryparam is adding approx 6600 validations, however would it really be the cause of slowing this query down so much? cfqueryparam is not adding 6600 validations. Each use of cfqueryparam is causing one variable to be set on the database side. So without cfqueryparam you are sending 1 SQL statement to the JDBC driver, with cfqueryparam you are first sending a prepare command with the 'structure' of the query, then the command to create an instance of it, then 6600 commands to set the 6600 parameters, then the command to execute it. I am not familiar with the wire protocol for MySQL and can not look into the propietary JDBC driver Adobe uses for MySQL, but I would not be surprised if this were implemented as a synchronous protocol and there is significant roundtripping between the database server and ColdFusion. You are testing the pathological worst case scenario for cfqueryparam. In addition to the overhead from specifiying the variables in separate statements, you add the overhead for preparing the query and caching the execution plan, and then you only execute it once. But anyway, do you really care? If this is part of a scheduled job that takes a few hours anyway ... Jochem -- Jochem van Dieten http://jochem.vandieten. net/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339109 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
transferring data from one app to another Hi, I am running the following cfquery which inserts a large amount of rows into a table (approx 2200) Just out of curiosity, where does your insert data originate from? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339110 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
transferring data from one app to another I'd just use Navicat to transfer the data. No CF needed. Quick and easy. Will ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339115 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
nice link, thanks will! transferring data from one app to another I'd just use Navicat to transfer the data. No CF needed. Quick and easy. Will ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339128 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Strange issue with cfquery
Hello,
I am running CF 8 on an Oracle database. I'm trying to pass in a SQL script as
a string and keep getting the error: ORA-00907: missing right parenthesis.
This is what I'm trying to do:
cfset sendMethod = createObject(component,components.sendMethods)
cfset sendMethod.init(dsn = request.selecteddns)
cfset sql=INSERT INTO nkonye_associated_person /
cfset sql = sql chr(10) chr(13)
(ADDED_DATE,ASSOCIATED_PERSON_ID,IS_REMOVED,PATIENT_ID,PROFILE_ID,ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,ADDRESS_LINE4,ADDRESS_LINE5,ASSOCIATED_RELATIONSHIP_ID,ASSOCIATED_RELATIONSHIP_OTHER,DAYS_ATTENDED,EMAIL,FIRST_LANGUAGE_ID,FORENAME,FREQUENCY_OF_CARE,INTERPRETER_REQUIRED_ID,IS_KEYHOLDER,MODIFIED_DATE,ORGANISATION_NAME,OTHER_COMMUNICATION_ISSUE,POSTCODE,PREFERRED_LANGUAGE_ID,PREFERRED_METHOD_ID,SURNAME)
/
cfset sql = sql chr(10) chr(13) VALUES (to_date('2009-10-07
13:07:28','-mm-dd hh24:mi:ss'),8,0,17,142,'122 Cleland
hospital',null,null,null,null,1014,null,null,null,null,'Borlotti',null,null,null,null,null,null,null,null,null,'Bean')
/
cfset sendMethod.doRunScript(#sql#) /
The doRunScript method is as follows:
cffunction name=doRunScript access=public
cfargument name=sql type=string required=yes
cfquery datasource=#request.selecteddns#
#sql#
/cfquery
/cffunction
Is there a workaround for this?
Thanks,
Nikki
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339070
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Strange issue with cfquery
try and change the following
cfquery datasource=#request.selecteddns#
#PreserveSingleQuotes(sql)#
/cfquery
From: Nkonye Oyewusi noyew...@hotmail.com
To: cf-talk cf-talk@houseoffusion.com
Sent: Wed, November 10, 2010 11:33:27 AM
Subject: Strange issue with cfquery
Hello,
I am running CF 8 on an Oracle database. I'm trying to pass in a SQL script as
a
string and keep getting the error: ORA-00907: missing right parenthesis.
This is what I'm trying to do:
cfset sendMethod = createObject(component,components.sendMethods)
cfset sendMethod.init(dsn = request.selecteddns)
cfset sql=INSERT INTO nkonye_associated_person /
cfset sql = sql chr(10) chr(13)
(ADDED_DATE,ASSOCIATED_PERSON_ID,IS_REMOVED,PATIENT_ID,PROFILE_ID,ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,ADDRESS_LINE4,ADDRESS_LINE5,ASSOCIATED_RELATIONSHIP_ID,ASSOCIATED_RELATIONSHIP_OTHER,DAYS_ATTENDED,EMAIL,FIRST_LANGUAGE_ID,FORENAME,FREQUENCY_OF_CARE,INTERPRETER_REQUIRED_ID,IS_KEYHOLDER,MODIFIED_DATE,ORGANISATION_NAME,OTHER_COMMUNICATION_ISSUE,POSTCODE,PREFERRED_LANGUAGE_ID,PREFERRED_METHOD_ID,SURNAME)
/
cfset sql = sql chr(10) chr(13) VALUES (to_date('2009-10-07
13:07:28','-mm-dd hh24:mi:ss'),8,0,17,142,'122 Cleland
hospital',null,null,null,null,1014,null,null,null,null,'Borlotti',null,null,null,null,null,null,null,null,null,'Bean')
/
cfset sendMethod.doRunScript(#sql#) /
The doRunScript method is as follows:
cffunction name=doRunScript access=public
cfargument name=sql type=string required=yes
cfquery datasource=#request.selecteddns#
#sql#
/cfquery
/cffunction
Is there a workaround for this?
Thanks,
Nikki
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339071
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: slow cfquery cfqueryparam?
Hey, maybe try something like this? I haven't tested this, but it should theoretically be faster cfset sqlstring = / cfset questionIDLen = arrayLen(questionIDArray)!---put this in a variable so it doesn't re-evaluate 2200 times--- cfset questionIDCheck = 0 cfloop index=i from=2 to=#questionIDLen# !---remove if statement so it doesn't evaluate 2200 times either.--- cfset questionIDCheck = isNumeric(questionIDArray[i] parentQuestionIDArray[i] parentLevelArray[i])!---since all your params were checking for a numeric value, lump together and be sure they are all numeric. probably could use a regEx or other cffunctions to accomplish the same thing if this does not fit the bill--- cfif questionIDCheck!---only add to sql string if it passes the numeric check. --- cfset sqlstring = '#sqlstring#,(#questionIDArray[i]#,#parentQuestionIDArray[i]#,#parentLevelArray[i]#)' /cfif /cfloop !---run query. manually add first line since we didn't loop over it.--- cfquery name=insertData datasource=dbname INSERT INTO parentquestions VALUES (cfqueryparam value=#questionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentQuestionIDArray[1]# cfsqltype=cf_sql_bigint maxlength=20,cfqueryparam value=#parentLevelArray[1]# cfsqltype=cf_sql_bigint maxlength=20) #sqlstring# /cfquery ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339076 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
