Re: What the heck
You're missing quotes. 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com On Aug 22, 2011, at 12:50 PM, Robert Harrison wrote: What am I missing here Element USERID is undefined in SESSION. The error occurred in C:\inetpub\wwwroot\vaughn_college_portal\_main_securecheck.cfm: line 3 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) ~| 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:346935 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: What the heck
Yeah... is see that now. Thanks 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: Wil Genovese [mailto:jugg...@trunkful.com] Sent: Monday, August 22, 2011 1:53 PM To: cf-talk Subject: Re: What the heck You're missing quotes. 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) Wil Genovese Sr. Web Application Developer/ Systems Administrator CF Webtools www.cfwebtools.com wilg...@trunkful.com www.trunkful.com On Aug 22, 2011, at 12:50 PM, Robert Harrison wrote: What am I missing here Element USERID is undefined in SESSION. The error occurred in C:\inetpub\wwwroot\vaughn_college_portal\_main_securecheck.cfm: line 3 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) ~| 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:346936 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: What the heck
It should be cfif isDefined(session.userid) isdefined requires what you are looking for to be in quotes unless the variable name you are looking for is stored in session.userid. Steve -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Monday, August 22, 2011 1:51 PM To: cf-talk Subject: What the heck What am I missing here Element USERID is undefined in SESSION. The error occurred in C:\inetpub\wwwroot\vaughn_college_portal\_main_securecheck.cfm: line 3 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) ~| 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:346937 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: What the heck
IsDefined takes a string. But you'll want to do structKeyExists instead anyway. On Mon, Aug 22, 2011 at 11:50 AM, Robert Harrison rob...@austin-williams.com wrote: What am I missing here Element USERID is undefined in SESSION. The error occurred in C:\inetpub\wwwroot\vaughn_college_portal\_main_securecheck.cfm: line 3 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) ~| 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:346938 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: What the heck
Your comment mentions cookie .. should it be (cookie.userid)? -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Monday, August 22, 2011 12:51 PM To: cf-talk Subject: What the heck What am I missing here Element USERID is undefined in SESSION. The error occurred in C:\inetpub\wwwroot\vaughn_college_portal\_main_securecheck.cfm: line 3 1 : !--- if the cookie is here, procees to dashboard --- 2 : cfset validated_id=0 3 : cfif isDefined(session.userid) ~| 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:346939 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: What the heck
Your comment mentions cookie .. should it be (cookie.userid)? That's further down :-) cfif isDefined(session.userid) cfif session.userid gt 0 cfset validated_id=#session.userid# /cfif cfelseif IsDefined(Cookie.StudentPortalUser) cfset validated_id=#Cookie.StudentPortalUser# cfset session.userid=#Cookie.StudentPortalUser# /cfif 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 ~| 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:346940 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: What the heck
cfif StructKeyExists(cookie,StudentPortalUser) Is what I would do Same with Session cfif StructKeyExists(Session,UserID) -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Monday, August 22, 2011 1:16 PM To: cf-talk Subject: RE: What the heck Your comment mentions cookie .. should it be (cookie.userid)? That's further down :-) cfif isDefined(session.userid) cfif session.userid gt 0 cfset validated_id=#session.userid# /cfif cfelseif IsDefined(Cookie.StudentPortalUser) cfset validated_id=#Cookie.StudentPortalUser# cfset session.userid=#Cookie.StudentPortalUser# /cfif 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 ~| 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:346941 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: What the heck
It doesn't really matter, but I just... can't... let... it... slide. No need for a single hash in this code. cfif isDefined(session.userid) cfif session.userid cfset validated_id = session.userid / /cfif cfelseif IsDefined(Cookie.StudentPortalUser) cfset validated_id = Cookie.StudentPortalUser / cfset session.userid = Cookie.StudentPortalUser / /cfif On Mon, Aug 22, 2011 at 2:16 PM, Robert Harrison rob...@austin-williams.com wrote: Your comment mentions cookie .. should it be (cookie.userid)? That's further down :-) cfif isDefined(session.userid) cfif session.userid gt 0 cfset validated_id=#session.userid# /cfif cfelseif IsDefined(Cookie.StudentPortalUser) cfset validated_id=#Cookie.StudentPortalUser# cfset session.userid=#Cookie.StudentPortalUser# /cfif 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 ~| 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:346942 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: What the heck
What am I missing here
That would be quotes... isdefined('session.userid')
Prepare yourself for the suggestions of changing to structKeyExists(session,
'userid')
.:.:.:.:.:.:.:.:.:.:.:.:.
Bobby Hartsfield
http://acoderslife.com
http://cf4em.com
-Original Message-
From: Robert Harrison [mailto:rob...@austin-williams.com]
Sent: Monday, August 22, 2011 1:51 PM
To: cf-talk
Subject: What the heck
What am I missing here
Element USERID is undefined in SESSION.
The error occurred in
C:\inetpub\wwwroot\vaughn_college_portal\_main_securecheck.cfm: line 3
1 : !--- if the cookie is here, procees to dashboard ---
2 : cfset validated_id=0
3 : cfif isDefined(session.userid)
~|
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:346943
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: What the heck is happening during CFQUERY?
On Monday 09 Mar 2009, Claude Schneegans wrote: Each memo field needs some special treatment and only some specific words are retrieved. So I need to simply read all records and loop on them. Use maxrows/startrow to do it in blocks of 1000 or something. -- Tom Chiverton Helping to centrally seize attention-grabbing efficient B2C markets as part of the IT team of the year, '09 and '08 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.co ~| 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:320316 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
On Sun, Mar 8, 2009 at 11:42 PM, Claude Schneegans wrote: I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. That would be impossible for CF to do because: - if the resultset were never used the query would never be run on the server; - if the resultset were used multiple times the query would be run multiple times on the server; - the number of records would be unknown. If you want that, declare a cursor and deal with those consequences yourself. SELECT armeId, armTexte FROM armesArmoriaux The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? I do not think 2 ms per record is all that much for Access. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC driver? There is a reason the docs warn not to use ODBC But you might be trashing memory as well.. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:320254 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: What the heck is happening during CFQUERY?
That doesn't work because getArmes contains only a count of the records in armesArmoriaux, not the actual records. -Original Message- From: Al Musella, DPM [mailto:muse...@virtualtrials.com] Sent: Sunday, March 08, 2009 7:37 PM To: cf-talk Subject: Re: What the heck is happening during CFQUERY? Change it to this and the time should go down by a factor of about 100,000 : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT count (*) as N FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.n#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT ~| 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:320258 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
Yeah, but what are you going to do with 300,000 actual records in a single recordset? In his original post, Claude never specified what the requirements of his CFQUERY were - if we knew what he was trying to achieve, we could provide better advice on how to do it... On 9 Mar 2009, at 13:44, Billy Cox wrote: That doesn't work because getArmes contains only a count of the records in armesArmoriaux, not the actual records. -Original Message- From: Al Musella, DPM [mailto:muse...@virtualtrials.com] Sent: Sunday, March 08, 2009 7:37 PM To: cf-talk Subject: Re: What the heck is happening during 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:320260 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
More specifically, what are you going to do with 300,000 ntext fields ... that could potentially be many GB of data, which the server will be holding in active memory. As a general rule, I leave my ntext fields out of any query that's pulling a list of more than a few items. ~| 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:320263 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
It gets a connection from the pool, queries the DB, retrieves all the data and finally returns the connection to the pool. Well, I'm stunned. I was sure CF was better designed than that. I thought that data was retrieved as loops were going. Obviously course, with 300k records, CF is reading all data from disk, from a system especially designed to handle and retrieve data, put it back on disk, and then gets it back again into memory as loops goes. This is ridiculous. Even dBase was designed to retrieve data by chunks only when needed. I can't believe this. ~| 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:320265 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
I don't think this is unexpected behaviour It is not only unexpected, it is completely retarded. All ODBC/JDBC functions are designed so the database can be connected, then the SQL statement be compiled, then data retrieved row by row, as needed. Even dBase, Clipper, Foxpro worked this way. - you just need to examine what you actually require from the query Well, I made a request to get only the record Id, it take only 6 seconds, then I read again each record with the data I need in the loop. I am creating an index on words found in memo fields, and I need to loop on all records in the table. I'm doing this once for all on my development server, I don't care if it takes hours, but 10 min, just to create the query, it looked like infinity to me. ~| 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:320268 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
There are a number of reasons why running the select directly in Access may appear much faster. 1) You are probably running it locally so there is no costly transfer of data between servers. I'm running the CF app locally also, so the difference does not come from connexion time. Anywa, the result is just a 2 lines message. 2) In my experience, Access spools the data behind the scenes and only loads in the records which are visible Of course, and there is nothing extraordinary there : ALL database systems work this way. They will get the records only when needed, and ODBC also has also been designed in that purpose. ~| 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:320269 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Change it to this and the time should go down by a factor of about 100,000 Thanks, you bet this is what I would have done if I only needed the number of records ;-) The output of recordCount was there only for illustration purpose. ~| 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:320270 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 4:18 PM, Claude Schneegans wrote: All ODBC/JDBC functions are designed so the database can be connected, then the SQL statement be compiled, then data retrieved row by row, as needed. CF just needs all the records all the time because that is the only way to get a recordcount. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:320271 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
More specifically, what are you going to do with 300,000 ntext fields I am creating a cross table index on word found in memo fields. Each memo field needs some special treatment and only some specific words are retrieved. So I need to simply read all records and loop on them. I found another way to read records one at a time. This is a one time job on a database. ~| 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:320272 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
- if the resultset were never used the query would never be run on the server; Well, if the result set is not used, what is the advantage of having the query run anyway? - if the resultset were used multiple times the query would be run multiple times on the server; If the result set is too large to fit in memory, it will be rewritten and reread from disk every time anyway. - the number of records would be unknown. Most of the time, one only needs to know if there are records or not. This can be checked reading the first record only. May be some parameter in CFQUERY (ie RECORDS=onTheFly) could be used to create on the fly queries. It will be up to the programmer to use it if he expects the query to generate lots of data. In that sense, the parameter blockFactor in CFQUERY is missleading: it says Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) So I thought that CF was getting rows one at a time. Apparently, it is getting all rows one at a time in the same time ;-) Doesn't make much sense to me ;-) ~| 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:320273 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
CF just needs all the records all the time because that is the only way to get a recordcount. While I don't think that is quite true, I do think there is a good reason for having the resultset downloaded at once. ColdFusion expects you to use the recordset within the single request and this differs from an offline application where it can make sense to keep a connection open to a resultset. Indeed, while the initial query may take longer, it is perhaps better performing than hitting the db on each iteration within the request. Thoughts? Dominic ~| 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:320274 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
just out of curiosity: did you try experimenting with BLOCKFACTOR attribute of cfquery? i am just curious, since you already are retrieving such a large dataset, if using blockfactor makes any difference at all on processing time... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Claude Schneegans wrote: Change it to this and the time should go down by a factor of about 100,000 Thanks, you bet this is what I would have done if I only needed the number of records ;-) The output of recordCount was there only for illustration purpose. ~| 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:320275 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
IIRC, blockFactor is only relevant on the Oracle drivers, and it refers specifically to how Oracle expects to batch and return large recordsets. If Oracle is allowed to spool out large recordsets without returning them in blocks, it will often spin the DB server out of threads, which then cascades to the waiting app threads ... and then things die. Blockfactor, therefore, controls how the DB server batch responds to the request, but it does not impact how CF brings data back into its CFQUERY struct. ~| 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:320277 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
because that is the only way to get a recordcount. A big price in efficiency to pay for something we use only sometimes. Most of the time, we only need to know if there are records or not. ~| 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:320279 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
If that's all you need to know, use SELECT TOP 1... On Mon, Mar 9, 2009 at 11:05 AM, Claude Schneegans schneeg...@internetique.com wrote: because that is the only way to get a recordcount. A big price in efficiency to pay for something we use only sometimes. Most of the time, we only need to know if there are records or not. ~| 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:320280 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
What we are really talking about here is having the right tool for the job. dBase, Clipper and FoxPro are all database management systems, and so are designed to work efficiently in examples like the one you are citing. ColdFusion is a Web Application server, designed to interact with a database to deliver web content. By the very nature of Web applications, you wouldn't want to be leaving open database connections all over the place - the surest way to crash a server under heavy load... Really, all the heavy lifting should be done within the database itself; but out of preference I don't think I'd be using Acces for this! However, you can do what you want using ColdFusion; I've done similar stuff in the past when the updating has required extra ColdFusion logic. I would just recommend doing it in chunks of data - maybe 5000 records at a time? Grab the first 5000 records; process them; then do the next 5000... I don't think this is unexpected behaviour It is not only unexpected, it is completely retarded. All ODBC/JDBC functions are designed so the database can be connected, then the SQL statement be compiled, then data retrieved row by row, as needed. Even dBase, Clipper, Foxpro worked this way. ~| 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:320282 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Indeed, while the initial query may take longer, it is perhaps better performing than hitting the db on each iteration within the request. Thoughts? Perfectly right for small result sets. But as soon as the virtual memory must be used, each record will generate some read-write-reread action, pretty bad on performance indeed. IMHO, CF should should be able to buffer the record set, ie. read a certain number of records until a certain amount of memory is full, then do as usual if the whole record set fits in memory, and read the next buffer when needed. ~| 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:320284 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
did you try experimenting with BLOCKFACTOR attribute of cfquery? i am just curious, since you already are retrieving such a large dataset, if using blockfactor makes any difference at all on processing time... The blockfactor is supposed to be one by default, but I tried it anyway, and no, it makes no difference. The blockfactor is either serving some other purpose and then not properly documented, or it is inoperant. ~| 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:320285 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
blockFactor is only relevant on the Oracle drivers CF 5 doc says This parameter applies to ORACLE native database drivers and to ODBC drivers This is ambiguous, If it applies only to Oracle, it should be stated: This parameter applies to ORACLE native database drivers and to ORACLE ODBC drivers And CF 7 : Might not be supported by some database systems. with no other details. IMO everything but ORACLE should not be called some database systems either... ;-/ ~| 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:320286 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 4:44 PM, Claude Schneegans wrote: - if the resultset were never used the query would never be run on the server; Well, if the result set is not used, what is the advantage of having the query run anyway? A select isn't necessarily idempotent (if CF were able to determine it was a select in the first place).. - if the resultset were used multiple times the query would be run multiple times on the server; If the result set is too large to fit in memory, it will be rewritten and reread from disk every time anyway. But if the query isn't deterministic you wouldn't necessarily get the same results each time. May be some parameter in CFQUERY (ie RECORDS=onTheFly) could be used to create on the fly queries. It will be up to the programmer to use it if he expects the query to generate lots of data. I'm not really interested in such a parameter, but if you submit it as a feature request you should consider naming it usecursor or something. That explains the way it would work much better. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:320290 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 5:05 PM, Claude Schneegans wrote: because that is the only way to get a recordcount. A big price in efficiency to pay for something we use only sometimes. Most of the time, we only need to know if there are records or not. That is what maxrows is for. The query will still execute to completion on the database server, but CF will stop fetching results after it has reached its maximum. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:320291 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
On Mon, Mar 9, 2009 at 4:41 PM, Dominic Watson wrote: CF just needs all the records all the time because that is the only way to get a recordcount. While I don't think that is quite true It is not strictly true. You can declare a cursor, do a move end, read from the metadata how many records you moved forward, then do a move start and wait for the commands to fetch rows. But that is hardly portable and will perform even worse then just fetching everything into a detached recordset on the client. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| 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:320292 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
if cf did not get the full dataset from the db, would it still be able to show query debugging / execution times / etc, and would cf monitor still work and be able to show you long-running/unoptimized queries / etc? Azadi Saryev Sabai-dee.com http://www.sabai-dee.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:320293 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
(if CF were able to determine it was a select in the first place) Well, it must be able somehow to determine the query returns data, otherwise, how would it create a structure from any result set? ~| 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:320294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is happening during CFQUERY?
A cfquery tag can potentially contain any number of SQL commands which may or may not return a result set. CF doesn't do any parsing of the SQL contained within, it just runs it. (The only exception being the SELECT, UPDATE, DELETE, DROP etc security set up in your datasource settings-- but that's not bullet proof) Regardless, CF wouldn't know if the cfquery was going to return a result set until after it had sent the commands to the database for execution and waited for the results to come back, and that would sort of defeat the purpose, wouldn't it? ~Brad Original Message Subject: Re: What the heck is happening during CFQUERY? From: Claude Schneegans schneeg...@internetique.com Date: Mon, March 09, 2009 1:11 pm To: cf-talk cf-talk@houseoffusion.com (if CF were able to determine it was a select in the first place) Well, it must be able somehow to determine the query returns data, otherwise, how would it create a structure from any result set? ~| 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:320296 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Regardless, CF wouldn't know if the cfquery was going to return a result set until after it had sent the commands to the database for execution and waited for the results to come back, and that would sort of defeat the purpose, wouldn't it? No, because the driver does not return the result set, but only the presence of a result set. It is then up to the application to get rows one by one: See http://msdn.microsoft.com/en-us/library/ms711012(VS.85).aspx In particular: If the statement is a SELECT statement, the application calls a CLI function to return the results in application buffers. Typically, this function returns one row or one column of data at a time. At least, this is how it works for ODBC, with native drivers, I don't know. Furthermore, if it is a SELECT statement, and if the application calls a CLI function to return the results, one may suppose that the application must know one way or another that there is some result set to get. ~| 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:320302 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
It seems like you know a bit about databases. Why not write the looping code in the database using T-SQL, VB, or .NET and keep CF out of the picture entirely? What is CF providing that makes you want to use it for this index building task? It doesn't sound like you are serving up Web pages to users with this code, which is what CF is designed to do. -Mike Chabot ~| 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:320310 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
Yes, CFQUERY reads all the content of the query. It gets a connection from the pool, queries the DB, retrieves all the data and finally returns the connection to the pool. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/3/9 Claude Schneegans schneeg...@internetique.com: Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? If I run the same query directly from Access, it runs so fast, I cannot even measure the time it takes. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC drive ~| 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:320240 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
The CFQUERY connects to the database, returns the whole of the resulting query result into the specified variable, then closes the connection; the variable is held in memory for the lifetime of the request. So, if you run a query that, as in this case, returns more than 300,000 records, there is going to be a significant overhead as the data is read out of the database and into ColdFusion. Especially if one of those fields is a memo field. I don't think this is unexpected behaviour - you just need to examine what you actually require from the query, and tailor your SQL to that. It is highly unlikely that you actually need to work with 300,000 records at one time... Seb Duggan Web ColdFusion Developer e: s...@sebduggan.com t: 07786 333184 w: http://sebduggan.com On 8 Mar 2009, at 22:42, Claude Schneegans wrote: Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/ CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? If I run the same query directly from Access, it runs so fast, I cannot even measure the time it takes. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC driver? ~| 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:320241 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is happening during CFQUERY?
By the time the cfquery tag has finished executing the entire data set has been returned from the database and is loaded into memory in ColdFusion. If the cfquery tag is taking a very long time to complete then: 1) The actual SQL is taking a long time to complete. 2) and/or you are returning a very large amount of data to the ColdFusion server 3) and/or you have very slow connection between your SQL server and your CF server. 302 thousands records is quite a bit-- especially if you have a memo field in there. Can you even add indexes in Access? If so, you might want to consider it. If you just want a count, then just do select count(1) and only a single record has to pass from your database to CF. There are a number of reasons why running the select directly in Access may appear much faster. 1) You are probably running it locally so there is no costly transfer of data between servers. 2) In my experience, Access spools the data behind the scenes and only loads in the records which are visible on the screen which gives your query a very fast turn-around and then it lazy-loads the data in as your scroll down. ~Brad - Original Message - From: Claude Schneegans schneeg...@internetique.com To: cf-talk cf-talk@houseoffusion.com Sent: Sunday, March 08, 2009 5:42 PM Subject: What the heck is happening during CFQUERY? Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 ~| 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:320242 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: What the heck is happening during CFQUERY?
Change it to this and the time should go down by a factor of about 100,000 : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT count (*) as N FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.n#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT Hi, I've always thought that all what CFQUERY was doing was to create some connection to the database, and then the actual content of all records would be read as needed during some loop on the result set. I have this statement on some Access database : CFQUERY NAME=getArmes DATASOURCE=Armoriaux SELECT armeId, armTexte FROM armesArmoriaux /CFQUERY CFOUTPUTgetArmes.recordCount = #getArmes.recordCount#BR cfquery.ExecutionTime = #cfquery.ExecutionTime#BR/CFOUTPUTCFABORT The query semms to take for ever. Actually, the output says: getArmes.recordCount = 303203 cfquery.ExecutionTime = 614469 More than 10 min. just to settle a result set? That does not make sense. Ok, armTexte is a memo field, but is CFQUERY supposed to read all of them? If I run the same query directly from Access, it runs so fast, I cannot even measure the time it takes. So, what the heck CF is doing within CFQUERY? Read all content? That does not make sense. Is the problem with CF, or the ODBC driver? ~| 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:320243 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: What the heck is wrong with this Query?
Hi Les, You could use ListValueCountNoCase if there is some reason for not letting the dbms do this aggregation for you. On the whole the database engine is better (i.e. faster) at this kind of data manipulation than CF is. What I would suggest is that your select gets the country and count of distinct categories and then you put these in a structure where the country_name is the key and the value is the count. This will let you access the relevant count directly without any looping over the query or a long list. heres how to construct the structure from a select that gives country and a cat count (as per my previous reply). cfset stCtryCat = structNew() cfloop query=qctrycat cfset stCtryCat[country] = catcount /cfloop cfoutput#stCtryCat[DE]#/cfoutput Hope that helps. Michael Traher Systems Manager -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 20:35 To: CF-Talk Subject: RE: What the heck is wrong with this Query? ::: CFSET egypt_Count = ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),egypt) :: De-dupe the list then count it How you you do that, exactly? I need to be able to use #InsertNameOfCountryHere_Count# anywhere on the page as needed - for each of some 30 different countries ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is wrong with this Query?
you need to add the other columns in the group by clause... cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_CATEGORY, ID, PA_COUNTRY /cfquery That should work... Mike - Original Message - From: Les Mizzell [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, August 04, 2003 9:38 AM Subject: What the heck is wrong with this Query? Given a small Access database containing: ID, PA_COUNTRY, PA_CATEGORY cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_CATEGORY /cfquery I get the following error: You tried to execute a query that does not include the specified expression 'PA_COUNTRY' as part of an aggregate function. If I remove the GROUP BY - it worksbut I need that in there! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is wrong with this Query?
cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_CATEGORY, ID, PA_COUNTRY /cfquery will work. I think that all of the columns in a select distinct need to be in the groupby also if I remember correctly. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 9:38 AM To: CF-Talk Subject: What the heck is wrong with this Query? Given a small Access database containing: ID, PA_COUNTRY, PA_CATEGORY cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_CATEGORY /cfquery I get the following error: You tried to execute a query that does not include the specified expression 'PA_COUNTRY' as part of an aggregate function. If I remove the GROUP BY - it worksbut I need that in there! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is wrong with this Query?
Just as a little more detail, since you were grouping your query by one column, the db didn't know what to do with the other columns, so like the others said, adding the other columns to the 'group by' fixes your error. But in other cases, you may need to use aggregate functions on fields that you don't want grouped, ie MIN, MAX, AVG, COUNT...etc, varies on what db you use hth, seth -Original Message- From: Bryan F. Hogan [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 9:45 AM To: CF-Talk Subject: RE: What the heck is wrong with this Query? cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_CATEGORY, ID, PA_COUNTRY /cfquery will work. I think that all of the columns in a select distinct need to be in the groupby also if I remember correctly. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 9:38 AM To: CF-Talk Subject: What the heck is wrong with this Query? Given a small Access database containing: ID, PA_COUNTRY, PA_CATEGORY cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_CATEGORY /cfquery I get the following error: You tried to execute a query that does not include the specified expression 'PA_COUNTRY' as part of an aggregate function. If I remove the GROUP BY - it worksbut I need that in there! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is wrong with this Query?
The query itself doesn't error out like this, but I'm not getting the needed results to count categories like I need: cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_COUNTRY, ID, PA_CATEGORY /cfquery CFSET egypt_Count = ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),egypt) This should give me the number of distinct categories for the distinct country, egypt. However, it's counting each individual instance, not the unique ones: This is a flat database - so, I'm trying to group the countries, and then count the number of unique catagories for each country... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is wrong with this Query?
De-dupe the list then count it :) -Jack -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 10:33 AM To: CF-Talk Subject: RE: What the heck is wrong with this Query? The query itself doesn't error out like this, but I'm not getting the needed results to count categories like I need: cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_COUNTRY, ID, PA_CATEGORY /cfquery CFSET egypt_Count = ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),egypt) This should give me the number of distinct categories for the distinct country, egypt. However, it's counting each individual instance, not the unique ones: This is a flat database - so, I'm trying to group the countries, and then count the number of unique catagories for each country... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is wrong with this Query?
Maybe you need to exclude ID, something like cfquery name=PA_CT datasource=phoenixart SELECT PA_COUNTRY, count(distinct PA_CATEGORY) as category_count FROM papers GROUP BY PA_COUNTRY /cfquery This will count the distinct categories, grouped by country. If you still have problems, please post a sample of data as this will clarify the problem. Michael Traher Systems Manager ICLP (London) Tel: UK +44 (0) 20 8256 9072 Fax: UK +44 (0) 20 8681 0234 This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of ICLP. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 15:33 To: CF-Talk Subject: RE: What the heck is wrong with this Query? The query itself doesn't error out like this, but I'm not getting the needed results to count categories like I need: cfquery name=PA_CT datasource=phoenixart SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_COUNTRY, ID, PA_CATEGORY /cfquery CFSET egypt_Count = ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),egypt) This should give me the number of distinct categories for the distinct country, egypt. However, it's counting each individual instance, not the unique ones: This is a flat database - so, I'm trying to group the countries, and then count the number of unique catagories for each country... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: What the heck is wrong with this Query?
::: CFSET egypt_Count = ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),egypt) :: De-dupe the list then count it How you you do that, exactly? I need to be able to use #InsertNameOfCountryHere_Count# anywhere on the page as needed - for each of some 30 different countries ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: What the heck is wrong with 4.5 and my queries?
For anyone interested in how I fixed the error I was getting using
Query_repeater and CF 4.5:
I simply removed the "maxrows" attribute from the code that actually runs
the query. I'm not sure why that was throwing back the "can't convert
999 to and integer" error, but deleting the attribute altogether
makes it run like a charm - like it did on 4.01.
Thanks for the tips, hints and advice.
Erika
- Original Message -
From: "Peter Tilbrook" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, April 18, 2000 9:09 PM
Subject: RE: What the heck is wrong with 4.5 and my queries?
The custom tag just might not be compatible with 4.5. I had to go back to
4.01 for a similar reason (but with a CFX).
-Original Message-
From: Erika Foster [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 19 April 2000 2:56 PM
To: [EMAIL PROTECTED]
Subject: What the heck is wrong with 4.5 and my queries?
The first problem I encountered after ugrading was this error:
~
Error Diagnostic Information
Cannot convert 99 to integer.
Please, check the ColdFusion manual for the allowed conversions between
data
types
The error occurred while processing an element with a general identifier
of
(CFQUERY), occupying document position (68:3) to (72:35) in the template
file C:\CFUSION\CustomTags\QUERY_Repeater.cfm.
~~~
When using query_repeater. This error never happened with 4.01.
Now, I'm getting the following error with the code below:
Error:
ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
query.
SQL = "INSERT INTO Inventory (SL, SLN, compound, manufacturer, activity,
product, NSN, ContainerSize, quantity, ro, rop) VALUES ('FL-1', 'abc',
'256', 'abc', '05A05', 'abc', 'abc', 'abc', 'a', 'abc', 'abc')"
Code:
cfloop index="i" FROM="1" TO= "10"
cfset Thissl = Evaluate("Form.sl" i)
cfset Thiscompound = Evaluate("Form.compound" i)
cfset Thisactivity = Evaluate("Form.activity" i)
cfset ThisManufacturer = Evaluate("Form.Manufacturer" i)
cfset ThisProduct = Evaluate("Form.Product" i)
cfset ThisNSN = Evaluate("Form.NSN" i)
cfset ThisContainerSize = Evaluate("Form.ContainerSize" i)
cfset ThisQuantity = Evaluate("Form.Quantity" i)
cfset ThisRO = Evaluate("Form.RO" i)
cfset ThisROP = Evaluate("Form.ROP" i)
cfset ThisSLN = Evaluate("Form.SLN" i)
cfif (Evaluate("Form.Product"i) NEQ "")
cfquery name="addrow" datasource="facilities"
INSERT INTO Inventory (SL, SLN, compound, manufacturer, activity,
product, NSN, ContainerSize, quantity, ro, rop)
VALUES ('#thissl#', '#thissln#', '#thiscompound#',
'#ThisManufacturer#', '#thisactivity#', '#thisproduct#', '#thisNSN#',
'#thiscontainersize#', '#thisquantity#', '#thisro#', '#thisrop#')
/cfquery
/cfif
/cfloop
Can someone help? I'm in the process of upgrading to 4.5.1 right now -
I'm
hovering at 89% complete but I'm tired and cranky and need this to work
tomorrow. Will the upgrade fix this? Anyone?
Thanks,
Erika
--
--
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: What the heck is wrong with 4.5 and my queries?
Make sure your database file is not read only
Duane
-Original Message-
From: Erika Foster [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 19, 2000 12:56 AM
To: [EMAIL PROTECTED]
Subject: What the heck is wrong with 4.5 and my queries?
The first problem I encountered after ugrading was this error:
~
Error Diagnostic Information
Cannot convert 99 to integer.
Please, check the ColdFusion manual for the allowed conversions between data
types
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (68:3) to (72:35) in the template
file C:\CFUSION\CustomTags\QUERY_Repeater.cfm.
~~~
When using query_repeater. This error never happened with 4.01.
Now, I'm getting the following error with the code below:
Error:
ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
query.
SQL = "INSERT INTO Inventory (SL, SLN, compound, manufacturer, activity,
product, NSN, ContainerSize, quantity, ro, rop) VALUES ('FL-1', 'abc',
'256', 'abc', '05A05', 'abc', 'abc', 'abc', 'a', 'abc', 'abc')"
Code:
cfloop index="i" FROM="1" TO= "10"
cfset Thissl = Evaluate("Form.sl" i)
cfset Thiscompound = Evaluate("Form.compound" i)
cfset Thisactivity = Evaluate("Form.activity" i)
cfset ThisManufacturer = Evaluate("Form.Manufacturer" i)
cfset ThisProduct = Evaluate("Form.Product" i)
cfset ThisNSN = Evaluate("Form.NSN" i)
cfset ThisContainerSize = Evaluate("Form.ContainerSize" i)
cfset ThisQuantity = Evaluate("Form.Quantity" i)
cfset ThisRO = Evaluate("Form.RO" i)
cfset ThisROP = Evaluate("Form.ROP" i)
cfset ThisSLN = Evaluate("Form.SLN" i)
cfif (Evaluate("Form.Product"i) NEQ "")
cfquery name="addrow" datasource="facilities"
INSERT INTO Inventory (SL, SLN, compound, manufacturer, activity,
product, NSN, ContainerSize, quantity, ro, rop)
VALUES ('#thissl#', '#thissln#', '#thiscompound#',
'#ThisManufacturer#', '#thisactivity#', '#thisproduct#', '#thisNSN#',
'#thiscontainersize#', '#thisquantity#', '#thisro#', '#thisrop#')
/cfquery
/cfif
/cfloop
Can someone help? I'm in the process of upgrading to 4.5.1 right now - I'm
hovering at 89% complete but I'm tired and cranky and need this to work
tomorrow. Will the upgrade fix this? Anyone?
Thanks,
Erika
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: What the heck is wrong with 4.5 and my queries?
The custom tag just might not be compatible with 4.5. I had to go back to
4.01 for a similar reason (but with a CFX).
-Original Message-
From: Erika Foster [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 19 April 2000 2:56 PM
To: [EMAIL PROTECTED]
Subject: What the heck is wrong with 4.5 and my queries?
The first problem I encountered after ugrading was this error:
~
Error Diagnostic Information
Cannot convert 99 to integer.
Please, check the ColdFusion manual for the allowed conversions between data
types
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (68:3) to (72:35) in the template
file C:\CFUSION\CustomTags\QUERY_Repeater.cfm.
~~~
When using query_repeater. This error never happened with 4.01.
Now, I'm getting the following error with the code below:
Error:
ODBC Error Code = S1000 (General error)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable
query.
SQL = "INSERT INTO Inventory (SL, SLN, compound, manufacturer, activity,
product, NSN, ContainerSize, quantity, ro, rop) VALUES ('FL-1', 'abc',
'256', 'abc', '05A05', 'abc', 'abc', 'abc', 'a', 'abc', 'abc')"
Code:
cfloop index="i" FROM="1" TO= "10"
cfset Thissl = Evaluate("Form.sl" i)
cfset Thiscompound = Evaluate("Form.compound" i)
cfset Thisactivity = Evaluate("Form.activity" i)
cfset ThisManufacturer = Evaluate("Form.Manufacturer" i)
cfset ThisProduct = Evaluate("Form.Product" i)
cfset ThisNSN = Evaluate("Form.NSN" i)
cfset ThisContainerSize = Evaluate("Form.ContainerSize" i)
cfset ThisQuantity = Evaluate("Form.Quantity" i)
cfset ThisRO = Evaluate("Form.RO" i)
cfset ThisROP = Evaluate("Form.ROP" i)
cfset ThisSLN = Evaluate("Form.SLN" i)
cfif (Evaluate("Form.Product"i) NEQ "")
cfquery name="addrow" datasource="facilities"
INSERT INTO Inventory (SL, SLN, compound, manufacturer, activity,
product, NSN, ContainerSize, quantity, ro, rop)
VALUES ('#thissl#', '#thissln#', '#thiscompound#',
'#ThisManufacturer#', '#thisactivity#', '#thisproduct#', '#thisNSN#',
'#thiscontainersize#', '#thisquantity#', '#thisro#', '#thisrop#')
/cfquery
/cfif
/cfloop
Can someone help? I'm in the process of upgrading to 4.5.1 right now - I'm
hovering at 89% complete but I'm tired and cranky and need this to work
tomorrow. Will the upgrade fix this? Anyone?
Thanks,
Erika
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
