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: 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
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: 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
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
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
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: 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
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
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!
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
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
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
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
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: cfquery and carriage return
alternatively could the problem be before the data is put into mysql. basically we are using an extjs text area to collect data, that passes the data over to a form which we put straight in the database. should we be doing anything with that data such as escaping it? Hi, i have data stored in the database with a carriage return. however, when coldfusion pulls the data out with cfquery it seems to lose the carriage return, i dump out the query and it doesnt show the carriage return. i tried looping through each character in the string and outputting the ascii value and none show the character 13 for carriage return does anyone know why this happens and how to get around it. thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338120 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
Aside from my trepidations about generating SQL like this because it prevents you from using cfqueryparam, what you're looking for is #preserveSingleQuotes()# coldfusion automatically escapes any single quotes in variables that are within a cfquery tag so if your query SQL is a variable cfquery ... select blah blah blah from blah group by blah #preserveSingleQuotes(havingClause)# /cfquery Otherwise, the single quotes get doubled up and you'll get errors. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337111 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
I'm with Rick. You should really change it up a wee bit so you can use cfqueryparam. It has many advantages. On Thu, Sep 16, 2010 at 8:54 AM, Rick Root rick.r...@gmail.com wrote: Aside from my trepidations about generating SQL like this because it prevents you from using cfqueryparam, what you're looking for is #preserveSingleQuotes()# coldfusion automatically escapes any single quotes in variables that are within a cfquery tag so if your query SQL is a variable cfquery ... select blah blah blah from blah group by blah #preserveSingleQuotes(havingClause)# /cfquery Otherwise, the single quotes get doubled up and you'll get errors. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337112 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
yea so don't forget to do your own SQL injection protection as well. On Thu, Sep 16, 2010 at 1:54 PM, Rick Root rick.r...@gmail.com wrote: Aside from my trepidations about generating SQL like this because it prevents you from using cfqueryparam, what you're looking for is #preserveSingleQuotes()# coldfusion automatically escapes any single quotes in variables that are within a cfquery tag so if your query SQL is a variable cfquery ... select blah blah blah from blah group by blah #preserveSingleQuotes(havingClause)# /cfquery Otherwise, the single quotes get doubled up and you'll get errors. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337113 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
thanks for the replies, although the preserveSingleQuotes doesnt work
hi, we are running a cfquery with the following sql statement:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN
viewcategories on views.categoryid = viewcategories.categoryid GROUP
BY viewname HAVING categoryname IN ('s) ORDER BY viewname ASC
LIMIT 0, 300
if we type this directly into mysql it works fine, similarly if we
type it directly into a cfquery it works fine. however the having
clause is getting built from following code and is causing the
problem:
cfset value = arraynew(1) /
cfset value[1] = 's /
cfloop index=a from=1 to=#arraylen(value)#
cfset value[a] = '#value[a]#' /
/cfloop
cfset havingClause = HAVING categoryname IN (#arraytolist(value)#)
/
the problem is occuring due to the apostrophe '. we have tried
preserveSingleQuotes but still does not work.
is there any way around this?
thanks
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337119
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
On 9/16/2010 6:44 AM, Richard White wrote:
thanks for the replies, although the preserveSingleQuotes doesnt work
You should be looking at the actual SQL that is being sent to the
database. It would be provide clear evidence of what is wrong.
Looking at this line:
cfset havingClause = HAVING categoryname IN (#arraytolist(value)#)
I expect you are getting SQL output that looks like this:
HAVING categoryname IN (aaa'a)
What proper SQL should look like is:
HAVING categoryname IN ('aaa''a') !--- note the double single quotes
inside the outer quotes ---
You may want to investigate the listQualify() function.
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337120
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
Or instead of using a variable, if you can just put the clause in the SQL statement, let the list param do the work for you: HAVING categoryname IN ( cfqueryparam cfsqltype=cf_sql_varchar value=#arrayToList(value)# list=Yes / ) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337123 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
Wrap your actual cfquery with cftry and then cfoutput the variable cfcatch.sql cftry cfquery.../cfquery cfcatch type=Databasecfoutputpre#cfcatch.sql#/pre/cfoutput/cfcatch /cftry Then post that sql here. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337131 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
thanks, although the problem is there is no error to catch. when we run a
cfdump of the query, the sql is as follows:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on
views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING 0 = 0 AND
categoryname IN ('s) ORDER BY viewname ASC LIMIT 0, 300
it just doesnt pull out the data, but if we copy it directly into mysql command
line it works fine
we do not even know how to trace the error as how can it work in mysql command
line but not in coldfusion? completely lost on this one!
Wrap your actual cfquery with cftry and then cfoutput the variable cfcatch.sql
cftry
cfquery.../cfquery
cfcatch
type=Databasecfoutputpre#cfcatch.sql#/pre/cfoutput/cfcatch
/cftry
Then post that sql here.
Rick
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337153
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
If it was MSSQL, you would want single quotes, so more like this:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN viewcategories on
views.categoryid = viewcategories.categoryid GROUP BY viewname HAVING 0 = 0
AND categoryname IN ('''s') ORDER BY viewname ASC LIMIT 0, 300
Instead of setting your havingClause variable and messing with
preserveSingleQuotes(), can you just do this?
cfquery
SELECT viewname, categoryname
FROM views LEFT OUTER JOIN
viewcategories ON views.categoryid = viewcategories.categoryid
GROUP BY viewname
HAVING 0 = 0
AND categoryname IN ( cfqueryparam cfsqltype=CF_SQL_VARCHAR
value=#arrayToList(value)# list=Yes / )
ORDER BY viewname ASC LIMIT 0, 300
/cfquery
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337154
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery not returning results with apostrophe
thanks for everyones help, its now solved!
the reason i am building it through a string is because it is interacting with
various extjs grids, which apply filters. i have a generic function that
creates the havingclause to filter the data.
i ran the havingclause through a loop printing out 1 character at a time. the
apostophe was printed as '%apos;' even though it appeared as ' in the sql
statement!
have taken on board comments re cfqueryparam and now that this error is fixed
will update it to include the sql injection
thanks for all the help :)
hi, we are running a cfquery with the following sql statement:
SELECT viewname, categoryname FROM views LEFT OUTER JOIN
viewcategories on views.categoryid = viewcategories.categoryid GROUP
BY viewname HAVING categoryname IN ('s) ORDER BY viewname ASC
LIMIT 0, 300
if we type this directly into mysql it works fine, similarly if we
type it directly into a cfquery it works fine. however the having
clause is getting built from following code and is causing the
problem:
cfset value = arraynew(1) /
cfset value[1] = 's /
cfloop index=a from=1 to=#arraylen(value)#
cfset value[a] = '#value[a]#' /
/cfloop
cfset havingClause = HAVING categoryname IN (#arraytolist(value)#)
/
the problem is occuring due to the apostrophe '. we have tried
preserveSingleQuotes but still does not work.
is there any way around this?
thanks
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337159
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
Google is your friend: Search cfquery to json and get: http://www.coldfusionjedi.com/index.cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example On Mon, Jun 7, 2010 at 11:53 AM, Richard White rich...@j7is.co.uk wrote: hi, is there a quick and easy way to convert a cfquery into a json array. i will be returning the json array to extjs to present in a grid thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334334 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
thanks, sorry dont think i was clear enough, i knew about serializejson but this doesnt work with extjs, in case anyone else stumbles on this post and is interested i found the following: http://blog.cutterscrossing.com/index.cfm/CFQueryReader Google is your friend: Search cfquery to json and get: http://www.coldfusionjedi.com/index.cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
in fact toJson is even better for th job: http://tojson.riaforge.org/ thanks, sorry dont think i was clear enough, i knew about serializejson but this doesnt work with extjs, in case anyone else stumbles on this post and is interested i found the following: http://blog.cutterscrossing.com/index.cfm/CFQueryReader Google is your friend: Search cfquery to json and get: http://www.coldfusionjedi.com/index. cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334336 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery to json array
toJson is better if you have room for the server-side overhead, and don't mind writing methods that can't be used in any other scenario than via Ajax. CFQueryReader is solely a client side solution, so that you can reuse your server-side code in any way that you see fit. Steve Cutter Blades Adobe Community Professional - ColdFusion Adobe Certified Professional Advanced Macromedia ColdFusion MX 7 Developer Co-Author of Learning Ext JS http://www.packtpub.com/learning-ext-js/book _ http://blog.cutterscrossing.com Richard White wrote: in fact toJson is even better for th job: http://tojson.riaforge.org/ thanks, sorry dont think i was clear enough, i knew about serializejson but this doesnt work with extjs, in case anyone else stumbles on this post and is interested i found the following: http://blog.cutterscrossing.com/index.cfm/CFQueryReader Google is your friend: Search cfquery to json and get: http://www.coldfusionjedi.com/index. cfm/2007/9/20/Quick-and-Dirty-JSONQuery-Example ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334346 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery within javascript
What are you trying to show. If you want a literal put your alert results in quotes. alert(cfoutput#query2.RecordCount#/cfoutput); Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: Wednesday, April 14, 2010 4:13 PM To: cf-talk Subject: cfquery within javascript hi, I am trying to do the following: I wrote a cfquery within javascript function as below: script language=javascript1.2 type=text/javascript cfquery name=query1 datasource=abc /cfquery cfif query1.recordcount neq 0 cfoutput query = query1 cfquery name=query2 datasource=abc select * from table1 where name=#query1.name# /cfquery /cfoutput alert(cfoutput#query2.RecordCount#/cfoutput); /cfif /script The alert value is 1 but the debugging section shows 0, and 0 is the correct value. is there something wrong that I am doing. If so can you please point out... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332888 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery within javascript
Any reason you are doing it this way and not just no javascript and use cfdump instead? -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: Thursday, 15 April 2010 6:13 AM To: cf-talk Subject: cfquery within javascript hi, I am trying to do the following: I wrote a cfquery within javascript function as below: script language=javascript1.2 type=text/javascript cfquery name=query1 datasource=abc /cfquery cfif query1.recordcount neq 0 cfoutput query = query1 cfquery name=query2 datasource=abc select * from table1 where name=#query1.name# /cfquery /cfoutput alert(cfoutput#query2.RecordCount#/cfoutput); /cfif /script The alert value is 1 but the debugging section shows 0, and 0 is the correct value. is there something wrong that I am doing. If so can you please point out... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332889 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfquery within javascript
Couldn't you just say: cfif query1.recordcount [...] cflogic [...] /cfif Sebastiaan = So long and thanx 4 all the fish == Onlinebase.nl -Original Message- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: Thursday, 15 April 2010 6:13 AM To: cf-talk Subject: cfquery within javascript hi, I am trying to do the following: I wrote a cfquery within javascript function as below: script language=javascript1.2 type=text/javascript cfquery name=query1 datasource=abc /cfquery cfif query1.recordcount neq 0 cfoutput query = query1 cfquery name=query2 datasource=abc select * from table1 where name=#query1.name# /cfquery /cfoutput alert(cfoutput#query2.RecordCount#/cfoutput); /cfif /script The alert value is 1 but the debugging section shows 0, and 0 is the correct value. is there something wrong that I am doing. If so can you please point out... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332890 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQUERY Question
Your cfif statement asks for the recordCount to be returned on if the recordCount is greater than 0. Try cfif blahName.recordcount GTE 0 and you should get the recordCount either way. Rick -Original Message- From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] Sent: Wednesday, March 31, 2010 1:59 PM To: cf-talk Subject: CFQUERY Question I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) cfquery name=blahName datasource=blah SELECT blah FROM blah WHERE blah = 1 /cfquery CFIF blahName.recordCount GT 0 12345 /CFIF if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332499 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY Question
You are correct, the record count for an empty query object does exist (and should be 0). What exactly is not working? Are you getting an error message? On Wed, Mar 31, 2010 at 1:59 PM, Dave Sueltenfuss dsueltenf...@gmail.comwrote: I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) cfquery name=blahName datasource=blah SELECT blah FROM blah WHERE blah = 1 /cfquery CFIF blahName.recordCount GT 0 12345 /CFIF if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332500 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CFQUERY Question
You are correct that it should work, but they maybe something else in the code which is preventing the query from being executed. On the top of your page add: cfparam name= blahName.recordcount default=0 And that will deal with the case where the query is not executed. Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged -Original Message- From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] Sent: Wednesday, March 31, 2010 1:59 PM To: cf-talk Subject: CFQUERY Question I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) cfquery name=blahName datasource=blah SELECT blah FROM blah WHERE blah = 1 /cfquery CFIF blahName.recordCount GT 0 12345 /CFIF if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332501 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY Question
Ok, found the cause of the issue, by taking the query into SQL Server In SQL, the following is being returned *Warning: Null value is eliminated by an aggregate or other SET operation. (0 row(s) affected)* So it appears this is what is throwing off the query results in CF I am working on resolving this issue now. -Dave On Wed, Mar 31, 2010 at 2:18 PM, Robert Harrison rob...@austin-williams.com wrote: You are correct that it should work, but they maybe something else in the code which is preventing the query from being executed. On the top of your page add: cfparam name= blahName.recordcount default=0 And that will deal with the case where the query is not executed. Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged -Original Message- From: Dave Sueltenfuss [mailto:dsueltenf...@gmail.com] Sent: Wednesday, March 31, 2010 1:59 PM To: cf-talk Subject: CFQUERY Question I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) cfquery name=blahName datasource=blah SELECT blah FROM blah WHERE blah = 1 /cfquery CFIF blahName.recordCount GT 0 12345 /CFIF if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332502 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CFQUERY Question
If the query is executing correctly but bringing back no records, this should work with blahname.recordcount equal zero . If the query is not executing correctly, blahname.recordcount will not equal zero. On Wed, Mar 31, 2010 at 10:59 AM, Dave Sueltenfuss dsueltenf...@gmail.com wrote: I'm running into an odd behavior with CFQUERY (at least I think it is odd) I have the following code (example) cfquery name=blahName datasource=blah SELECT blah FROM blah WHERE blah = 1 /cfquery CFIF blahName.recordCount GT 0 12345 /CFIF if the query blahName returns no results, I thought i would still be able to access blahName.recordCount Am I wrong on this, or is there something else I am overlooking? Thanks -Dave ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:332503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfquery return datatypes
Hello. Is there a way to return a datatype list from your query along with the column list? Example: IIRC, there is no direct method. But you could certainly extract that information using getMetaData() cfdump var=#getMetaData(qryTest)# ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331179 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: cfquery return datatypes
You can usually get that info from the database's system tables, depending on your RDBMS. (In the following queries, 'typename' will give you the datatype.) MSSQL: SELECT syscolumns.colid, syscolumns.name, syscolumns.colorder AS sortorder, syscolumns.prec AS collen, syscolumns.xtype, syscolumns.typestat, syscolumns.xusertype, syscolumns.isnullable, systypes.name AS typename, ( SELECT COUNT(*) FROM sysindexkeys WHERE id = syscolumns.id AND indid = 1 AND colid = syscolumns.colid ) AS isPrimary FROM syscolumns LEFT JOIN systypes ON syscolumns.xtype = systypes.xtype AND syscolumns.xusertype = systypes.xusertype WHERE id = OBJECT_ID(cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#arguments.tableName#) ORDER BY colorder Oracle: SELECT table_name colid, column_name name, column_id sortorder, data_length collen, nullable isnullable, data_type typename, ( SELECT COUNT(*) FROM user_constraints c INNER JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name WHERE c.table_name = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#UCase(arguments.tableName)# AND c.constraint_type = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=P AND cc.column_name = user_tab_columns.column_name ) AS isPrimary FROM user_tab_columns WHERE table_name = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#UCase(arguments.tableName)# ORDER BY column_id ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331186 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery return datatypes
In CF8 there is cfdbinfo This function below will return a query object the following fields (see below). http://www.cfquickdocs.com/cf8/?getDoc=cfdbinfo#cfdbinfo If you don't have CF 8 you will have to query the metadata based on the SQL dialect for that particular database. You can look at the source code for Brian Rinaldi's Illudium cfcgenerator. Last time I looked he had cfcs for MySQL, MSSQL, Oracle and Postges. http://code.google.com/p/cfcgenerator/ cffunction name=GetTableMetaData access=public output=false returntype=query hint=I return column metadata cfargument name=MyDSN required=true type=string / cfargument name=MyTable required=true type=string / cfdbinfo type=columns datasource=#arguments.MyDSN# name=TableMetaData table = #Arguments.MyTable# cfreturn TableMetaData / /cffunction COLUMN_NAME Name of the column. TYPE_NAME SQL data type of the column. IS_NULLABLE Whether the column allows nulls. IS_PRIMARYKEY Whether the column is a primary key. IS_FOREIGNKEY Whether the column is a foreign key. REFERENCED_PRIMARYKEY If the column is a foreign key, the name of the table it refers to. REFERENCED_PRIMARYKEY_TABLE If the column is a foreign key, the key name it refers to. COLUMN_SIZE Size of the column DECIMAL_DIGITS Number of digits to the right of the decimal point. COLUMN_DEFAULT_VALUE Default value of column. CHAR_OCTET_LENGTH Maximum length in bytes of a character or integer data type column. ORDINAL_POSITION Ordinal position of the column. REMARKS Remarks of the column. On Fri, Feb 26, 2010 at 12:13 PM, Joshua Rowe joshua.r...@varimereweb.comwrote: Hello. Is there a way to return a datatype list from your query along with the column list? Example: cfquery name=qryTest datasource=test SELECT * FROM tblTest; /cfquery cfoutput #qryTest.columnlist#br #qryTest.datatypes# /cfoutput Thanks! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331193 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfquery timeout doesn't work out on jdbc driver
How about installing SeeFusion and enabling one of its rules to kill threads that run for a certain about of time? If there is a running query it will kill it. It may be a hammer where you wanted a scalpel, but it would work. Since requests who area waiting for external I/O can't monitor themselves, often the only way to ensure such a timeout is with a second monitor thread. ~Brad Original Message Subject: cfquery timeout doesn't work out on jdbc driver From: Vamsi Pappu vissu.va...@gmail.com Date: Thu, October 08, 2009 7:47 am To: cf-talk cf-talk@houseoffusion.com My requirement is no matter what happens, if query is not responding in given amount of time then it should timeout.Can anyone suggest a good advice. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327034 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery timeout doesn't work out on jdbc driver
Run it in a thread with CFthread and put a timeout on the cfjoin. Adam On Thu, Oct 8, 2009 at 8:47 AM, Vamsi Pappu vissu.va...@gmail.com wrote: Hi All, I have a situation here where cfquery doesn't seem to be performing well on jdbc driver, when I read in forums it was clearly mentioned that cfquery timeout doesn't support some drivers, Is there any another alternate to this. My requirement is no matter what happens, if query is not responding in given amount of time then it should timeout.Can anyone suggest a good advice. Thanks, Vamsi ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327036 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery timeout doesn't work out on jdbc driver
Or figure out why its running slowly, and make it run faster. =) Rick On Thu, Oct 8, 2009 at 12:01 PM, Adam Haskell a.hask...@gmail.com wrote: Run it in a thread with CFthread and put a timeout on the cfjoin. Adam On Thu, Oct 8, 2009 at 8:47 AM, Vamsi Pappu vissu.va...@gmail.com wrote: Hi All, I have a situation here where cfquery doesn't seem to be performing well on jdbc driver, when I read in forums it was clearly mentioned that cfquery timeout doesn't support some drivers, Is there any another alternate to this. My requirement is no matter what happens, if query is not responding in given amount of time then it should timeout.Can anyone suggest a good advice. Thanks, Vamsi ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327052 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: cfquery
I think you want it this way, using a CASE statement for the leadmgr column. I also moved all your subselects into left joins, which should give a bit better performance. cfquery datasource=askseaton name=getOffices result=varibles select s.address + ' ' + s.address2 + ' ' + s.address3 + ',' + s.city + ',' + s.state + ',' + s.city as office_address, leadmgr = case when (lm.user_id is null) then lm.lead_note else lm.first + ' ' + lm.last end, amr.first + ' ' + amr.last as director, sn.first + ' ' + sn.last as SDIRNDIR, vp.first + ' ' + vp.last as VICEPRE, fm.first + ' ' + fm.last as FASTMGR from sourcebook_1 s left outer join user_info lm on s.lead_mgr = lm.user_id left outer join user_info amr on s.area_mgr_rdo = amr.user_id left outer join user_info sn on s.sdir_ndir = sn.user_id left outer join user_info vp on s.vice_pre = vp.user_id left outer join user_info fm on s.fast_mgr = fm.user_id where active = 1 and office_id not in (36,37,38,73) order by office_number /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324792 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery
cfquery datasource=askseaton name=getOffices select iif(lm.user_id = s.lead_mgr, (lm.first + ' ' + lm.last) , s.lead_note) as leadMgr, iif(lm.user_id = s.AREA_MGR_RDO, lm.first + ' ' + lm.last , s.Area_Note) as director , iif(lm.user_id = s.sdir_ndir, lm.first + ' ' + lm.last , s.sdir_note) as sndir , iif(lm.user_id = s.vice_pre, lm.first + ' ' + lm.last , s.vice_note) as vicePre , iif(lm.user_id = s.fast_mgr, lm.first + ' ' + lm.last , s.fast_note) as fastMgr , from sourcebook_1 s,user_info lm where s.active = 1 and (lm.user_id = s.lead_mgr or lm.user_id = s.AREA_MGR_RDO or lm.user_id = s.sdir_ndir or lm.user_id = s.vice_pre or lm.user_id = s.fast_mgr) and s.office_id not in(36,37,38,73) order by s.office_number /cfquery i am writing like this, it is working fine, thanks for your response I think you want it this way, using a CASE statement for the leadmgr column. I also moved all your subselects into left joins, which should give a bit better performance. cfquery datasource=askseaton name=getOffices result=varibles select s.address + ' ' + s.address2 + ' ' + s.address3 + ',' + s.city + ',' + s.state + ',' + s.city as office_address, leadmgr = case when (lm.user_id is null) then lm.lead_note else lm.first + ' ' + lm.last end, amr.first + ' ' + amr.last as director, sn.first + ' ' + sn.last as SDIRNDIR, vp.first + ' ' + vp.last as VICEPRE, fm.first + ' ' + fm.last as FASTMGR from sourcebook_1 s left outer join user_info lm on s.lead_mgr = lm.user_id left outer join user_info amr on s.area_mgr_rdo = amr.user_id left outer join user_info sn on s.sdir_ndir = sn.user_id left outer join user_info vp on s.vice_pre = vp.user_id left outer join user_info fm on s.fast_mgr = fm.user_id where active = 1 and office_id not in (36,37,38,73) order by office_number /cfquery ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321605 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
On Tue, Apr 14, 2009 at 5:25 PM, Sung Woo s...@cornell.edu wrote: Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinitely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. That's totally separate issue. if sqlserver is locking up, coldfusion is waiting to make a connection, not getting the result back. the timeout on cfquery only works on the actually sql statement itself. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321606 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY Timeout for SQL2005 Fails
The query timeout is not designed to work when there is no connection at all to the DB server. It is designed to time the statement - so if the connection itself is lost the setting will not have any affect. In affect you have an orphaned thread out there that is taking up space in the running request queue. The big question is why do you lose connection? Networking? Overloaded SQL server? That's where you need to focus I think. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Sung Woo [mailto:s...@cornell.edu] Sent: Wednesday, April 15, 2009 7:58 AM To: cf-talk Subject: Re: CFQUERY Timeout for SQL2005 Fails Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321608 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
This is an interesting thread, I've seen what I believe to be very similar behaviour here on one of my webservices, seemed that one particular query sometimes just hangs indefinitly, however other threads and requests are all just fine. I tried playing around with application request timeouts, concurrent threads and cfquery timeout settings but seem to still have the same problem on occasion, I'm wondering if they're caused by the same issue, I too running SQL2k5 however I'm on CF8. Mark, I'd concur that its waiting to establish a connection, is there anything which can be done to cure that problem with a timeout on the connection? I'd imagine mine is down to load as its a busy statistical database which we're having problems with which really needs moving to a beefier box but in the mean time it'd be nice to try and find a quick-fix to tide me over. Rob That's totally separate issue. if sqlserver is locking up, coldfusion is waiting to make a connection, not getting the result back. the timeout on cfquery only works on the actually sql statement itself. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321611 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Timeout for SQL2005 Fails
Mark and Casey, Thanks for clarifying this for me. I don't own the SQL Server that I need to hit, so it looks like I'm between a rock and a hard place. I still don't think the TIMEOUT parameter is working, though. In my example, I'm hitting a database that is fully operational, and I have a query that runs for about 3.5 seconds. However, I have the TIMEOUT param set to 1, so shouldn't it time out in exactly 1 second and return an error? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321612 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Also -- this may just be me being dense, but I don't get why the TIMEOUT feature in CFQUERY has to work this way. It's CF that's making the initial request to the datasource, so it should start counting down from the moment it creates that request. And then after waiting for a predetermined point of time (in my case, 1 second), it should sever the connection if the connection still exists and return an error. Is there another way this can be handled? Like I said, CFSETTING is also useless in this example, since it, too, has to wait for the CFQUERY to end. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321613 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Hi Mark, To answer your question -- there's a DDL transaction that's running on the server, and from what the folks have told me, an ALTER TABLE command was causing the lock to occur. And the downside is that giving hints (i.e., WITH (NOLOCK)) is not going to have an effect in this situation. Working for a large corporation, I often do not have control over the databases that are in the company. So as much as I'd love to fix the issue, I can't. I'm sure we've all been there. This is why it's imperative that CF has the ability to protect itself against underperforming datasources. - Sung The query timeout is not designed to work when there is no connection at all to the DB server. It is designed to time the statement - so if the connection itself is lost the setting will not have any affect. In affect you have an orphaned thread out there that is taking up space in the running request queue. The big question is why do you lose connection? Networking? Overloaded SQL server? That's where you need to focus I think. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321617 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFQUERY Timeout for SQL2005 Fails
For an ALTER TABLE to have this affect it would need to be running on a table that you were trying to access. Dropping and adding columns (if that is what was happening) on production tables will cause lots of problems - and not just with CF. It should be done infrequently and very carefully. I would be at a loss to really help if there is no cooperation at the other end. The DB folks have to be involved here. You can't work around poor practice or bad communication. -Mk -Original Message- From: Sung Woo [mailto:s...@cornell.edu] Sent: Wednesday, April 15, 2009 10:38 AM To: cf-talk Subject: Re: CFQUERY Timeout for SQL2005 Fails Hi Mark, To answer your question -- there's a DDL transaction that's running on the server, and from what the folks have told me, an ALTER TABLE command was causing the lock to occur. And the downside is that giving hints (i.e., WITH (NOLOCK)) is not going to have an effect in this situation. Working for a large corporation, I often do not have control over the databases that are in the company. So as much as I'd love to fix the issue, I can't. I'm sure we've all been there. This is why it's imperative that CF has the ability to protect itself against underperforming datasources. - Sung The query timeout is not designed to work when there is no connection at all to the DB server. It is designed to time the statement - so if the connection itself is lost the setting will not have any affect. In affect you have an orphaned thread out there that is taking up space in the running request queue. The big question is why do you lose connection? Networking? Overloaded SQL server? That's where you need to focus I think. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321618 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
The docs say, no, it may not timeout at 1 second. Check out the livedocs... Because the timeout attribute only affects the maximum time for each suboperation of a query, the cumulative time may exceed its value. And, even then, the query stuff is considered to be 3rd party (or at least, so I remember reading a ways back) and does not fall under the same rules as CF templates when it comes to setting a timeout using CFsetting or by way of the administrator. The best way to kill those hung threads is going to be something like FusionReactor or SeeFusion. Matthew Williams Geodesic GraFX ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321619 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Check this out. This one, as I've said before, outputs a number around 3000. cfquery name=test datasource=dsn timeout=1 select top 2 * from table/cfquery cfoutput#cfquery.ExecutionTime#/cfoutput However, this one results in an error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Execution timeout expired. cfquery name=test datasource=dsn timeout=1 select top 1 * from table waitfor delay '00:00:05' /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput Porbably because the query in the second test ran under a second (completed successfully), then it waited five seconds. I read that portion of the docs, too, but I thought it meant if you'd strung more than a single SQL statement -- i.e., SELECT/INSERT/SELECT. Regardless, I think it's terrible that there isn't more substantial control over this. Shouldn't CF know how long a 3rd-party request is taking? There's a timeout parameter in CFHTTP, and it works just fine: wait.cfm cfset thread = CreateObject(java, java.lang.Thread) cfset thread.sleep(1) test.cfm cfhttp url=wait.cfm method=GET timeout=1 /cfhttp cfdump var=#CFHTTP# Result, in about two seconds: Statuscode 408 Request Time-out Which leads me to believe that the connection was indeed broken and CF completed the thread. And that's a 3rd-party call, too, isn't it? The docs say, no, it may not timeout at 1 second. Check out the livedocs... Because the timeout attribute only affects the maximum time for each suboperation of a query, the cumulative time may exceed its value. And, even then, the query stuff is considered to be 3rd party (or at least, so I remember reading a ways back) and does not fall under the same rules as CF templates when it comes to setting a timeout using CFsetting or by way of the administrator. The best way to kill those hung threads is going to be something like FusionReactor or SeeFusion. Matthew Williams Geodesic GraFX ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321627 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Timeout for SQL2005 Fails
Actually, I was wrong -- even cfsetting requesttimeout=1 enablecfoutputonly=No doesn't work, because it still waits for the cfquery to finish before it outputs the timeout error. Is there any solution for this? The database we're relying on is unstable and we need to be able to kill the query sooner. Thanks, - Sung Here's my test: cfquery name=test datasource=dsn timeout=1 select top 2 * from table /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput I'm getting 3000+ for this value. This shouldn't be, right? Because I have the timeout parameter set for a second? The driver I'm using is the one that's supplied with CFMX7.02, Microsoft SQL Server. The only way I can make this work is if I use cfsetting requesttimeout=1 enablecfoutputonly=No, but I don't want to do that. What is going on? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321590 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery On Tue, Apr 14, 2009 at 4:58 PM, Sung Woo s...@cornell.edu wrote: Actually, I was wrong -- even cfsetting requesttimeout=1 enablecfoutputonly=No doesn't work, because it still waits for the cfquery to finish before it outputs the timeout error. Is there any solution for this? The database we're relying on is unstable and we need to be able to kill the query sooner. Thanks, - Sung ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321591 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Timeout for SQL2005 Fails
Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321594 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery result=resultInfo
and have already picked up a nugget. A tasty one at that. Thanx for sharing. Should I return an array of structures I would say yes. From what I have been reading, arrays of structures is the standard way to exchange data between platforms (Think web services). If you do it that way (arrays of structures) systems using Java, .NET, PHP etc.will be able to call the CFC as a web service if the need ever arises. G! On Thu, Mar 19, 2009 at 10:23 AM, Phillip Senn phillips...@gmail.comwrote: I am reading the cfcurriculum at http://www.adobe.com/education/instruction/teach/cfcurriculum.html and have already picked up a nugget. I didn't realize that cfquery returned two results, the name= attribute and the result= attribute. I've always just returned the query in my functions, but now I'm considering returning both the query and the result info. Q: What's the best way to return two structures from a function? Should I return an array of structures or can I return the resultInfo in one of the arguments from the calling program? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320693 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery result=resultInfo
Q: What's the best way to return two structures from a function? Should I return an array of structures or can I return the resultInfo in one of the arguments from the calling program? Well, strictly speaking, if you set the value of something within the calling program that's not returning a value. You'll want to return a single value, which could be an array or a structure. It really doesn't matter which you do. I'd probably return a structure with both, if I wanted to return both, so that I could then refer to the contents of that structure by name instead of by position. Arrays are generally used to hold a bunch of items of the same type, although CF doesn't force you to only put items of one type within an array as many other languages do. Gerald's point about returning arrays of structures is useful if you're going to interact with non-CF systems, but those systems won't be able to consume a query object anyway, so that's not too important in this case. If you wanted to write a service which could be consumed by other platforms, you'd have to return the query itself as an array of structures. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320695 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfquery result=resultInfo
This is the syntax for doing it with an array of structures: cfset var result = ArrayNew(1) cfquery name=qry datasource=myDatasource result=info SELECT * FROM Tablename /cfquery cfset result[1] = qry cfset result[2] = info cfreturn result What would be the syntax for using a structure of structures? Q: What's the best way to return two structures from a function? Should I return an array of structures or can I return the resultInfo in one of the arguments from the calling program? Well, strictly speaking, if you set the value of something within the calling program that's not returning a value. You'll want to return a single value, which could be an array or a structure. It really doesn't matter which you do. I'd probably return a structure with both, if I wanted to return both, so that I could then refer to the contents of that structure by name instead of by position. Arrays are generally used to hold a bunch of items of the same type, although CF doesn't force you to only put items of one type within an array as many other languages do. Gerald's point about returning arrays of structures is useful if you're going to interact with non-CF systems, but those systems won't be able to consume a query object anyway, so that's not too important in this case. If you wanted to write a service which could be consumed by other platforms, you'd have to return the query itself as an array of structures. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320721 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQuery
I am reviewing CF and working on a small site to get my skills back up to par. Here is what I have: CFSET theSQL = SELECT queryName, title, content FROM content WHERE queryName = '#URL.queryName#' cfquery name=getContent datasource=indie#theSQL#/cfquery CFOUTPUT#theSQL#/CFOUTPUT I am getting an error about SQL Statement being invalid: ... Notice that PrivacyPolicy has ³² around it..I am not putting it there. If I put this statement in a SQL editor and run it it is invalid and it I swithc to like I think I am doing in the CFSET it works. What am I doing wrong? You are doing two things wrong. First, if you want to use a single-quoted string like you're doing, you need to use PreserveSingleQuotes: cfquery ...#PreserveSingleQuotes(theSQL)#/cfquery Second, and more importantly, using raw data from the browser like that is a serious security vulnerability. Whenever you use unsafe data within a query, you should build a prepared statement using the CFQUERYPARAM tag: http://www.adobe.com/devnet/coldfusion/articles/cfqueryparam.html Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317684 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQuery
Hi Dave, Thanks for the reply. Second, and more importantly, using raw data from the browser like that is a serious security vulnerability. Whenever you use unsafe data within a query, you should build a prepared statement using the CFQUERYPARAM tag: http://www.adobe.com/devnet/coldfusion/articles/cfqueryparam.html Thanks for the link. I see that you authored this article. Very well laid out. I understand SQL Injection and qhy to use CFQUERYPARAM. What I did not see in the article was how to take a CFSET statement that builds a SQL String and put CFQUERYPARAMS into it and make it work. Like: cfset theSQL = SELECT queryName, title, content FROM content WHERE queryName = 'cfqueryparam cfsqltype=cf_sql_varchar value=#URL.queryName#' How does one accomplish this? Thanks! -Jason ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317688 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQuery
Thanks for the link. I see that you authored this article. Very well laid out. I understand SQL Injection and qhy to use CFQUERYPARAM. What I did not see in the article was how to take a CFSET statement that builds a SQL String and put CFQUERYPARAMS into it and make it work. Like: cfset theSQL = SELECT queryName, title, content FROM content WHERE queryName = 'cfqueryparam cfsqltype=cf_sql_varchar value=#URL.queryName# ' How does one accomplish this? You can't really do it like that... you can never put cf tags inside a string literal and have them work (except in the case of code generation, which isn't what you want). (Also the double-quotes would need to be escaped () in order to embed them in a string.) The reason why the cfquery tag has an end-tag however is actually to eliminate the need to build sql statements using string literals like this. So normally you would use this: cfquery ... SELECT queryName, title, content FROM content WHERE queryName = cfqueryparam cfsqltype=cf_sql_varchar value=#URL.queryName# / /cfquery Note that when you use a cfqueryparam tag you omit the single-quotes around the parameter. The parameter will handle the quotes for you if they're needed. Having said that, the DataFaucet ORM actually does use a technique which allows it to have just one pair of cfquery tags in the entire framework, which behaves similarly to what you're describing. They don't work on a string literal however, they work on an array which contains some strings and some structures, where the structures contain the attributes that will go into cfqueryparam tags. That looks similar to this: cfquery ... cfloop index=x from=1 to=#ArrayLen(sql)# cfif isStruct(sql[x]) cfqueryparam attributecollection=#sql[x]# / cfelse #preserveSingleQuotes(sql[x])# /cfif /cfloop /cfquery Other mechanisms within the ORM framework help to prevent SQL injection -- with just a small number of rules to follow regarding the things you should not do with the ORM to prevent opening yourself to attacks. -- s. isaac dealey ^ new epoch isn't it time for a change? ph: 781.769.0723 http://onTap.riaforge.org/blog ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317694 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Question
On Wednesday 07 Jan 2009, Andrew Tegenkamp wrote: can I fix it? You can, but shouldn't. Constructing raw SQL strings is dangerous, security wise. I expect your real use case is more complex, but rewriting it to : cfquery name=insert datasource=#DSN# INSERT INTO users(userName) VALUES(cfqueryparam value=andrew/) /cfquery is trivial. -- Tom Chiverton Helping to synergistically negotiate fourth-generation next-generation metrics This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office together with a list of those non members who are referred to as partners. We use the word ?partner? to refer to a member of the LLP, or an employee or consultant with equivalent standing and qualifications. Regulated by the Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317604 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Question
Andrew Tegenkamp wrote: Why is this Because CF automatically escapes all single quotes in variables used inside of a CFQUERY... block. and can I fix it? Use the perserveSingleQuotes() function whose purpose is to over ride this default behavior. Just be aware that if you are not careful, it is really easy to open yourself up to all kinds of potential for SQL injection attacks with this kind of functionality. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317534 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: cfquery and cfstoredproc
Actually, I'm gonna pick on you again Dave and challenge this. (I'm hoping to add to my wall) If a someone is using MySQL ... Well, the original poster was asking about the current attack, which specifically targets MS SQL Server. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309509 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
