RE: Why would this query not return the correct data?
That's the actual sql... a simple statement. Now this is strange... I ran a little test. I changed the event_day data directly in the database from 1,1,1,3,4,6 or whatever it was and added one to each digit and made the event_day data 2,2,2,4,5,7. This data is in a MySQL db with a field type of tiny integer. (I then convert this into a day_of_week using MySQL) Anyway, when I run the same query in the MySQL editor, I get the expected results, 2,4,5,7. However, when I run the query via a cf page using... cfquery name=get_days datasource=his_anointing select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop cfdump var=#get_days# I still get 1,1,1,1 from both the looped output and the cfdump. I even went back and changed the text color on the page to make sure I'm running the correct page in the browser. It's the right one, because the text color is now red. I changed the database table data and changed one of the 2's to a 3 to see if the output and cfdump changed to make sure I'm running the query on the same database in both places... yep, now the output from the editor reads 2,3,4,5,7, but the query output and cfdump reads 1,1,1,1,1. It just added another 1 to the original 4 1's. So the 1's can't be representing the data; there are no 1's in the table now. It's as if it's stuck on a rowcount of 1 or something; or perhaps...no it couldn't be...a value of 1. But that wouldn't make any sense. Is it a variable type (tinyint) problem? I don't see why a variable type of tinyint wouldn't work. I just need to store numbers 1-7 to convert to a day of the week. Which worked fine in MySQL 4.1 and CF 4.5. ??? Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 10:17 PM To: CF-Talk Subject: Re: Why would this query not return the correct data? is this select distinct event_day from weekly_schedule order by event_day your actual sql statement, or just a 'simplified' version for the mailing list? ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291085 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Why would this query not return the correct data?
now that IS strange... is there some query caching involved??? i have even reproduced your table and query on my CF8/MySQL5 setup, and it returns correct data... does your cfdump still return 1,1,1,1 ??? did you try running: cfoutput query=get_days #get_days.event_day#br/ /cfoutput (no cfloop) Azadi Rick Faircloth wrote: That's the actual sql... a simple statement. Now this is strange... I ran a little test. I changed the event_day data directly in the database from 1,1,1,3,4,6 or whatever it was and added one to each digit and made the event_day data 2,2,2,4,5,7. This data is in a MySQL db with a field type of tiny integer. (I then convert this into a day_of_week using MySQL) Anyway, when I run the same query in the MySQL editor, I get the expected results, 2,4,5,7. However, when I run the query via a cf page using... cfquery name=get_days datasource=his_anointing select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop cfdump var=#get_days# I still get 1,1,1,1 from both the looped output and the cfdump. I even went back and changed the text color on the page to make sure I'm running the correct page in the browser. It's the right one, because the text color is now red. I changed the database table data and changed one of the 2's to a 3 to see if the output and cfdump changed to make sure I'm running the query on the same database in both places... yep, now the output from the editor reads 2,3,4,5,7, but the query output and cfdump reads 1,1,1,1,1. It just added another 1 to the original 4 1's. So the 1's can't be representing the data; there are no 1's in the table now. It's as if it's stuck on a rowcount of 1 or something; or perhaps...no it couldn't be...a value of 1. But that wouldn't make any sense. Is it a variable type (tinyint) problem? I don't see why a variable type of tinyint wouldn't work. I just need to store numbers 1-7 to convert to a day of the week. Which worked fine in MySQL 4.1 and CF 4.5. ??? Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 10:17 PM To: CF-Talk Subject: Re: Why would this query not return the correct data? is this select distinct event_day from weekly_schedule order by event_day your actual sql statement, or just a 'simplified' version for the mailing list? ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291089 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Why would this query not return the correct data?
Rick, This might sound really, really stupid... but is your datasource pointing to the a different version of your database? Also, make sure that caching is turned off within the admin for things like class files. Matthew Williams Geodesic GraFX www.geodesicgrafx.com/blog ~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291088 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Why would this query not return the correct data?
I know this does not solve the actual problem but if all you need to do is convert 1-7 to the day of the week you could just make a query result using the QueryNew() function and avoid querying the database altogether. Are you 100% sure this is the only query in the site returning incorrect data? On 10/15/07, Rick Faircloth [EMAIL PROTECTED] wrote: numbers 1-7 to convert to a day of the week. Which worked fine in MySQL 4.1 and CF 4.5. -- Aaron Rouse http://www.happyhacker.com/ ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291091 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
I doubt it would make any difference, if the cfdump has the wrong values then the problem would be outside of how he is looping over the query and referencing the values. On 10/15/07, Azadi Saryev [EMAIL PROTECTED] wrote: now that IS strange... is there some query caching involved??? i have even reproduced your table and query on my CF8/MySQL5 setup, and it returns correct data... does your cfdump still return 1,1,1,1 ??? did you try running: cfoutput query=get_days #get_days.event_day#br/ /cfoutput (no cfloop) Azadi -- Aaron Rouse http://www.happyhacker.com/ ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291092 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Why would this query not return the correct data?
Could CF8 be converting the Tiny Integer type to say a bit/boolean type? Could you quickly test using an equivalent table but use integer instead of tiny integer? -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, October 15, 2007 8:02 AM To: CF-Talk Subject: RE: Why would this query not return the correct data? That's the actual sql... a simple statement. Now this is strange... I ran a little test. I changed the event_day data directly in the database from 1,1,1,3,4,6 or whatever it was and added one to each digit and made the event_day data 2,2,2,4,5,7. This data is in a MySQL db with a field type of tiny integer. (I then convert this into a day_of_week using MySQL) Anyway, when I run the same query in the MySQL editor, I get the expected results, 2,4,5,7. However, when I run the query via a cf page using... cfquery name=get_days datasource=his_anointing select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop cfdump var=#get_days# .I still get 1,1,1,1 from both the looped output and the cfdump. I even went back and changed the text color on the page to make sure I'm running the correct page in the browser. It's the right one, because the text color is now red. I changed the database table data and changed one of the 2's to a 3 to see if the output and cfdump changed to make sure I'm running the query on the same database in both places... yep, now the output from the editor reads 2,3,4,5,7, but the query output and cfdump reads 1,1,1,1,1. It just added another 1 to the original 4 1's. So the 1's can't be representing the data; there are no 1's in the table now. It's as if it's stuck on a rowcount of 1 or something; or perhaps...no it couldn't be...a value of 1. But that wouldn't make any sense. Is it a variable type (tinyint) problem? I don't see why a variable type of tinyint wouldn't work. I just need to store numbers 1-7 to convert to a day of the week. Which worked fine in MySQL 4.1 and CF 4.5. ??? Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 10:17 PM To: CF-Talk Subject: Re: Why would this query not return the correct data? is this select distinct event_day from weekly_schedule order by event_day your actual sql statement, or just a 'simplified' version for the mailing list? ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291094 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
Could CF8 be converting the Tiny Integer type to say a bit/boolean type? I was thinking about this, but in this case, all even values would converted to zeros, not ones -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291114 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Why would this query not return the correct data?
@ Azadi: The cfdump reports no query caching. Even after I manually changed the data in the database to exclude the integer 1, I still get all 1's ! What datatype did you assign to event_day? If it was tinyint, did you give it a length of 1? @ Matthew: I changed two things to make sure I wasn't accessing the wrong database or cfm page. I change the text to red and that worked. I also changed the integers for event_day I the database and they editor query returns the new and correct information. CFDump reports no query caching. @ Aaron: I haven't checked the rest of the site for errors explicitly, but I haven't noticed any problems yet. I'll expand the error search soon if this isn't resolved. @ Aaron: Yes, the cfdump shows consistent data. I get same wrong result whether I cfoutput the query or loop the query. @ Andrew: Your theory could be correct about the bit/Boolean datatype. I had assigned a tinyint datatype with a length of 1. Which would fit the definition for a Boolean and also my single digit day of the week number requirements. However, when I changed the datatype to integer and allowed it to have its full length of 11, I got back the correct results from my query, the cfdump, and sql editor's query. So the issue seems to be solved... but the question remains: Why did my datatype and length no work? Just wanted to say THANKS! to everyone who has helped solve this. Now I really want to understand this behavior so I don't run into unexpected problems in the future. Ideas? Rick -Original Message- From: Azadi Saryev [mailto:[EMAIL PROTECTED] Sent: Monday, October 15, 2007 8:56 AM To: CF-Talk Subject: Re: Why would this query not return the correct data? now that IS strange... is there some query caching involved??? i have even reproduced your table and query on my CF8/MySQL5 setup, and it returns correct data... does your cfdump still return 1,1,1,1 ??? did you try running: cfoutput query=get_days #get_days.event_day#br/ /cfoutput (no cfloop) Azadi ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291115 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Why would this query not return the correct data?
It could be more simple... All non-zero values are true. -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Monday, October 15, 2007 10:57 AM To: CF-Talk Subject: Re: Why would this query not return the correct data? Could CF8 be converting the Tiny Integer type to say a bit/boolean type? I was thinking about this, but in this case, all even values would converted to zeros, not ones -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291119 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
When accessing MySQL 5 from Java (i.e. when using JDBC) all TINYINT(1) fields are converted to Boolean values. Documented here: http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html You can prevent this behavior by adding a Connection String to your ColdFusion datasource (in Advanced Settings): tinyInt1isBit=false This will cause the JDBC driver to treat TINYINT(1) the way you want it to - as tiny little integers instead of boolean true/false values. -tom- Rick Faircloth wrote: Hi, all. Here's the query: cfquery name=get_days datasource=#dsn# select distinct event_days from weekly schedule order by event_day /cfquery The data that should be returned if the query is looped is 1, 1, 1, 1, 2, 4, 6 However, when I run. cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop .. all I get is 1, 1, 1, 1.four ones, and that's it. What could I be doing wrong? I knew transitioning from CF 4.5 to CF 8 would present some issues, but gee whiz. Rick ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291132 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Why would this query not return the correct data?
Thanks, Tom! Rick -Original Message- From: Tom Donovan [mailto:[EMAIL PROTECTED] Sent: Monday, October 15, 2007 1:44 PM To: CF-Talk Subject: Re: Why would this query not return the correct data? When accessing MySQL 5 from Java (i.e. when using JDBC) all TINYINT(1) fields are converted to Boolean values. Documented here: http://dev.mysql.com/doc/refman/5.0/en/connector-j- reference-type-conversions.html You can prevent this behavior by adding a Connection String to your ColdFusion datasource (in Advanced Settings): tinyInt1isBit=false This will cause the JDBC driver to treat TINYINT(1) the way you want it to - as tiny little integers instead of boolean true/false values. -tom- ~| Check out the new features and enhancements in the latest product release - download the What's New PDF now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291151 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Why would this query not return the correct data?
Considering that your doing a select distinct, the correct output should be 1, 2, 4, 6 and not 1, 1, 1, 1, 2, 4, 6 as you first stated. So it looks like you're getting the very first value 4 times. Try to scope the output and see if that helps: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop Or better yet drop the cfloop all together and only use cfoutput cfoutput query=get_days#event_days#/cfoutput /Max On 10/14/07, Rick Faircloth [EMAIL PROTECTED] wrote: Follow-up: When I run the same query below in a query editor, the correct data, 1, 2, 4, 6 is returned. What wouldn't CF 8 return the same data with the same query? Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 1:40 AM To: CF-Talk Subject: Why would this query not return the correct data? Hi, all. Here's the query: cfquery name=get_days datasource=#dsn# select distinct event_days from weekly schedule order by event_day /cfquery The data that should be returned if the query is looped is 1, 1, 1, 1, 2, 4, 6 However, when I run. cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop .. all I get is 1, 1, 1, 1.four ones, and that's it. What could I be doing wrong? I knew transitioning from CF 4.5 to CF 8 would present some issues, but gee whiz. Rick ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291026 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Why would this query not return the correct data?
Follow-up: When I run the same query below in a query editor, the correct data, 1, 2, 4, 6 is returned. What wouldn't CF 8 return the same data with the same query? Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 1:40 AM To: CF-Talk Subject: Why would this query not return the correct data? Hi, all. Here's the query: cfquery name=get_days datasource=#dsn# select distinct event_days from weekly schedule order by event_day /cfquery The data that should be returned if the query is looped is 1, 1, 1, 1, 2, 4, 6 However, when I run. cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop .. all I get is 1, 1, 1, 1.four ones, and that's it. What could I be doing wrong? I knew transitioning from CF 4.5 to CF 8 would present some issues, but gee whiz. Rick ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291025 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop This is an old CF bug: Inside a loop, a column name alone always comes from the first record. You must specify the query name to get the current row: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291036 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Why would this query not return the correct data?
I'd do the latter and still prefix the column with the query name. But not for any reason other than I just do it that way all the time after becoming a scoping nazi due to a horrible project I overtook many years ago. On 10/14/07, Maximilian Nyman [EMAIL PROTECTED] wrote: Considering that your doing a select distinct, the correct output should be 1, 2, 4, 6 and not 1, 1, 1, 1, 2, 4, 6 as you first stated. So it looks like you're getting the very first value 4 times. Try to scope the output and see if that helps: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop Or better yet drop the cfloop all together and only use cfoutput cfoutput query=get_days#event_days#/cfoutput /Max On 10/14/07, Rick Faircloth [EMAIL PROTECTED] wrote: Follow-up: When I run the same query below in a query editor, the correct data, 1, 2, 4, 6 is returned. What wouldn't CF 8 return the same data with the same query? Rick -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 1:40 AM To: CF-Talk Subject: Why would this query not return the correct data? Hi, all. Here's the query: cfquery name=get_days datasource=#dsn# select distinct event_days from weekly schedule order by event_day /cfquery The data that should be returned if the query is looped is 1, 1, 1, 1, 2, 4, 6 However, when I run. cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop .. all I get is 1, 1, 1, 1.four ones, and that's it. What could I be doing wrong? I knew transitioning from CF 4.5 to CF 8 would present some issues, but gee whiz. Rick ~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291040 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Why would this query not return the correct data?
On 10/14/07, Rick Faircloth [EMAIL PROTECTED] wrote: @ All... This is just nuts... I can run this sql in an editor and get the correct results: select distinct event_day from weekly_schedule order by event_day And the correct results are 1, 2, 4, 6. But if I run that same sql in a CF8 cfquery, scoping or not scoping every variable, all I get is 1, 1, 1, 1. This was working perfectly in CF 4.5. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery I even put just the query and output commands on a separate page and still get 1, 1, 1, 1. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop I just don't get it... Any more ideas, anyone? :o/ you tried dumping the query? cfdump will be your very best friend. cfdump var=#get_days# see if it's the query that's not returning the data you want, or if it's the loop that's outputting it incorrectly. -- Charlie Griefer ...All the world shall be your enemy, Prince with a Thousand Enemies, and whenever they catch you, they will kill you. But first they must catch you, digger, listener, runner, prince with a swift warning. Be cunning and full of tricks and your people shall never be destroyed. ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291055 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
What does it show when you run a cfdump on the query? cfdump var=#get_days# / On 10/14/07, Rick Faircloth [EMAIL PROTECTED] wrote: @ All... This is just nuts... I can run this sql in an editor and get the correct results: select distinct event_day from weekly_schedule order by event_day And the correct results are 1, 2, 4, 6. But if I run that same sql in a CF8 cfquery, scoping or not scoping every variable, all I get is 1, 1, 1, 1. This was working perfectly in CF 4.5. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery I even put just the query and output commands on a separate page and still get 1, 1, 1, 1. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop I just don't get it... Any more ideas, anyone? :o/ Rick -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 11:49 AM To: CF-Talk Subject: Re: Why would this query not return the correct data? cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop This is an old CF bug: Inside a loop, a column name alone always comes from the first record. You must specify the query name to get the current row: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291054 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Why would this query not return the correct data?
@ All... This is just nuts... I can run this sql in an editor and get the correct results: select distinct event_day from weekly_schedule order by event_day And the correct results are 1, 2, 4, 6. But if I run that same sql in a CF8 cfquery, scoping or not scoping every variable, all I get is 1, 1, 1, 1. This was working perfectly in CF 4.5. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery I even put just the query and output commands on a separate page and still get 1, 1, 1, 1. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop I just don't get it... Any more ideas, anyone? :o/ Rick -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 11:49 AM To: CF-Talk Subject: Re: Why would this query not return the correct data? cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop This is an old CF bug: Inside a loop, a column name alone always comes from the first record. You must specify the query name to get the current row: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291053 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
Also if that shows the query itself is in fact pulling out the correct data then have you tried doing a cfoutput with the query attribute and eliminating the cfloop with the cfoutput within that? Something like: cfoutput query=Get_Days#get_days.event_day#br //cfoutput On 10/14/07, Aaron Rouse [EMAIL PROTECTED] wrote: What does it show when you run a cfdump on the query? cfdump var=#get_days# / On 10/14/07, Rick Faircloth [EMAIL PROTECTED] wrote: @ All... This is just nuts... I can run this sql in an editor and get the correct results: select distinct event_day from weekly_schedule order by event_day And the correct results are 1, 2, 4, 6. But if I run that same sql in a CF8 cfquery, scoping or not scoping every variable, all I get is 1, 1, 1, 1. This was working perfectly in CF 4.5. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery I even put just the query and output commands on a separate page and still get 1, 1, 1, 1. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop I just don't get it... Any more ideas, anyone? :o/ Rick -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 11:49 AM To: CF-Talk Subject: Re: Why would this query not return the correct data? cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop This is an old CF bug: Inside a loop, a column name alone always comes from the first record. You must specify the query name to get the current row: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finderproductID=1522loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291056 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Why would this query not return the correct data?
cfdump is returning the same wrong results... so strange. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 8:48 PM To: CF-Talk Subject: Re: Why would this query not return the correct data? On 10/14/07, Rick Faircloth [EMAIL PROTECTED] wrote: @ All... This is just nuts... I can run this sql in an editor and get the correct results: select distinct event_day from weekly_schedule order by event_day And the correct results are 1, 2, 4, 6. But if I run that same sql in a CF8 cfquery, scoping or not scoping every variable, all I get is 1, 1, 1, 1. This was working perfectly in CF 4.5. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery I even put just the query and output commands on a separate page and still get 1, 1, 1, 1. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop I just don't get it... Any more ideas, anyone? :o/ you tried dumping the query? cfdump will be your very best friend. cfdump var=#get_days# see if it's the query that's not returning the data you want, or if it's the loop that's outputting it incorrectly. -- Charlie Griefer ~| Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291065 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Why would this query not return the correct data?
is this select distinct event_day from weekly_schedule order by event_day your actual sql statement, or just a 'simplified' version for the mailing list? Rick Faircloth wrote: @ All... This is just nuts... I can run this sql in an editor and get the correct results: select distinct event_day from weekly_schedule order by event_day And the correct results are 1, 2, 4, 6. But if I run that same sql in a CF8 cfquery, scoping or not scoping every variable, all I get is 1, 1, 1, 1. This was working perfectly in CF 4.5. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery I even put just the query and output commands on a separate page and still get 1, 1, 1, 1. cfquery name=get_days datasource=#dsn# select distinct event_day from weekly_schedule order by event_day /cfquery cfloop query=get_days cfoutput#get_days.event_day#/cfoutputbr / /cfloop I just don't get it... Any more ideas, anyone? :o/ Rick -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Sunday, October 14, 2007 11:49 AM To: CF-Talk Subject: Re: Why would this query not return the correct data? cfloop query=get_days cfoutput#event_days#/cfoutput /cfloop This is an old CF bug: Inside a loop, a column name alone always comes from the first record. You must specify the query name to get the current row: cfloop query=get_days cfoutput#get_days.event_days#/cfoutput /cfloop -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291067 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
