Error using Select TOP with foxpro

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
samclay
Posts: 1
Joined: Fri 18 Jun 2021 15:43

Error using Select TOP with foxpro

Post by samclay » 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:

Code: Select all

"DRIVER={Devart ODBC Driver for xBase};Database=C:\\Data\\;DBFFormat=VisualFoxPro"
If I query:

Code: Select all

SELECT A.VendorId, A.Code, A.Name 
FROM Vendors as A 
ORDER BY A.VendorId asc
I get the correct result. But if I try to use TOP in the query:

Code: Select all

SELECT TOP 5 A.VendorId, A.Code, A.Name 
FROM Vendors as A 
ORDER BY A.VendorId asc
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?

Stellar
Devart Team
Posts: 431
Joined: Tue 03 Oct 2017 11:00

Re: Error using Select TOP with foxpro

Post by Stellar » Fri 23 Jul 2021 14:07

Hello,

ODBC Driver for xBase is designed with using SQLite engine, therefore to compose queries using our drivers, you can use the syntax used in SQLIte: sqlite.org/lang.html
In most cases, our driver supports the SQLite syntax. To limit the number of rows returned for a query, please use the LIMIT clause. For example:

Code: Select all

SELECT TOP 5 A.VendorId, A.Code, A.Name 
FROM Vendors as A 
LIMIT 5
Should you have any questions, do not hesitate to ask!
Best regards,
Sergey
Devart Team
www.devart.com

Post Reply