Flummoxed bya simple query
This is a new one for me. I have been querying the same database for years. I have hundreds of queries working on this very database on a daily basis without issues. Today a new request came in to access a field that I have not needed to retrieve before. Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo table, which is part of a FoxPro database designed by a vendor. The following query completes just fine: SELECT * FROM BasicInfo WHERE IDNo = '5183' And yes, I know that the use of asterisks in a query is not good practice. I am trying to troubleshoot here... I do a CFDUMP of the query, and the field I am most interested in, namely BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of the other fields in the table. HOWEVER, When I rewrite the query as follows: SELECT IDNo, Mcaidrecda FROM BasicInfo WHERE IDNo = '5183' I get an error saying that Column 'MCAIDRECDA' is not found. It happens when I remove the WHERE clause, too. When I open the database table (a FoxPro table), the Mcaidrecda field is very clearly there, and this is supported by the first test (CFDUMP) shown above. Why can't it see the field when the query is written the second way??? I checked and triple checked my spelling of the field names, etc, and all is well. The reason this is important is because I want to set a WHERE clause based on the Mcaidrecda field, but it stops cold and says the field does not exist, even though I have proven time and again that it is most certainly there. SOMEBODY tell me I am missing the obvious here BTW: CF7 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311367 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
not knowing much about Foxpro... Is it case sensitive, (IE: are MCAIDRECDA and Mcaidrecda the same thing) Tim Claremont wrote: > This is a new one for me. I have been querying the same database for years. I > have hundreds of queries working on this very database on a daily basis > without issues. Today a new request came in to access a field that I have not > needed to retrieve before. > > Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo table, > which is part of a FoxPro database designed by a vendor. > > The following query completes just fine: > > SELECT * > FROM BasicInfo > WHERE IDNo = '5183' > > And yes, I know that the use of asterisks in a query is not good practice. I > am trying to troubleshoot here... > > I do a CFDUMP of the query, and the field I am most interested in, namely > BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of > the other fields in the table. > > HOWEVER, > > When I rewrite the query as follows: > > SELECT IDNo, Mcaidrecda > FROM BasicInfo > WHERE IDNo = '5183' > > I get an error saying that Column 'MCAIDRECDA' is not found. It happens when > I remove the WHERE clause, too. > > When I open the database table (a FoxPro table), the Mcaidrecda field is very > clearly there, and this is supported by the first test (CFDUMP) shown above. > Why can't it see the field when the query is written the second way??? I > checked and triple checked my spelling of the field names, etc, and all is > well. > > The reason this is important is because I want to set a WHERE clause based on > the Mcaidrecda field, but it stops cold and says the field does not exist, > even though I have proven time and again that it is most certainly there. > > SOMEBODY tell me I am missing the obvious here > > BTW: CF7 > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311368 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
I recently ran into an issue where the table names had to be in all caps matching the field name in the querry. I never worried about it before and was very confused when it happened. Could that be the case? Tim Claremont wrote: > This is a new one for me. I have been querying the same database for years. I > have hundreds of queries working on this very database on a daily basis > without issues. Today a new request came in to access a field that I have not > needed to retrieve before. > > Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo table, > which is part of a FoxPro database designed by a vendor. > > The following query completes just fine: > > SELECT * > FROM BasicInfo > WHERE IDNo = '5183' > > And yes, I know that the use of asterisks in a query is not good practice. I > am trying to troubleshoot here... > > I do a CFDUMP of the query, and the field I am most interested in, namely > BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of > the other fields in the table. > > HOWEVER, > > When I rewrite the query as follows: > > SELECT IDNo, Mcaidrecda > FROM BasicInfo > WHERE IDNo = '5183' > > I get an error saying that Column 'MCAIDRECDA' is not found. It happens when > I remove the WHERE clause, too. > > When I open the database table (a FoxPro table), the Mcaidrecda field is very > clearly there, and this is supported by the first test (CFDUMP) shown above. > Why can't it see the field when the query is written the second way??? I > checked and triple checked my spelling of the field names, etc, and all is > well. > > The reason this is important is because I want to set a WHERE clause based on > the Mcaidrecda field, but it stops cold and says the field does not exist, > even though I have proven time and again that it is most certainly there. > > SOMEBODY tell me I am missing the obvious here > > BTW: CF7 > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311369 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Flummoxed bya simple query
You said FoxPro which leads me to believe you might be running on Mac or Linux. Does FoxPro have case-sensitive column names? -Original Message- From: Tim Claremont [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2008 10:00 AM To: CF-Talk Subject: Flummoxed bya simple query This is a new one for me. I have been querying the same database for years. I have hundreds of queries working on this very database on a daily basis without issues. Today a new request came in to access a field that I have not needed to retrieve before. Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo table, which is part of a FoxPro database designed by a vendor. The following query completes just fine: SELECT * FROM BasicInfo WHERE IDNo = '5183' And yes, I know that the use of asterisks in a query is not good practice. I am trying to troubleshoot here... I do a CFDUMP of the query, and the field I am most interested in, namely BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of the other fields in the table. HOWEVER, When I rewrite the query as follows: SELECT IDNo, Mcaidrecda FROM BasicInfo WHERE IDNo = '5183' I get an error saying that Column 'MCAIDRECDA' is not found. It happens when I remove the WHERE clause, too. When I open the database table (a FoxPro table), the Mcaidrecda field is very clearly there, and this is supported by the first test (CFDUMP) shown above. Why can't it see the field when the query is written the second way??? I checked and triple checked my spelling of the field names, etc, and all is well. The reason this is important is because I want to set a WHERE clause based on the Mcaidrecda field, but it stops cold and says the field does not exist, even though I have proven time and again that it is most certainly there. SOMEBODY tell me I am missing the obvious here BTW: CF7 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311370 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
That was my first thought, but I even went so far as to COPY AND PASTE the field name from the actual FoxPro database. Also, to further confirm, I entered the IDNo field as both IDNO and idno and it works just fine. Conclusion is that I do not believe the case to be relevent. >I recently ran into an issue where the table names had to be in all caps >matching the field name in the querry. I never worried about it before >and was very confused when it happened. > >Could that be the case? > >Tim Claremont wrote: >> ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311371 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
Nope. Running on a PC. I have proven to myself time and again that the fieldnames are NOT case sensitive. >You said FoxPro which leads me to believe you might be running on Mac or >Linux. Does FoxPro have case-sensitive column names? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311372 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
I've worked briefly with it at an old job. In CFMX 6 ColdFusion connected via the ODBC bridge and there were some "unusual" data results. Has someone produced a JDBC driver for FoxPro? I don't think Microsoft supports it anymore.. Scott Stewart wrote: > not knowing much about Foxpro... > > Is it case sensitive, (IE: are MCAIDRECDA and Mcaidrecda the same thing) > > Tim Claremont wrote: > >> This is a new one for me. I have been querying the same database for years. >> I have hundreds of queries working on this very database on a daily basis >> without issues. Today a new request came in to access a field that I have >> not needed to retrieve before. >> >> Mcaidrecda (Medicaid Recertification Date) is a field in the BasicInfo >> table, which is part of a FoxPro database designed by a vendor. >> >> The following query completes just fine: >> >> SELECT * >> FROM BasicInfo >> WHERE IDNo = '5183' >> >> And yes, I know that the use of asterisks in a query is not good practice. I >> am trying to troubleshoot here... >> >> I do a CFDUMP of the query, and the field I am most interested in, namely >> BasicInfo.MCAIDRECDA, shows up as expected in the CFDUMP, along with all of >> the other fields in the table. >> >> HOWEVER, >> >> When I rewrite the query as follows: >> >> SELECT IDNo, Mcaidrecda >> FROM BasicInfo >> WHERE IDNo = '5183' >> >> I get an error saying that Column 'MCAIDRECDA' is not found. It happens when >> I remove the WHERE clause, too. >> >> When I open the database table (a FoxPro table), the Mcaidrecda field is >> very clearly there, and this is supported by the first test (CFDUMP) shown >> above. Why can't it see the field when the query is written the second >> way??? I checked and triple checked my spelling of the field names, etc, and >> all is well. >> >> The reason this is important is because I want to set a WHERE clause based >> on the Mcaidrecda field, but it stops cold and says the field does not >> exist, even though I have proven time and again that it is most certainly >> there. >> >> SOMEBODY tell me I am missing the obvious here >> >> BTW: CF7 >> >> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311373 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Flummoxed bya simple query
>>Does FoxPro have case-sensitive column names? It could be an issue with table names, since tables are actually files, but not for columns. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311374 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Flummoxed bya simple query
The following queries all return the same recordcount (1161 Records): SELECT * FROM BASICINFO SELECT * FROM BasicInfo SELECT * FROM BaSICINFO >It could be an issue with table names, since tables are actually files, >but not for columns. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311375 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
I've seen issues surrounding ColdFusion caching table structures. To fix the problem, go into CF administrator, turn off caching for that datasource, execute your query again, and turn caching back on. On Thu, Aug 21, 2008 at 11:24 AM, Tim Claremont < [EMAIL PROTECTED]> wrote: > The following queries all return the same recordcount (1161 Records): > > SELECT * > FROM BASICINFO > > SELECT * > FROM BasicInfo > > SELECT * > FROM BaSICINFO > > > > >It could be an issue with table names, since tables are actually files, > >but not for columns. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311376 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Flummoxed bya simple query
This is REALLY clutching at straws, but here are some stupid things you might try: SELECT Mcaidrecad FROM BASICINFO WHERE IDNo = '5183' (in case there's something about "IDNo, Mcaidrecad" it doesn't like) Or output the queryname.columnlist, and cut-and-paste from that instead of from the db definition. -Original Message- From: Sonny Savage [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2008 11:32 AM To: CF-Talk Subject: Re: Flummoxed bya simple query I've seen issues surrounding ColdFusion caching table structures. To fix the problem, go into CF administrator, turn off caching for that datasource, execute your query again, and turn caching back on. On Thu, Aug 21, 2008 at 11:24 AM, Tim Claremont < [EMAIL PROTECTED]> wrote: > The following queries all return the same recordcount (1161 Records): > > SELECT * > FROM BASICINFO> > SELECT * > FROM BasicInfo > > SELECT * > FROM BaSICINFO > > > > >It could be an issue with table names, since tables are actually files, > >but not for columns. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311377 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
THIS works: SELECT BasicInfo.*, Identity.*, Enrollment.* FROM BasicInfo INNER JOIN Identity ON BasicInfo.IDID = Identity.ID INNER JOIN Enrollment ON BasicInfo.IDID = Enrollment.IDID ORDER BY LastName ASC, FirstName ASC #GetAll.RecordCount# Clients found with recert dates between #DateFormat(MAStartDate,"MM/DD/")# and #DateFormat(MAEndDate,"MM/DD/")# #Trim(GetAll.LastName)#, #Trim(GetAll.FirstName)# #Trim(GetAll.MI)# #DateFormat(GetAll.mcaidrecda,"MM/DD/")# Note that I refer to mcaidrecda in my CFLOOP at the end of the code shown above, and it displays accurately in the output. THIS Fails: SELECT BasicInfo.mcaidrecda, Identity.*, Enrollment.* FROM BasicInfo INNER JOIN Identity ON BasicInfo.IDID = Identity.ID INNER JOIN Enrollment ON BasicInfo.IDID = Enrollment.IDID ORDER BY LastName ASC, FirstName ASC The specific error is as follows: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro Driver]SQL: Column 'MCAIDRECDA' is not found. #GetAll.RecordCount# Clients found with recert dates between #DateFormat(MAStartDate,"MM/DD/")# and #DateFormat(MAEndDate,"MM/DD/")# #Trim(GetAll.LastName)#, #Trim(GetAll.FirstName)# #Trim(GetAll.MI)# #DateFormat(GetAll.mcaidrecda,"MM/DD/")# ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311378 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Flummoxed bya simple query
On Thu, Aug 21, 2008 at 11:07 AM, Tim Claremont <[EMAIL PROTECTED]> wrote: > THIS Fails: > > > >SELECT BasicInfo.mcaidrecda, Identity.*, Enrollment.* >FROM BasicInfo >INNER JOIN Identity ON BasicInfo.IDID = Identity.ID >INNER JOIN Enrollment ON BasicInfo.IDID = Enrollment.IDID >ORDER BY LastName ASC, FirstName ASC > What about running the query directly in some sort of FoxPro Query tool (if there is one) as opposed to using CF? -- Matt Williams "It's the question that drives us." ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311379 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
Every time this has happened to me, it was a caching issue. Try to remove caching from the datasource, if that doesn't work reboot the database machine or restart the db services. After that restart CF and see if its all better. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311384 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flummoxed bya simple query
I had a problem once where an CFLDAP query returned an invalid column name. It was a similar issue where I could see it there, but I could not access it because it had an invalid column name. I found however that I could rename it using a method that I found on Ben Nadel's site. While your column name may not be invalid, perhaps you could try renaming it to something else to see if you can access it. Worth a try maybe. http://www.bennadel.com/index.cfm?dax=blog:357.view Dan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311388 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Flummoxed bya simple query
That's not a bad idea. Have you tried aliasing the column to something else then referencing the aliased column? -Original Message- From: Dan Crouch [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2008 4:19 PM To: CF-Talk Subject: Re: Flummoxed bya simple query I had a problem once where an CFLDAP query returned an invalid column name. It was a similar issue where I could see it there, but I could not access it because it had an invalid column name. I found however that I could rename it using a method that I found on Ben Nadel's site. While your column name may not be invalid, perhaps you could try renaming it to something else to see if you can access it. Worth a try maybe. http://www.bennadel.com/index.cfm?dax=blog:357.view Dan ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311389 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Flummoxed bya simple query
Have you checked that the field name does NOT have a trailing Space? FoxPro and access allow spaces in field names. [BasicInfo].[mcaidrecda ] isnt the same as [BasicInfo].[mcaidrecda] On Thu, Aug 21, 2008 at 5:39 PM, Andy Matthews <[EMAIL PROTECTED]>wrote: > That's not a bad idea. Have you tried aliasing the column to something else > then referencing the aliased column? > > > -Original Message- > From: Dan Crouch [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 21, 2008 4:19 PM > To: CF-Talk > Subject: Re: Flummoxed bya simple query > > I had a problem once where an CFLDAP query returned an invalid column name. > It was a similar issue where I could see it there, but I could not access > it > because it had an invalid column name. I found however that I could rename > it using a method that I found on Ben Nadel's site. While your column name > may not be invalid, perhaps you could try renaming it to something else to > see if you can access it. Worth a try maybe. > > http://www.bennadel.com/index.cfm?dax=blog:357.view > > Dan > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311404 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Flummoxed bya simple query
I worked around the problem by selecting everything from the table within my date range, and then using QoQ to get the field in question. This works just fine, but is far from optimal. I have rebooted the server, and the services, said three hail Mary's, you name it. The only thing that worked is QoQ! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311510 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
