The TOP statement reports a syntax error when UniDAC queries the dbf file

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Wilson Alex
Posts: 14
Joined: Fri 13 Dec 2019 16:39

The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Wilson Alex » Wed 13 Oct 2021 02:51

Hi,

I am trying to use UniDAC to query the data in the Visual FoxPro database. I refer to the instructions in this official document (https://blog.devart.com/delphi-component-for-dbf.html) to connect the dbf data table file, and everything is normal.

But when I use the TOP n function to query the first record in the table that is larger than a certain field, it keeps reporting "near "1"1: syntax error".
https://sm.ms/image/A9G4pHiml8vEKaF

But I use Microsoft Visual FoxPro 9 SP2 to execute the same SQL statement in the command line window and it can be executed successfully.
https://sm.ms/image/sVNEABow6dDTFPJ

I want to know where the problem is and how to deal with it.

Wilson Alex
Posts: 14
Joined: Fri 13 Dec 2019 16:39

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Wilson Alex » Wed 13 Oct 2021 14:48

Hi MaximG, Could you give me some help? I'm a little anxious.

frickler
Posts: 27
Joined: Wed 04 Apr 2018 08:30

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by frickler » Mon 18 Oct 2021 11:30

UniDAC uses SQL engine of SQLite for this. So you are limited to SQLite compatible SQL.

Instead of "TOP" use "LIMIT":

Code: Select all

SELECT TOP 10 id, name FROM foo ORDER BY id
becomes

Code: Select all

SELECT id, name FROM foo ORDER BY id LIMIT 10

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

Re: The TOP statement reports a syntax error when UniDAC queries the dbf file

Post by Stellar » Tue 19 Oct 2021 12:55

Hi,

Devart 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, pelase use the LIMIT clause.

For example:
SELECT Name FROM TestTable LIMIT 1

Should you have any questions, do not hesitate to ask!
Best regards,
Sergey

Post Reply