Re: Query-of-Query Issue
>Does the original query passed to the CFC come from a database itself, or >are you creating it some other way? Ah, that was the answer Dave! I checked again and it does go through a function that rewrites the query, and that seems to be what is causing the problem, although it doesn't show up until I hit that QoQ line. Apparently the field type is getting lost along the way. MJS ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269606 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query-of-Query Issue
>The main problem with your situation is that there shouldn't be a list in a >database field. There should be a separate table that creates the >one-to-many or many-to-many relationship. By doing that, your problems >would be solved. No, that does not help much at all. Without going into details about the application, there's good reasons it's in a list here. MJS ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269605 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query-of-Query Issue
>Does the original query passed to the CFC come from a database itself, or >are you creating it some other way? It's a database query, although it does get manipulated along the way. This particular field however should be the same as what is pulled from the database. MJS ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269603 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query-of-Query Issue
To quote someone else: "Every time you store a comma-delimited list in a database field God kills a kitty." The main problem with your situation is that there shouldn't be a list in a database field. There should be a separate table that creates the one-to-many or many-to-many relationship. By doing that, your problems would be solved. Hope that helps. Matt On 2/12/07, Mary Jo Sminkey <[EMAIL PROTECTED]> wrote: > > I've run into a strange query-of-query issue and wondering if anyone else > has seen this or knows of a work-around. Basically, I'm passing a query into > a CFC and then running this QoQ: > > >SELECT DISTINCT Item_ID, Related_Items FROM arguments.theQuery > > > Related_Items is a text field with a list of IDs. The problem I am having > is that when this runs, if Related_Items just has a single ID (or none), it > works fine. But if it has several items in the list, the QoQ doesn't return > the list, it apparently is converting it to a date field. > > Any ideas? > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269600 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query-of-Query Issue
> I've run into a strange query-of-query issue and wondering if > anyone else has seen this or knows of a work-around. > Basically, I'm passing a query into a CFC and then running this QoQ: > > >SELECT DISTINCT Item_ID, Related_Items FROM > arguments.theQuery > > Related_Items is a text field with a list of IDs. The problem > I am having is that when this runs, if Related_Items just has > a single ID (or none), it works fine. But if it has several > items in the list, the QoQ doesn't return the list, it > apparently is converting it to a date field. Does the original query passed to the CFC come from a database itself, or are you creating it some other way? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269599 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query-of-Query Issue
I've run into a strange query-of-query issue and wondering if anyone else has seen this or knows of a work-around. Basically, I'm passing a query into a CFC and then running this QoQ: SELECT DISTINCT Item_ID, Related_Items FROM arguments.theQuery Related_Items is a text field with a list of IDs. The problem I am having is that when this runs, if Related_Items just has a single ID (or none), it works fine. But if it has several items in the list, the QoQ doesn't return the list, it apparently is converting it to a date field. Any ideas? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269598 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: odd query issue returning 0 results in CFMC 7.2
d'oH! :) On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote: > problem solved. forgot to commit new database changes. > > > well...i took a look at the query in the debugging info. then i took > > that query, which has the coldfusion variables parsed already, and ran > > it on the command line. so yeah, i know i'm using the same query. > > > > > > > how are you sure it's the same query? are there variables involved > > > > > in > > > the CFMX code? are you sure those variables are evaluating to what > > > you expect? > > > > > > On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote: > > > > i have a query that returns results when using sqlplus. however, > > > > > when i run the same query in cfmx there are 0 records returned. has > > > > > anyone experienced this issue? is this a bug? > > > > > > > > tia > > > > > > > > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268098 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: odd query issue returning 0 results in CFMC 7.2
problem solved. forgot to commit new database changes. > well...i took a look at the query in the debugging info. then i took > that query, which has the coldfusion variables parsed already, and ran > it on the command line. so yeah, i know i'm using the same query. > > > > how are you sure it's the same query? are there variables involved > > > in > > the CFMX code? are you sure those variables are evaluating to what > > you expect? > > > > On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote: > > > i have a query that returns results when using sqlplus. however, > > > when i run the same query in cfmx there are 0 records returned. has > > > anyone experienced this issue? is this a bug? > > > > > > tia > > > > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268097 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: odd query issue returning 0 results in CFMC 7.2
well...i took a look at the query in the debugging info. then i took that query, which has the coldfusion variables parsed already, and ran it on the command line. so yeah, i know i'm using the same query. > how are you sure it's the same query? are there variables involved > in > the CFMX code? are you sure those variables are evaluating to what > you expect? > > On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote: > > i have a query that returns results when using sqlplus. however, > when i run the same query in cfmx there are 0 records returned. has > anyone experienced this issue? is this a bug? > > > > tia > > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268096 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: odd query issue returning 0 results in CFMC 7.2
how are you sure it's the same query? are there variables involved in the CFMX code? are you sure those variables are evaluating to what you expect? On 1/30/07, Steve Dworman <[EMAIL PROTECTED]> wrote: > i have a query that returns results when using sqlplus. however, when i run > the same query in cfmx there are 0 records returned. has anyone experienced > this issue? is this a bug? > > tia > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268095 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
odd query issue returning 0 results in CFMC 7.2
i have a query that returns results when using sqlplus. however, when i run the same query in cfmx there are 0 records returned. has anyone experienced this issue? is this a bug? tia ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268094 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Odd Query issue - SOLVED
Thanks for all help all, turns out a trigger on the table was referencing the job_id field in a different table that was recently changed. All the queries work now. -Original Message- From: Brian Rinaldi [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 1:34 PM To: CF-Talk Subject: Re: Odd Query issue This may seem silly but did you pop this into query analyzer (assuming this is MS SQL...I didn't see you mention what database it is). I have found that when you get odd errors like this back from a query sometimes they are misreporting the error...if you try it in query analyzer you may get a clearer error message. My guess is the problem lies in the word satus and not in job_id...so try bracketing that keyword (which btw using Query Analyzer will highlight potential keyword conflicts for you too). Hope that helps. - Brian Rinaldi blog - http://www.remotesynthesis.com/blog CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist Boston CFUG - http://www.bostoncfug.org ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247811 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Odd Query issue
Looks like you have an update trigger on the update. "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Scott Mulholland <[EMAIL PROTECTED]> To: CF-Talk Sent: Wed Jul 26 20:29:47 2006 Subject: RE: Odd Query issue Yeah, its ms-sql, and yes, does seem silly that I hadn't done that yet ;) Query analyzer gave me the same result for the update but with a better error message, "Invalid column job_id, Procedure jobs_UTrig". Looks like it's an issue with one of my relationships/constraints. Thanks, Scott -Original Message- From: Brian Rinaldi [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 1:34 PM To: CF-Talk Subject: Re: Odd Query issue This may seem silly but did you pop this into query analyzer (assuming this is MS SQL...I didn't see you mention what database it is). I have found that when you get odd errors like this back from a query sometimes they are misreporting the error...if you try it in query analyzer you may get a clearer error message. My guess is the problem lies in the word satus and not in job_id...so try bracketing that keyword (which btw using Query Analyzer will highlight potential keyword conflicts for you too). Hope that helps. - Brian Rinaldi blog - http://www.remotesynthesis.com/blog CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist Boston CFUG - http://www.bostoncfug.org ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247810 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Odd Query issue
Yeah, its ms-sql, and yes, does seem silly that I hadn't done that yet ;) Query analyzer gave me the same result for the update but with a better error message, "Invalid column job_id, Procedure jobs_UTrig". Looks like it's an issue with one of my relationships/constraints. Thanks, Scott -Original Message- From: Brian Rinaldi [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 1:34 PM To: CF-Talk Subject: Re: Odd Query issue This may seem silly but did you pop this into query analyzer (assuming this is MS SQL...I didn't see you mention what database it is). I have found that when you get odd errors like this back from a query sometimes they are misreporting the error...if you try it in query analyzer you may get a clearer error message. My guess is the problem lies in the word satus and not in job_id...so try bracketing that keyword (which btw using Query Analyzer will highlight potential keyword conflicts for you too). Hope that helps. - Brian Rinaldi blog - http://www.remotesynthesis.com/blog CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist Boston CFUG - http://www.bostoncfug.org ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247808 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Odd Query issue
This may seem silly but did you pop this into query analyzer (assuming this is MS SQL...I didn't see you mention what database it is). I have found that when you get odd errors like this back from a query sometimes they are misreporting the error...if you try it in query analyzer you may get a clearer error message. My guess is the problem lies in the word satus and not in job_id...so try bracketing that keyword (which btw using Query Analyzer will highlight potential keyword conflicts for you too). Hope that helps. - Brian Rinaldi blog - http://www.remotesynthesis.com/blog CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist Boston CFUG - http://www.bostoncfug.org ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247786 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Odd Query issue
Does the update work in Query Analyser? Which DB is it again? What drivers you using (and what ColdFusion) "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Scott Mulholland <[EMAIL PROTECTED]> To: CF-Talk Sent: Wed Jul 26 17:42:27 2006 Subject: RE: Odd Query issue I just wrote these two tests and ran them, same result...the select works, the update gives "Invalid column name 'job_id'." Select * from jobs where job_id = 1 Update jobs set status = 0 where job_id = 1 Scott -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 3:02 PM To: CF-Talk Subject: RE: Odd Query issue Can you actually post the CFQuery codes so we can see if maybe there is something you are not seeing. . Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 1:23 PM To: CF-Talk Subject: Odd Query issue I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247784 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Odd Query issue
This may seem silly but did you pop this into query analyzer (assuming this is MS SQL...I didn't see you mention what database it is). I have found that when you get odd errors like this back from a query sometimes they are misreporting the error...if you try it in query analyzer you may get a clearer error message. My guess is the problem lies in the word satus and not in job_id...so try bracketing that keyword (which btw using Query Analyzer will highlight potential keyword conflicts for you too). Hope that helps. - Brian Rinaldi blog - http://www.remotesynthesis.com/blog CF Open Source List - http://www.remotesynthesis.com/cfopensourcelist Boston CFUG - http://www.bostoncfug.org On 7/26/06, Scott Mulholland <[EMAIL PROTECTED]> wrote: > > I tried with a qryparam yesterday, no change. Selecting job_id from jobs > works as well. > > If I run select * from information_schema.columns where table_name = > 'jobs' > the column comes back as well. > > This is one of those weird "it worked fine for yrs up until yesterday and > nothing in the code or table changed" things. I'm thinking something is > corrupt in the database. > > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 26, 2006 12:32 PM > To: CF-Talk > Subject: Re: Odd Query issue > > What happens if you put it into a cfqueryparam? > > What happens if you select job_id from jobs? > > > > > > > > "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, > Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, > Registered in England, Number 678540. It contains information which is > confidential and may also be privileged. It is for the exclusive use of > the > intended recipient(s). If you are not the intended recipient(s) please > note > that any form of distribution, copying or use of this communication or the > information in it is strictly prohibited and may be unlawful. If you have > received this communication in error please return it to the sender or > call > our switchboard on +44 (0) 20 89107910. The opinions expressed within > this > communication are not necessarily those expressed by Reed Exhibitions." > Visit our website at http://www.reedexpo.com > > -Original Message- > From: Scott Mulholland <[EMAIL PROTECTED]> > To: CF-Talk > Sent: Wed Jul 26 17:42:27 2006 > Subject: RE: Odd Query issue > > I just wrote these two tests and ran them, same result...the select works, > the update gives "Invalid column name 'job_id'." > > > Select * from jobs where job_id = 1 > > > > Update jobs set status = 0 where job_id = 1 > > > > Scott > > -Original Message- > From: Ben Nadel [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 25, 2006 3:02 PM > To: CF-Talk > Subject: RE: Odd Query issue > > Can you actually post the CFQuery codes so we can see if maybe there is > something you are not seeing. > > .. > Ben Nadel > www.bennadel.com > > -Original Message- > From: Scott Mulholland [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 25, 2006 1:23 PM > To: CF-Talk > Subject: Odd Query issue > > I have a query today that stopped working with an invalid column > error. The > database did not change. Select statements work on it but not updates. > > > > Select * from jobs where job_id = 1 - works fine > > > > Update jobs set status = 0 where job_id = 1 - results in Invalid column > name > 'job_id'. > > > > Any ideas what would cause this to just start happening? > > > > Thanks, > Scott > > > > > > > > > > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247783 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Odd Query issue
What happens when you try to do a delete statement that you know will fail (so you don't delete data): DELETE FROM jobs WHERE job_id != job_id This should never be true, but I am curious as to if it throws the same error. ... Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 1:16 PM To: CF-Talk Subject: RE: Odd Query issue I tried with a qryparam yesterday, no change. Selecting job_id from jobs works as well. If I run select * from information_schema.columns where table_name = 'jobs' the column comes back as well. This is one of those weird "it worked fine for yrs up until yesterday and nothing in the code or table changed" things. I'm thinking something is corrupt in the database. -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 12:32 PM To: CF-Talk Subject: Re: Odd Query issue What happens if you put it into a cfqueryparam? What happens if you select job_id from jobs? "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Scott Mulholland <[EMAIL PROTECTED]> To: CF-Talk Sent: Wed Jul 26 17:42:27 2006 Subject: RE: Odd Query issue I just wrote these two tests and ran them, same result...the select works, the update gives "Invalid column name 'job_id'." Select * from jobs where job_id = 1 Update jobs set status = 0 where job_id = 1 Scott -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 3:02 PM To: CF-Talk Subject: RE: Odd Query issue Can you actually post the CFQuery codes so we can see if maybe there is something you are not seeing. ... Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 1:23 PM To: CF-Talk Subject: Odd Query issue I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247779 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Odd Query issue
I tried with a qryparam yesterday, no change. Selecting job_id from jobs works as well. If I run select * from information_schema.columns where table_name = 'jobs' the column comes back as well. This is one of those weird "it worked fine for yrs up until yesterday and nothing in the code or table changed" things. I'm thinking something is corrupt in the database. -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 12:32 PM To: CF-Talk Subject: Re: Odd Query issue What happens if you put it into a cfqueryparam? What happens if you select job_id from jobs? "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Scott Mulholland <[EMAIL PROTECTED]> To: CF-Talk Sent: Wed Jul 26 17:42:27 2006 Subject: RE: Odd Query issue I just wrote these two tests and ran them, same result...the select works, the update gives "Invalid column name 'job_id'." Select * from jobs where job_id = 1 Update jobs set status = 0 where job_id = 1 Scott -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 3:02 PM To: CF-Talk Subject: RE: Odd Query issue Can you actually post the CFQuery codes so we can see if maybe there is something you are not seeing. .. Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 1:23 PM To: CF-Talk Subject: Odd Query issue I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247776 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Odd Query issue
Have you tried surrounding the "job_id" with brackets such that: Update jobs set status=0 where [job_id]=1 Perhaps, and I'm only stabbing in the dark here, job_id becomes a reserved word when submitting certain types of statements to your DBMS (where they might become jobs)? Try it and see. Cheers, Kris > I just wrote these two tests and ran them, same result...the select works, > the update gives "Invalid column name 'job_id'." > > > Select * from jobs where job_id = 1 > > > > Update jobs set status = 0 where job_id = 1 > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247771 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Odd Query issue
What happens if you put it into a cfqueryparam? What happens if you select job_id from jobs? "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -Original Message- From: Scott Mulholland <[EMAIL PROTECTED]> To: CF-Talk Sent: Wed Jul 26 17:42:27 2006 Subject: RE: Odd Query issue I just wrote these two tests and ran them, same result...the select works, the update gives "Invalid column name 'job_id'." Select * from jobs where job_id = 1 Update jobs set status = 0 where job_id = 1 Scott -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 3:02 PM To: CF-Talk Subject: RE: Odd Query issue Can you actually post the CFQuery codes so we can see if maybe there is something you are not seeing. . Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 1:23 PM To: CF-Talk Subject: Odd Query issue I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247770 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Odd Query issue
I just wrote these two tests and ran them, same result...the select works, the update gives "Invalid column name 'job_id'." Select * from jobs where job_id = 1 Update jobs set status = 0 where job_id = 1 Scott -Original Message- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 3:02 PM To: CF-Talk Subject: RE: Odd Query issue Can you actually post the CFQuery codes so we can see if maybe there is something you are not seeing. Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 1:23 PM To: CF-Talk Subject: Odd Query issue I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247768 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Query issue between MX 6 and MX 7
Yup, that is what I mean, the columns getting an empty string. -Original Message- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 10:35 AM To: CF-Talk Subject: Re: Query issue between MX 6 and MX 7 I assume what the "radically different result" you describe is that on CF 7, column C gets reverted to the empty string (from '3') when the second query is injected into column BB? Or is there something else? cheers, barneyb On 7/25/06, Trevor Orr <[EMAIL PROTECTED]> wrote: > I ran across an issue with queries in MX 6 and MX7, try this code in > MX > 6 and MX 7 and you get radically different results. Anyone know of a > reason for this or is it a bug. I sent this to Adobe but got no > response so maybe someone here can shed some light. > > > Here is the code: > > > > > > > > > > > > > > > > > > > > > > > > > Trevor Orr -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247695 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Odd Query issue
Can you actually post the CFQuery codes so we can see if maybe there is something you are not seeing. ... Ben Nadel www.bennadel.com -Original Message- From: Scott Mulholland [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 1:23 PM To: CF-Talk Subject: Odd Query issue I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247660 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Odd Query issue
Yeah, I checked that as well. Update is still allowed. I also queried the system tables by column name and the table does come back for column = 'job_id'. Very strange. -Original Message- From: Joseph Lamoree [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 2:01 PM To: CF-Talk Subject: Re: Odd Query issue Maybe someone changed your datasource configuration. -- Joseph Lamoree On 25 Jul 2006, at 10:22, Scott Mulholland wrote: > I have a query today that stopped working with an invalid column > error. The > database did not change. Select statements work on it but not > updates. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247641 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Odd Query issue
Maybe someone changed your datasource configuration. -- Joseph Lamoree On 25 Jul 2006, at 10:22, Scott Mulholland wrote: > I have a query today that stopped working with an invalid column > error. The > database did not change. Select statements work on it but not > updates. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247639 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Query issue between MX 6 and MX 7
I assume what the "radically different result" you describe is that on CF 7, column C gets reverted to the empty string (from '3') when the second query is injected into column BB? Or is there something else? cheers, barneyb On 7/25/06, Trevor Orr <[EMAIL PROTECTED]> wrote: > I ran across an issue with queries in MX 6 and MX7, try this code in MX > 6 and MX 7 and you get radically different results. Anyone know of a > reason for this or is it a bug. I sent this to Adobe but got no > response so maybe someone here can shed some light. > > > Here is the code: > > > > > > > > > > > > > > > > > > > > > > > > > > > Trevor Orr -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247636 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query issue between MX 6 and MX 7
I ran across an issue with queries in MX 6 and MX7, try this code in MX 6 and MX 7 and you get radically different results. Anyone know of a reason for this or is it a bug. I sent this to Adobe but got no response so maybe someone here can shed some light. Here is the code: Trevor Orr Summit Projects [EMAIL PROTECTED] 541.387.8883 x213 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247634 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Odd Query issue
I have a query today that stopped working with an invalid column error. The database did not change. Select statements work on it but not updates. Select * from jobs where job_id = 1 - works fine Update jobs set status = 0 where job_id = 1 - results in Invalid column name 'job_id'. Any ideas what would cause this to just start happening? Thanks, Scott ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247633 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Wierd query of query issue
I've just got my hands on a new W2k3 server and installed the MX6.1 trial. The problem as described in the original message no longer exists Must be a CF5 issue only... ho hum. Paul _ From: Paul Vernon Sent: Wednesday, 10 March 2004 11:50 To: CF-Talk Subject: Wierd query of query issue I've just discovered that one of my query of query functions is truncating a particular field in a query and I was wondering if anyone had come across this behaviour before I'm running CF5 on W2k SP4. SELECT * FROM IMAPServer #notaffectedcolumn# #MyColumn# SELECT * FROM IMAPServerQuery ORDER BY internaldate DESC #notaffectedcolumn# #MyColumn# [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Wierd query of query issue
I've just discovered that one of my query of query functions is truncating a particular field in a query and I was wondering if anyone had come across this behaviour before I'm running CF5 on W2k SP4. SELECT * FROM IMAPServer #notaffectedcolumn# #MyColumn# SELECT * FROM IMAPServerQuery ORDER BY internaldate DESC #notaffectedcolumn# #MyColumn# [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Query Issue
SELECT * FROM Table WHERE #form.ID# IN (AccessList) At 03:42 PM 5/20/2002 -0700, you wrote: >Howdy List! > >Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu >thread) > >I have an existing database with a field named 'AccessList'. This field is a >comma delimited list of IDs (numeric only). These IDs are for users who are >to be granted access to a function. For example one entry would be >4,15,26,44,101,9. > >I am trying to devise a query that will check the field for a single userID. >Say 4 for example... note that 44 also exists in the list. > >My fumbling attempts to devise a way using LIKE and IN are not working. > > > > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Issue
ahhh one more for the road... select list from table select ANYTHING TO GET A RECORDCOUNT from table where userID = #ID# AND userid IN (#list#) SUCCESS! DENIED! dead thread... - Original Message - From: "Ewok" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, May 20, 2002 7:06 PM Subject: Re: Query Issue > Im probably way off what you mean > > select list > from table > > > > > SUCCESS > > DENIED > > > > > > - Original Message - > From: "Tony Gruen" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, May 20, 2002 6:42 PM > Subject: Query Issue > > > > Howdy List! > > > > Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu > > thread) > > > > I have an existing database with a field named 'AccessList'. This field is > a > > comma delimited list of IDs (numeric only). These IDs are for users who > are > > to be granted access to a function. For example one entry would be > > 4,15,26,44,101,9. > > > > I am trying to devise a query that will check the field for a single > userID. > > Say 4 for example... note that 44 also exists in the list. > > > > My fumbling attempts to devise a way using LIKE and IN are not working. > > > > > > > > > > > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue
Thanks everyone. This helps. We have inherited an access DB that was originally in FileMaker Pro3 and migrated into access 97 by some goombaa. We proposed rebuilding the structure but no sale. It is on an intranet and does not carry any budget dollars for the client. Tony -Original Message- From: Ewok [mailto:[EMAIL PROTECTED]] Sent: Monday, May 20, 2002 4:07 PM To: CF-Talk Subject: Re: Query Issue Im probably way off what you mean select list from table SUCCESS DENIED - Original Message - From: "Tony Gruen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, May 20, 2002 6:42 PM Subject: Query Issue > Howdy List! > > Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu > thread) > > I have an existing database with a field named 'AccessList'. This field is a > comma delimited list of IDs (numeric only). These IDs are for users who are > to be granted access to a function. For example one entry would be > 4,15,26,44,101,9. > > I am trying to devise a query that will check the field for a single userID. > Say 4 for example... note that 44 also exists in the list. > > My fumbling attempts to devise a way using LIKE and IN are not working. > > > > > > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Issue
Im probably way off what you mean select list from table SUCCESS DENIED - Original Message - From: "Tony Gruen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, May 20, 2002 6:42 PM Subject: Query Issue > Howdy List! > > Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu > thread) > > I have an existing database with a field named 'AccessList'. This field is a > comma delimited list of IDs (numeric only). These IDs are for users who are > to be granted access to a function. For example one entry would be > 4,15,26,44,101,9. > > I am trying to devise a query that will check the field for a single userID. > Say 4 for example... note that 44 also exists in the list. > > My fumbling attempts to devise a way using LIKE and IN are not working. > > > > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue
select user_ID from usertbl where user_ID in (select accesslist from area where ) - j -Original Message- From: Tony Gruen [mailto:[EMAIL PROTECTED]] Sent: Monday, May 20, 2002 6:43 PM To: CF-Talk Subject: Query Issue Howdy List! Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu thread) I have an existing database with a field named 'AccessList'. This field is a comma delimited list of IDs (numeric only). These IDs are for users who are to be granted access to a function. For example one entry would be 4,15,26,44,101,9. I am trying to devise a query that will check the field for a single userID. Say 4 for example... note that 44 also exists in the list. My fumbling attempts to devise a way using LIKE and IN are not working. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue
Any chance you can change that db schema, or is it too late? I would think it might be a big headaches to maintain such a scheme. It's also likely to be much slower than some other approaches. If you're stuck with the current setup, just try something like this: select blah from Functions where 4 IN (select UserIDList from Functions where FunctionId = 1) -Original Message- From: Tony Gruen [mailto:[EMAIL PROTECTED]] Sent: Monday, May 20, 2002 6:43 PM To: CF-Talk Subject: Query Issue Howdy List! Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu thread) I have an existing database with a field named 'AccessList'. This field is a comma delimited list of IDs (numeric only). These IDs are for users who are to be granted access to a function. For example one entry would be 4,15,26,44,101,9. I am trying to devise a query that will check the field for a single userID. Say 4 for example... note that 44 also exists in the list. My fumbling attempts to devise a way using LIKE and IN are not working. __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue
If you must do it this way then ensure that you always put a comma at the beginning and at the end of the list of numbers before inserting into the DB. That you end up with this: ,4,15,26,44,101,9, . Then use LIKE %,4,% to get your results. I'd like to say that there are better ways of doing this... [table] users (FK)> [table] users_roles <(FK) [table] roles +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: chad [mailto:[EMAIL PROTECTED]] Sent: Monday, May 20, 2002 3:52 PM To: CF-Talk Subject: Re: Query Issue Why do it in the query? Grab your list and loop over it until you find a true. You should probably make another table and related that field out instead of making a comma delimited list in the db... but maybe you don't have control over the db. At 03:42 PM 5/20/2002 -0700, you wrote: >Howdy List! > >Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu >thread) > >I have an existing database with a field named 'AccessList'. This field is a >comma delimited list of IDs (numeric only). These IDs are for users who are >to be granted access to a function. For example one entry would be >4,15,26,44,101,9. > >I am trying to devise a query that will check the field for a single userID. >Say 4 for example... note that 44 also exists in the list. > >My fumbling attempts to devise a way using LIKE and IN are not working. > > > > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Issue
Why do it in the query? Grab your list and loop over it until you find a true. You should probably make another table and related that field out instead of making a comma delimited list in the db... but maybe you don't have control over the db. At 03:42 PM 5/20/2002 -0700, you wrote: >Howdy List! > >Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu >thread) > >I have an existing database with a field named 'AccessList'. This field is a >comma delimited list of IDs (numeric only). These IDs are for users who are >to be granted access to a function. For example one entry would be >4,15,26,44,101,9. > >I am trying to devise a query that will check the field for a single userID. >Say 4 for example... note that 44 also exists in the list. > >My fumbling attempts to devise a way using LIKE and IN are not working. > > > > > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Query Issue
Howdy List! Can someone please 'gimmie a thread' on this? (ie: Brain thread, as in cpu thread) I have an existing database with a field named 'AccessList'. This field is a comma delimited list of IDs (numeric only). These IDs are for users who are to be granted access to a function. For example one entry would be 4,15,26,44,101,9. I am trying to devise a query that will check the field for a single userID. Say 4 for example... note that 44 also exists in the list. My fumbling attempts to devise a way using LIKE and IN are not working. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Issue.
Well obviously you wouldn't be using a column that you were trying to update. - Original Message - From: Jared Stark To: CF-Talk Sent: Friday, May 03, 2002 6:25 PM Subject: RE: Query Issue. Except then if you try to update that same column later in the same query SQL Server will get upset with you. -Original Message- From: Kreig Zimmerman [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 4:23 PM To: CF-Talk Subject: Re: Query Issue. what i usually do is "columnname = columnname" which updates the content of the column, with the contents of the column--no problems there, eh? - Original Message - From: Jared Stark To: CF-Talk Sent: Friday, May 03, 2002 5:52 PM Subject: RE: Query Issue. I tried the 0 = 0 trick and SQL Server gives me an error. Does that work for you? __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue.
Except then if you try to update that same column later in the same query SQL Server will get upset with you. -Original Message- From: Kreig Zimmerman [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 4:23 PM To: CF-Talk Subject: Re: Query Issue. what i usually do is "columnname = columnname" which updates the content of the column, with the contents of the column--no problems there, eh? - Original Message - From: Jared Stark To: CF-Talk Sent: Friday, May 03, 2002 5:52 PM Subject: RE: Query Issue. I tried the 0 = 0 trick and SQL Server gives me an error. Does that work for you? __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Query Issue.
what i usually do is "columnname = columnname" which updates the content of the column, with the contents of the column--no problems there, eh? - Original Message - From: Jared Stark To: CF-Talk Sent: Friday, May 03, 2002 5:52 PM Subject: RE: Query Issue. I tried the 0 = 0 trick and SQL Server gives me an error. Does that work for you? __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue.
That's the trick. I should have seen that!! Thanks Jared -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 4:16 PM To: CF-Talk Subject: RE: Query Issue. It's been a while since I've tried it... I know it works with WHERE clauses... to be honest I don't remember if it worked for me or not with updates... I see your problem... do this instead: UPDATE table SET col1 = '#col1#'col1, col2 = '#col2#'col2, col3 = '#col3#'col3, col4 = '#col4#'col4 WHERE id = 12345 +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jared Stark [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 2:52 PM To: CF-Talk Subject: RE: Query Issue. I tried the 0 = 0 trick and SQL Server gives me an error. Does that work for you? -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 3:22 PM To: CF-Talk Subject: RE: Query Issue. escaping the inner apostrphe should work, but since it isn't, why build the update in a string? Just put the IF statements in the query itself. UPDATE table SET 0=0 ,col1 = '#col1#' ,col2 = '#col2#' ,col3 = '#col3#' ,col4 = '#col4#' WHERE id = 12345 +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jared Stark [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:59 PM To: CF-Talk Subject: Query Issue. I seem to be stuck between a rock and a hard spot here. I am building the 'guts' of an UPDATE query inside a variable, and then using the variable inside a query like so: UPDATE table SET #PreserveSingleQuotes(qryText)# WHERE id = 12345 This works fine unless inside qryText I get something like: Now it barfs on the ' because I am using PreserveSingleQuotes and that inner ' doesn't get escaped, which I have to do otherwise it escapes the quotes around the value. So I tried escaping the apostrophe like: but it still doesn't like that, and tells me there is an error at that apostrophe. Am I missing something here? I do realize it is Friday and I should probably be at the movies instead, but if someone could be so gracious as to point out what I'm missing I'd be grateful. ~ Jared __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue.
It's been a while since I've tried it... I know it works with WHERE clauses... to be honest I don't remember if it worked for me or not with updates... I see your problem... do this instead: UPDATE table SET col1 = '#col1#'col1, col2 = '#col2#'col2, col3 = '#col3#'col3, col4 = '#col4#'col4 WHERE id = 12345 +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jared Stark [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 2:52 PM To: CF-Talk Subject: RE: Query Issue. I tried the 0 = 0 trick and SQL Server gives me an error. Does that work for you? -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 3:22 PM To: CF-Talk Subject: RE: Query Issue. escaping the inner apostrphe should work, but since it isn't, why build the update in a string? Just put the IF statements in the query itself. UPDATE table SET 0=0 ,col1 = '#col1#' ,col2 = '#col2#' ,col3 = '#col3#' ,col4 = '#col4#' WHERE id = 12345 +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jared Stark [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:59 PM To: CF-Talk Subject: Query Issue. I seem to be stuck between a rock and a hard spot here. I am building the 'guts' of an UPDATE query inside a variable, and then using the variable inside a query like so: UPDATE table SET #PreserveSingleQuotes(qryText)# WHERE id = 12345 This works fine unless inside qryText I get something like: Now it barfs on the ' because I am using PreserveSingleQuotes and that inner ' doesn't get escaped, which I have to do otherwise it escapes the quotes around the value. So I tried escaping the apostrophe like: but it still doesn't like that, and tells me there is an error at that apostrophe. Am I missing something here? I do realize it is Friday and I should probably be at the movies instead, but if someone could be so gracious as to point out what I'm missing I'd be grateful. ~ Jared __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue.
I tried the 0 = 0 trick and SQL Server gives me an error. Does that work for you? -Original Message- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 3:22 PM To: CF-Talk Subject: RE: Query Issue. escaping the inner apostrphe should work, but since it isn't, why build the update in a string? Just put the IF statements in the query itself. UPDATE table SET 0=0 ,col1 = '#col1#' ,col2 = '#col2#' ,col3 = '#col3#' ,col4 = '#col4#' WHERE id = 12345 +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jared Stark [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:59 PM To: CF-Talk Subject: Query Issue. I seem to be stuck between a rock and a hard spot here. I am building the 'guts' of an UPDATE query inside a variable, and then using the variable inside a query like so: UPDATE table SET #PreserveSingleQuotes(qryText)# WHERE id = 12345 This works fine unless inside qryText I get something like: Now it barfs on the ' because I am using PreserveSingleQuotes and that inner ' doesn't get escaped, which I have to do otherwise it escapes the quotes around the value. So I tried escaping the apostrophe like: but it still doesn't like that, and tells me there is an error at that apostrophe. Am I missing something here? I do realize it is Friday and I should probably be at the movies instead, but if someone could be so gracious as to point out what I'm missing I'd be grateful. ~ Jared __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Query Issue.
escaping the inner apostrphe should work, but since it isn't, why build the update in a string? Just put the IF statements in the query itself. UPDATE table SET 0=0 ,col1 = '#col1#' ,col2 = '#col2#' ,col3 = '#col3#' ,col4 = '#col4#' WHERE id = 12345 +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: Jared Stark [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:59 PM To: CF-Talk Subject: Query Issue. I seem to be stuck between a rock and a hard spot here. I am building the 'guts' of an UPDATE query inside a variable, and then using the variable inside a query like so: UPDATE table SET #PreserveSingleQuotes(qryText)# WHERE id = 12345 This works fine unless inside qryText I get something like: Now it barfs on the ' because I am using PreserveSingleQuotes and that inner ' doesn't get escaped, which I have to do otherwise it escapes the quotes around the value. So I tried escaping the apostrophe like: but it still doesn't like that, and tells me there is an error at that apostrophe. Am I missing something here? I do realize it is Friday and I should probably be at the movies instead, but if someone could be so gracious as to point out what I'm missing I'd be grateful. ~ Jared __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Query Issue.
I seem to be stuck between a rock and a hard spot here. I am building the 'guts' of an UPDATE query inside a variable, and then using the variable inside a query like so: UPDATE table SET #PreserveSingleQuotes(qryText)# WHERE id = 12345 This works fine unless inside qryText I get something like: Now it barfs on the ' because I am using PreserveSingleQuotes and that inner ' doesn't get escaped, which I have to do otherwise it escapes the quotes around the value. So I tried escaping the apostrophe like: but it still doesn't like that, and tells me there is an error at that apostrophe. Am I missing something here? I do realize it is Friday and I should probably be at the movies instead, but if someone could be so gracious as to point out what I'm missing I'd be grateful. ~ Jared __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Dynamic Insert Query Issue
How about if you make the unused fields on your form invisible and give them a value which you can detect on your processing page and set them to null. A little more work on the form generation side. Another way to do it is to use a set if Isdefined("Form.Adresses_X") on the processing page and set the ones that are not defined to "Null". - Original Message - From: "Brunt, Michael" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, April 02, 2002 6:13 PM Subject: Dynamic Insert Query Issue > This is difficult to explain but I will try my best. I have an Insert Query > which will be passed a list of values from a form. This form is dynamically > generated from a previous query result set so by the time the form is > generated we know the number of records returned used for building the form. > Once the user has entered information in the form and submitted it the > insert query has to behave like this: - > > > INSERT INTO #request.TBCR#TN0987 > ADDR_ID_001, >ADDR_ID_002, >ADDR_ID_003, >ADDR_ID_004, >ADDR_ID_005, >ADDR_ID_006, >ADDR_ID_007, >ADDR_ID_008 (this carries on up to 100) > VALUES >#form.addressID# (once again we have 100 fields in the db and for > example in the case where there are 5 values passed to this query from the > form we have make the remaining 95 values be null and this will vary > dependant on the number of form values past. So again if there are 20 > form.addressID's passed we would have to dynamically set the remaining 80 > values in the Insert to null. > > Hope I explained this well, can anyone help? > > Mike Brunt > Sempra Energy > 213.244.5226 > > "A logician trying to explain logic to a programmer is like a cat trying to > explain to a fish what it's like to get wet." > > > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Dynamic Insert Query Issue
This is difficult to explain but I will try my best. I have an Insert Query which will be passed a list of values from a form. This form is dynamically generated from a previous query result set so by the time the form is generated we know the number of records returned used for building the form. Once the user has entered information in the form and submitted it the insert query has to behave like this: - INSERT INTO #request.TBCR#TN0987 ADDR_ID_001, ADDR_ID_002, ADDR_ID_003, ADDR_ID_004, ADDR_ID_005, ADDR_ID_006, ADDR_ID_007, ADDR_ID_008 (this carries on up to 100) VALUES #form.addressID# (once again we have 100 fields in the db and for example in the case where there are 5 values passed to this query from the form we have make the remaining 95 values be null and this will vary dependant on the number of form values past. So again if there are 20 form.addressID's passed we would have to dynamically set the remaining 80 values in the Insert to null. Hope I explained this well, can anyone help? Mike Brunt Sempra Energy 213.244.5226 "A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet." __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists