Here is an example of yesterdays SHK (School of hard knocks) I am willing to give you plenty of examples where I am an idiot.
5/13/95
Just because it works in ISQL does not mean it will work in CW using ODBC. ISQL uses direct DLL calls to the database and actually handles many situations better then ODBC.
SELECT * FROM CLINICS WHERE NAME LIKE "troy%" works in ISQL and Intersolves 32bit DataDirect ODBC drivers.
However using MS ODBC causes an FILEERROR. Always use single quotes and you wont have a problem.
SELECT * FROM CLINICS WHERE NAME LIKE 'troy%'
This took about 2 hours to find since I had not been using ERRORCODE() and FILERROR() in my NEXT() loop. Since I did not want an error message to appear when I hit the end of the subset. The damn app ran fine on my computer but when I moved it to other computers (using MS ODBC) I was getting no data back. After putting in FILEERROR() I found that MS ODBC returned an "Invalid column name "troy%". Tom Hummel looked over my shoulder and said try single quotes. My reply was "I cut and pasted the select into ISQL and it works!". But I tried single quotes as my last desperate attempt. TaDa!! it works!! Never trust ISQL it is several layers closer to the server and is not a TRUE test of sql statements in our CW ODBC environment. Also different ODBC drivers behave differently.
Intersolves ODBC liked the double quotes and did not post any errors. So being an idiot I did not have any error checking. But then when the app was moved to a MS ODBC errors started occurring but were not being trapped since I had tested the SQL statements and knew they were good. The new office rule is to use the following errorchecking on any C/S loop.
This was my loop generated by a template in psudo code.
Loop NEXT(file) IF ERRORCODE(); EndOfFetch = true ; break. DO SqlFillBuffer End
Now with C/S compatible errorchecking
Loop
NEXT(file)
case ERRORCODE()
of 33 ! RECORD NOT AVAILABLE ie. in MS SQL ODBC = EOF
EndOfFetch = true
Break
of 90 ! UNKNOWN ERROR POSTED ie. look at FILEERROR() for a description
Message(FILEERROR(),'ODBC ERROR',icon:hand)
Break
end
DO SqlFillBuffer
End
Troy Sorzano
Information Packaging Unlimited