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

not knowing much about Foxpro... Is it case sensitive, (IE: are MCAIDRECDA and Mcaidrecda the same thing) Tim Claremont wrote: ----- Excess quoted text cut - see Original Post for more -----

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: ----- Excess quoted text cut - see Original Post for more -----

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: ----- Excess quoted text cut - see Original Post for more -----

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. ----- Excess quoted text cut - see Original Post for more -----

You said FoxPro which leads me to believe you might be running on Mac or Linux. Does FoxPro have case-sensitive column names? 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

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?

>>Does FoxPro have case-sensitive column names? It could be an issue with table names, since tables are actually files, but not for columns.

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.

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 < timothy.claremont@viahealth.org> wrote: ----- Excess quoted text cut - see Original Post for more -----

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. 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 < timothy.claremont@viahealth.org> wrote: ----- Excess quoted text cut - see Original Post for more ----- files, > >but not for columns. > >

THIS works: <cfquery name="GetAll" datasource="PaceCareData" maxrows=10>   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 </cfquery> #GetAll.RecordCount# Clients found with recert dates between #DateFormat(MAStartDate,"MM/DD/YYYY")# and #DateFormat(MAEndDate,"MM/DD/YYYY")#<br><br> <cfloop query="GetAll"> #Trim(GetAll.LastName)#, #Trim(GetAll.FirstName)# #Trim(GetAll.MI)# #DateFormat(GetAll.mcaidrecda,"MM/DD/YYYY")#<br> </CFLOOP> 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: <cfquery name="GetAll" datasource="PaceCareData" maxrows=10>   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 </cfquery> 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/YYYY")# and #DateFormat(MAEndDate,"MM/DD/YYYY")#<br><br> <cfloop query="GetAll"> #Trim(GetAll.LastName)#, #Trim(GetAll.FirstName)# #Trim(GetAll.MI)# #DateFormat(GetAll.mcaidrecda,"MM/DD/YYYY")#<br> </CFLOOP>

On Thu, Aug 21, 2008 at 11:07 AM, Tim Claremont <timothy.claremont@viahealth.org> wrote: ----- Excess quoted text cut - see Original Post for more ----- 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."

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.

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

That's not a bad idea. Have you tried aliasing the column to something else then referencing the aliased column? 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

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 <lists@commadelimited.com>wrote: ----- Excess quoted text cut - see Original Post for more -----

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!