Error using Select TOP with foxpro
Posted: Fri 18 Jun 2021 16:26
I am testing the ODBC driver for xBase (v 3.0.1) in an APII am building, I have a connection set up with the database directory. The connection string is:
If I query:
I get the correct result. But if I try to use TOP in the query:
I get this error: ERROR [HY000] [Devart][ODBC][xBase]near \"5\": syntax error
If I use a VFPOLEDB connection the query works fine.
I have also created a system DSN pointing to the same database (using the same driver) and added a linked server in SSMS. The Query works as expected there as well.
If it's directly related to the driver, why does it work in SSMS but not in the API? I know there are limitations, such as not being able to access the long column names (stored in the dbc), which are available in VFPOLEDB. But TOP does work for SSMS. Maybe SSMS is applying the TOP after it has received the query results from the linked server? I'm not sure.
I was wanting to know if anyone has encountered this or similar limitations, and if they have found a solution?
Code: Select all
"DRIVER={Devart ODBC Driver for xBase};Database=C:\\Data\\;DBFFormat=VisualFoxPro"
Code: Select all
SELECT A.VendorId, A.Code, A.Name
FROM Vendors as A
ORDER BY A.VendorId asc
Code: Select all
SELECT TOP 5 A.VendorId, A.Code, A.Name
FROM Vendors as A
ORDER BY A.VendorId asc
If I use a VFPOLEDB connection the query works fine.
I have also created a system DSN pointing to the same database (using the same driver) and added a linked server in SSMS. The Query works as expected there as well.
If it's directly related to the driver, why does it work in SSMS but not in the API? I know there are limitations, such as not being able to access the long column names (stored in the dbc), which are available in VFPOLEDB. But TOP does work for SSMS. Maybe SSMS is applying the TOP after it has received the query results from the linked server? I'm not sure.
I was wanting to know if anyone has encountered this or similar limitations, and if they have found a solution?